Home / Azure / Fix Azure SQL Database Deadlock

Azure

Fix Azure SQL Database Deadlock

Resolve Azure SQL deadlocks by analyzing deadlock graphs, optimizing transaction order, and implementing retry logic.

Published: Apr 3, 20269 min readBy FixWikiHub Editorial Team

Abstract illustration for a troubleshooting knowledge base category.

Introduction

Azure SQL Database deadlocks occur when two or more transactions hold locks on resources and each waits for a lock held by the other. SQL Server's deadlock detector kills one transaction (victim) to resolve the circular dependency.

Symptoms

Transaction deadlock:

sql

Error: 1205, Severity: 13, State: 51
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Deadlock in application:

json

{
  "error": {
    "code": "SqlException",
    "message": "Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim"
  }
}

High deadlock rate:

# Shows elevated deadlock count ```

Common Causes

1.Opposite lock order - Transactions acquire locks in different order
2.Long-running transactions - Extended lock hold time increases conflict
3.Explicit locks - SELECT WITH HOLDLOCK or explicit locking hints
4.Foreign key checks - Parent-child table lock contention
5.Book-look searches - Bookmark lookup deadlock pattern
6.Parallel queries - Intra-query parallelism deadlock
7.High concurrency - Many simultaneous transactions on same resources

Step-by-Step Fix

Step 1: Enable Deadlock Monitoring

```bash # Enable extended events for deadlock capture # Via Azure Portal or T-SQL:

# In SQL Server Management Studio or via sqlcmd: sqlcmd -S my-server.database.windows.net -d my-database -U admin -P password

# Create extended events session CREATE EVENT SESSION [deadlock_capture] ON SERVER ADD EVENT sqlserver.database_xml_deadlock_report ADD TARGET package0.event_file(SET filename = 'deadlock.xel') WITH (STARTUP_STATE = ON); GO

ALTER EVENT SESSION [deadlock_capture] ON SERVER STATE = START; GO ```

Step 2: Query Deadlock Graph

```bash # Query sys.dm_xe_session_targets for deadlock info sqlcmd -S my-server.database.windows.net -d my-database -U admin -P password -Q " SELECT xed.value('@timestamp', 'datetime') as CreationTime, xed.query('.') as DeadlockGraph FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address CROSS APPLY st.target_data.nodes('Event[@name=\"database_xml_deadlock_report\"]') AS XEventData(xed) WHERE s.name = 'deadlock_capture' ORDER BY CreationTime DESC "

# Deadlock graph shows: # - Victim process # - Lock owners # - Locked resources # - Wait-for graph ```

Step 3: Analyze Deadlock Graph

xml

<!-- Sample deadlock graph structure -->
<deadlock>
  <victim-list>
    <victimProcess id="process54" />
  </victim-list>
  <process-list>
    <process id="process54">
      <executionStack>
        <frame>UPDATE Orders SET Status = 'Processing' WHERE OrderId = 1</frame>
      </executionStack>
      <inputbuf>UPDATE Orders SET Status = 'Processing' WHERE OrderId = 1</inputbuf>
    </process>
    <process id="process55">
      <executionStack>
        <frame>UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductId = 1</frame>
      </executionStack>
      <inputbuf>UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductId = 1</inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock objectname="Orders" mode="X">
      <owner-list><owner id="process54" /></owner-list>
      <waiter-list><waiter id="process55" /></waiter-list>
    </keylock>
    <keylock objectname="Inventory" mode="X">
      <owner-list><owner id="process55" /></owner-list>
      <waiter-list><waiter id="process54" /></waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Step 4: Standardize Lock Order

```sql -- Problem: Transactions lock tables in different order -- Transaction A: Orders -> Inventory -- Transaction B: Inventory -> Orders

-- Solution: Always lock in consistent order -- All transactions: Orders -> Inventory

BEGIN TRANSACTION; -- Always lock Orders first UPDATE Orders SET Status = 'Processing' WHERE OrderId = 1; -- Then lock Inventory UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductId = 1; COMMIT TRANSACTION;

-- Alternative: Use sp_getapplock for explicit ordering BEGIN TRANSACTION; EXEC sp_getapplock @Resource = 'Order_1', @LockMode = 'Exclusive'; -- Process order... EXEC sp_releaseapplock @Resource = 'Order_1'; COMMIT TRANSACTION; ```

Step 5: Reduce Transaction Duration

```sql -- Problem: Long transaction holding locks BEGIN TRANSACTION; -- Many operations... SELECT * FROM LargeTable; -- Slow query UPDATE TargetTable SET Value = ...; -- More operations... COMMIT TRANSACTION;

-- Solution: Minimize transaction scope -- Move read operations outside transaction SELECT * FROM LargeTable; -- Outside transaction

BEGIN TRANSACTION; -- Only necessary updates inside transaction UPDATE TargetTable SET Value = ...; COMMIT TRANSACTION;

-- Or use snapshot isolation SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRANSACTION; -- Reads don't block writes UPDATE TargetTable SET Value = ...; COMMIT TRANSACTION; ```

Step 6: Implement Retry Logic

```sql -- Application retry pattern for deadlock victims DECLARE @retry_count INT = 0; DECLARE @max_retries INT = 3; DECLARE @success BIT = 0;

WHILE @retry_count < @max_retries AND @success = 0 BEGIN BEGIN TRY BEGIN TRANSACTION; -- Transaction operations UPDATE Orders SET Status = 'Processing' WHERE OrderId = 1; UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductId = 1; COMMIT TRANSACTION; SET @success = 1; END TRY BEGIN CATCH IF ERROR_NUMBER() = 1205 -- Deadlock BEGIN SET @retry_count = @retry_count + 1; WAITFOR DELAY '00:00:01'; -- Wait 1 second before retry END ELSE BEGIN -- Other errors - don't retry THROW; END END CATCH END

IF @success = 0 BEGIN RAISERROR('Transaction failed after %d retries', 16, 1, @max_retries); END ```

Step 7: Use Read Committed Snapshot

```bash # Enable RCSI at database level (via Azure Portal or T-SQL) sqlcmd -S my-server.database.windows.net -d my-database -U admin -P password -Q " ALTER DATABASE my-database SET READ_COMMITTED_SNAPSHOT ON; "

# RCSI benefits: # - Readers don't block writers # - Writers don't block readers # - Reduces many deadlock scenarios

# Verify RCSI enabled sqlcmd -S my-server.database.windows.net -d master -U admin -P password -Q " SELECT name, is_read_committed_snapshot_on FROM sys.databases WHERE name = 'my-database'; " ```

Step 8: Optimize Foreign Key Constraints

```sql -- Foreign key checks can cause deadlocks -- Parent table locked when updating child

-- Option 1: Batch updates to reduce FK checks INSERT INTO OrderItems (OrderId, ProductId, Quantity) SELECT OrderId, ProductId, Quantity FROM TempOrderItems; -- Batch insert

-- Option 2: Use indexed FK columns for faster checks -- Ensure FK columns are indexed CREATE INDEX IX_OrderItems_OrderId ON OrderItems(OrderId);

-- Option 3: Consider NOT FOR REPLICATION for FK (careful) -- ALTER TABLE OrderItems WITH NOCHECK ADD CONSTRAINT FK_OrderItems_Orders -- FOREIGN KEY (OrderId) REFERENCES Orders(OrderId) NOT FOR REPLICATION; ```

Step 9: Monitor Deadlock Rate

# Create alert for high deadlock rate az monitor metrics alert create \ --name sql-deadlock-alert \ --resource-group my-rg \ --scopes /subscriptions/SUB/resourceGroups/my-rg/providers/Microsoft.Sql/servers/my-server \ --condition "avg deadlocks > 10" \ --window-size 1h ```

Step 10: Use Query Hints Carefully

```sql -- UPDLOCK hint prevents read-deadlock patterns SELECT * FROM Orders WITH (UPDLOCK, HOLDLOCK) WHERE OrderId = 1; -- Now safe to update without another transaction interfering

-- NOLOCK hint avoids read locks (but may read dirty data) SELECT * FROM Inventory WITH (NOLOCK); -- Use only for read-only reporting queries

-- ROWLOCK hint for finer-grain locking UPDATE Orders WITH (ROWLOCK) SET Status = 'Processing' WHERE OrderId = 1; ```

Deadlock Resolution Strategies

Strategy	Impact	Use Case
Consistent lock order	Prevents	All multi-table transactions
Short transactions	Reduces	Long-running batch jobs
RCSI	Eliminates read locks	High read/write mix
Retry logic	Resolves	Transient deadlocks
Snapshot isolation	Prevents	Read-heavy workloads

Verification

```sql -- After implementing fixes -- Monitor deadlock count over time sqlcmd -S my-server.database.windows.net -d my-database -U admin -P password -Q " SELECT COUNT(*) as DeadlockCount FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE s.name = 'deadlock_capture' "

-- Should show reduced deadlock count

-- Query performance impact SELECT * FROM sys.dm_exec_query_stats WHERE execution_count > 100 ORDER BY total_elapsed_time DESC; ```

Prevention

To prevent Azure SQL database deadlock issues from recurring, implement these proactive measures:

1. Monitor Deadlock Events

yaml

groups:
- name: azure-sql-deadlock
  rules:
  - alert: AzureSQLDeadlockRateHigh
    expr: |
      rate(azure_sql_deadlocks_total[5m]) > 0.1
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "High rate of Azure SQL deadlocks"

2. Use Consistent Lock Ordering

```sql -- Always access tables in same order across transactions -- Transaction A: orders then items BEGIN TRANSACTION; UPDATE orders SET status = 'processing' WHERE id = 1; UPDATE order_items SET processed = 1 WHERE order_id = 1; COMMIT;

-- Transaction B: same order (not items then orders) BEGIN TRANSACTION; UPDATE orders SET status = 'shipped' WHERE id = 2; UPDATE order_items SET shipped = 1 WHERE order_id = 2; COMMIT; ```

3. Enable Deadlock Monitoring

```sql -- Create extended event session for deadlock capture CREATE EVENT SESSION deadlock_capture ON SERVER ADD EVENT sqlserver.database_deadlock ADD TARGET package0.event_file (SET filename = 'deadlock_capture.xel') WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);

ALTER EVENT SESSION deadlock_capture ON SERVER STATE = START; ```

Best Practices Checklist

[ ] Monitor deadlock events
[ ] Use consistent lock ordering
[ ] Enable deadlock monitoring
[ ] Keep transactions short
[ ] Add appropriate indexes
[ ] Use appropriate isolation level

[Fix Azure SQL Database DTU Limit](/articles/fix-azure-sql-database-dtu-limit)
[Fix Azure SQL Transaction Log Full](/articles/fix-azure-sql-transaction-log-full)
[Fix Azure SQL Query Performance Slow](/articles/fix-azure-sql-database-dtu-limit)

[Technical troubleshooting: Fix Azure Aks Pod Crashloopbackoff Issue in Azure](azure-aks-pod-crashloopbackoff)
[Technical troubleshooting: Fix Azure Api Management Policy Expression Runtime](azure-api-management-policy-expression-runtime-error)
[Technical troubleshooting: Fix Azure App Configuration Feature Flag Not Refre](azure-app-configuration-feature-flag-not-refreshing)
[Technical troubleshooting: Fix Azure App Service 503 Always On Disabled Issue](azure-app-service-503-always-on-disabled)
[Technical troubleshooting: Fix Azure Application Gateway Err SSL Unrecognized](azure-application-gateway-err-ssl-unrecognized-name-alert)

Was this guide helpful?

Related search paths

People also search for

If the symptom is close but not identical, these search paths usually surface the right neighboring fixes faster than scrolling the full archive.

Azure SQL Database Deadlock Azure SQL Database Deadlock Azure Azure SQL Database Deadlock troubleshooting Azure SQL Database Deadlock fix Resolve Azure SQL deadlocks by analyzing deadlock graphs, optimizing transaction order, and implementing retry logic Azure Resolve Azure SQL deadlocks by analyzing deadlock graphs, optimizing transaction order, and implementing retry logic

Explore Related Topics

Browse Guides from Other Categories

Discover troubleshooting guides from related categories to expand your knowledge.

FAQ

Azure Troubleshooting FAQs

Common questions about troubleshooting and preventing similar issues

How do I know if this azure-errors troubleshooting guide applies to my situation?

This guide is designed for azure-errors issues. If you're experiencing similar symptoms described in the article, follow the step-by-step instructions. Start with the most common causes and work through the diagnostic process.

Is it safe to follow these azure-errors troubleshooting steps?

Yes, all steps are designed to be safe and non-destructive. We recommend creating backups before making significant changes and testing each step before proceeding to the next.

How long does it typically take to resolve this type of azure-errors issue?

Most azure-errors issues can be resolved within 30 minutes to 2 hours, depending on the complexity and root cause. Follow the troubleshooting flow to identify and fix the problem efficiently.

How can I prevent this azure-errors issue from happening again?

Regular maintenance, monitoring, and following best practices for azure-errors configuration can help prevent recurrence. Consider implementing automated checks and alerts for early detection.

Written by

FixWikiHub Editorial Team

Our editorial team consists of experienced DevOps engineers, systems administrators, and cloud architects with hands-on experience in production environments across AWS, Azure, GCP, and on-premises infrastructure.

Every guide undergoes technical review for accuracy and is updated when software versions, commands, or best practices change.

Last updated: Apr 3, 2026

About our team

Important Notice

Disclaimer & Safety Guidelines

The troubleshooting steps in this guide are provided for educational and informational purposes. Before applying any changes to production systems:

Test in a staging environment first — Always verify commands and configurations in a non-production environment before deploying to live systems.
Create backups — Ensure you have current backups of databases, configurations, and critical files before making changes.
Understand the impact — Review how each step may affect your specific environment, dependencies, and users.
Consult official documentation — This guide supplements, but does not replace, official vendor documentation and best practices.

FixWikiHub is not responsible for any damages arising from the use of this content. See our Terms of Use for more information.

Resources

Official Documentation & Further Reading

For authoritative information, consult the official documentation for the technologies discussed in this guide. Our troubleshooting content supplements, but does not replace, vendor documentation.

AWS Documentation — Official Amazon Web Services guides and API references
Kubernetes Documentation — Official Kubernetes documentation
Nginx Documentation — Official Nginx web server documentation
Apache Documentation — Official Apache HTTP Server documentation
Docker Documentation — Official Docker container documentation

Fix Azure SQL Database Deadlock

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Step 1: Enable Deadlock Monitoring

Step 2: Query Deadlock Graph

Step 3: Analyze Deadlock Graph

Step 4: Standardize Lock Order

Step 5: Reduce Transaction Duration

Step 6: Implement Retry Logic

Step 7: Use Read Committed Snapshot

Step 8: Optimize Foreign Key Constraints

Step 9: Monitor Deadlock Rate

Step 10: Use Query Hints Carefully

Deadlock Resolution Strategies

Verification

Prevention

1. Monitor Deadlock Events

2. Use Consistent Lock Ordering

3. Enable Deadlock Monitoring

Best Practices Checklist

People also search for

Browse Guides from Other Categories

WordPress

SSL

DNS

Azure Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading

Fix Azure SQL Database Deadlock

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Step 1: Enable Deadlock Monitoring

Step 2: Query Deadlock Graph

Step 3: Analyze Deadlock Graph

Step 4: Standardize Lock Order

Step 5: Reduce Transaction Duration

Step 6: Implement Retry Logic

Step 7: Use Read Committed Snapshot

Step 8: Optimize Foreign Key Constraints

Step 9: Monitor Deadlock Rate

Step 10: Use Query Hints Carefully

Deadlock Resolution Strategies

Verification

Prevention

1. Monitor Deadlock Events

2. Use Consistent Lock Ordering

3. Enable Deadlock Monitoring

Best Practices Checklist

Related Issues

Related Articles

People also search for

Share this guide

More Azure Troubleshooting Guides

Browse Guides from Other Categories

Azure Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading