--- title: "Deadlock in SQL Server" description: "Deadlock in SQL Server is one of the most common problems in database-driven applications, especially in large systems where multiple users access the same data" author: "Ravi Vishwakarma" published: 2026-03-17 updated: 2026-03-18 canonical: https://answers.mindstick.com/blog/99/deadlock-in-sql-server category: "asp.net" tags: ["asp.net"] reading_time: 4 minutes --- # Deadlock in SQL Server Deadlock in [SQL Server](https://www.mindstick.com/articles/34/create-table-in-microsoft-sql-server) is one of the most common problems in database-driven applications, especially in large systems where multiple users access the same data at the same time. [Understanding](https://www.mindstick.com/news/2555/understanding-the-work-of-bionutrients-everything-you-need-to-know) deadlock is very important for [developers](https://www.mindstick.com/blog/302688/handling-errors-in-rust-a-comprehensive-guide-for-developers), DBAs, and backend [engineers](https://www.mindstick.com/blog/12377/10-tips-to-pass-comptia-security-for-future-specialists-and-engineers) because it directly affects performance and data consistency. This article explains deadlock in simple language, with examples, causes, and solutions. ## 1. What is Deadlock? A **deadlock** happens when two or more [transactions](https://www.mindstick.com/interview/307/explain-acid-rule-of-thumb-for-transactions) block each other by holding locks on resources and waiting for the other transaction to release its lock. In simple words: > Transaction A is waiting for Transaction B\ > Transaction B is waiting for Transaction A\ > Both are stuck → This is Deadlock SQL [Server automatically](https://www.mindstick.com/forum/538/copy-one-table-to-another-in-sql-server-automatically) detects this situation and kills one transaction. The killed transaction is called **Deadlock Victim**. ## 2. Example of Deadlock Consider two tables: ```plaintext Users Orders ``` ### Transaction 1 ```plaintext BEGIN TRAN UPDATE Users SET Name = 'A' WHERE Id = 1 WAITFOR DELAY '00:00:05' UPDATE Orders SET Amount = 100 WHERE Id = 1 COMMIT ``` ### Transaction 2 ```plaintext BEGIN TRAN UPDATE Orders SET Amount = 200 WHERE Id = 1 WAITFOR DELAY '00:00:05' UPDATE Users SET Name = 'B' WHERE Id = 1 COMMIT ``` ### What happens? | Step | Transaction 1 | Transaction 2 | | --- | --- | --- | | 1 | Lock Users | Lock Orders | | 2 | Wait for Orders | Wait for Users | | 3 | Both waiting | Deadlock | SQL Server detects deadlock and kills one transaction. Error: ```plaintext Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. ``` ## 3. Why Deadlock Happens Deadlock usually happens because of: ### 1. Different order of table access Bad: ```plaintext T1 → Users → Orders T2 → Orders → Users ``` Good: ```plaintext T1 → Users → Orders T2 → Users → Orders ``` Same order = No deadlock ### 2. Long transactions If transaction runs too long, locks stay longer → higher chance of deadlock. Bad: ```plaintext BEGIN TRAN SELECT * WAIT 10 sec UPDATE COMMIT ``` Good: ```plaintext BEGIN TRAN UPDATE COMMIT ``` ### 3. Missing indexes If index not present, SQL Server scans full table → more locks → deadlock possible. Bad: ```plaintext UPDATE Users WHERE Email = 'abc@test.com' ``` Good: ```plaintext CREATE INDEX IX_Email ON Users(Email) ``` ### 4. Using SELECT without proper isolation Example: ```plaintext SELECT * FROM Orders WITH (UPDLOCK) ``` Locks stay longer → deadlock possible ### 5. Multiple users updating same rows High concurrency systems like: - Banking - E-commerce - Chat apps - Order systems - CMS / Blog systems - Deadlock chances are high. ## 4. How SQL Server Handles Deadlock SQL Server automatically: - Detect deadlock - Select victim - Kill one transaction - Allow other to continue Victim [selection](https://www.mindstick.com/blog/60/populate-records-in-second-listbox-according-to-the-selection-in-first-list-box) based on: - Less cost transaction - DEADLOCK_PRIORITY - Log usage Example: ```plaintext SET DEADLOCK_PRIORITY LOW ``` ## 5. How to Prevent Deadlock ### Rule 1 — Access tables in same order Good: ```plaintext Users → Orders → Payments ``` Always same order in all SP / queries. ### Rule 2 — Keep transactions short Bad: ```plaintext BEGIN SELECT WAIT UPDATE ``` Good: ```plaintext BEGIN UPDATE COMMIT ``` ### Rule 3 — Use proper indexes ```plaintext CREATE INDEX IX_UserId ON Orders(UserId) ``` Less scan = less lock ### Rule 4 — Use TRY CATCH and retry ```plaintext BEGIN TRY BEGIN TRAN UPDATE Users SET Name='A' WHERE Id=1 COMMIT END TRY BEGIN CATCH IF ERROR_NUMBER() = 1205 BEGIN PRINT 'Deadlock detected, retry' END END CATCH ``` Error 1205 = Deadlock ### Rule 5 — Use NOLOCK carefully ```plaintext SELECT * FROM Users WITH (NOLOCK) ``` Pros: - No blocking Cons: - Dirty data - Use only for reports. ## 6. How to Detect Deadlock ### Method 1 — SQL Server Profiler Check event: ```plaintext Deadlock graph ``` ### Method 2 — Extended Events ```plaintext system_health session ``` ### Method 3 — Query ```plaintext SELECT * FROM sys.dm_tran_locks ``` ## 7. Real Life Example Deadlock common in: - Order placing - Payment [processing](https://www.mindstick.com/blog/254/background-processing-in-android) - [Inventory](https://www.mindstick.com/articles/330314/5-inventory-management-techniques-to-boost-efficiency) update - Chat message save - Article update - Bulk email system Large websites like: - Amazon - Flipkart - Facebook - Banking systems - All must handle deadlock. ## 8. Conclusion Deadlock is not a bug.\ Deadlock is a **normal situation in multi-user systems**. Good [developer](https://www.mindstick.com/articles/188260/do-you-know-what-to-look-for-when-hiring-an-app-developer) should: - Write short transactions - Use indexes - Maintain lock order - Handle retry logic - Monitor deadlocks If handled correctly → system becomes stable and fast. --- Original Source: https://answers.mindstick.com/blog/99/deadlock-in-sql-server Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.