--- title: SQL Server Deadlock: Build One With Your Own Hands description: A hands-on walkthrough of SQL Server deadlocks, showing how to deliberately create one using two sessions acquiring locks in opposite order. Covers the full... canonical: https://daily.dev/posts/sql-server-deadlock-build-one-with-your-own-hands-mpg3offv0 twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: SQL Server Deadlock: Build One With Your Own Hands | daily.dev og:description: A hands-on walkthrough of SQL Server deadlocks, showing how to deliberately create one using two sessions acquiring locks in opposite order. Covers the full... og:url: https://daily.dev/posts/sql-server-deadlock-build-one-with-your-own-hands-mpg3offv0 og:image: https://api.daily.dev/og/posts/MpG3OfFV0.png og:image:alt: SQL Server Deadlock: Build One With Your Own Hands og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # SQL Server Deadlock: Build One With Your Own Hands **[SQL Authority](https://daily.dev/sources/sql-authority)** · 8 min read · 0 upvotes · 0 comments ## Summary A hands-on walkthrough of SQL Server deadlocks, showing how to deliberately create one using two sessions acquiring locks in opposite order. Covers the full cycle: setup, step-by-step deadlock formation, how SQL Server's lock monitor detects and resolves it via victim selection and error 1205, and how to prevent it using consistent resource access order. Also explains why lowering isolation levels doesn't fix writer-writer deadlocks, how to read deadlock graphs from Extended Events, and provides a production checklist including bounded retries with backoff and jitter. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### Why doesn't lowering the isolation level to READ COMMITTED SNAPSHOT fix a deadlock caused by two transactions updating the same rows in opposite order? Row versioning options like READ UNCOMMITTED, READ COMMITTED SNAPSHOT, and SNAPSHOT reduce reader-writer conflicts by using row versions for reads, but they do not remove writer-writer dependencies. Both transactions still must protect their uncommitted changes with locks, so a cycle can still form. The actual fix for this pattern is enforcing a consistent resource access order across transactions, not adjusting isolation level. _Debugging deadlocks that isolation-level changes won't fix is easier with concrete SQL Server examples surfaced on daily.dev._ ### How do I read SQL Server deadlock graphs from the system_health Extended Events session? Query sys.fn_xe_file_target_read_file against 'system_health*.xel' filtering on object_name = 'xml_deadlock_report' to extract the deadlock XML, which includes participating processes, requested and owned resources, execution context, and victim info. The system_health session runs automatically on SQL Server and Azure SQL Managed Instance but rolls over, so only recent history is retained; Azure SQL Database lacks this built-in session. Reading files needs VIEW SERVER STATE on SQL Server 2019 and earlier, or VIEW SERVER PERFORMANCE STATE/VIEW DATABASE PERFORMANCE STATE on SQL Server 2022 and later. _Tracking permission and tooling changes across SQL Server versions is simpler when relevant deadlock-debugging content is on daily.dev._ ### How does SQL Server choose which transaction becomes the deadlock victim? SQL Server checks DEADLOCK_PRIORITY first, then estimated rollback cost, and can choose randomly when both are equal. The chosen victim's entire transaction is rolled back, including statements that already succeeded, returning error 1205 with severity 13, while the surviving transaction continues and gets both resources. Applications should never assume the same session will always be the victim. _Designing retry logic that survives unpredictable deadlock victim selection is a workload worth following on daily.dev._ ## Similar posts on daily.dev - [What actually happens in a database deadlock?](https://daily.dev/posts/what-actually-happens-in-a-database-deadlock--m6r52cbpe) · Medium · 1 upvotes · 0 comments - [Finding the Root Cause of Slow Queries](https://daily.dev/posts/finding-the-root-cause-of-slow-queries-74zd6atvn) · SQL Authority · 0 upvotes · 0 comments - [Locking and Concurrency Control in .NET 10](https://daily.dev/posts/locking-and-concurrency-control-in-net-10-um1rhzp52) · Waseem .NET Newsletter · 14 upvotes · 0 comments - [Deadlock in Java: Examples, Detection, and Prevention](https://daily.dev/posts/deadlock-in-java-examples-detection-and-prevention-kvyshorrf) · DigitalOcean Community · 0 upvotes · 0 comments - [The lock that killed my migration](https://daily.dev/posts/the-lock-that-killed-my-migration-rpiz4vna3) · The Art of Simplicity · 1 upvotes · 0 comments --- Tags: [#sql](https://daily.dev/tags/sql), [#microsoft-sql-server](https://daily.dev/tags/microsoft-sql-server) [View this post on daily.dev](https://daily.dev/posts/sql-server-deadlock-build-one-with-your-own-hands-mpg3offv0) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"SQL Server Deadlock: Build One With Your Own Hands","url":"https://daily.dev/posts/sql-server-deadlock-build-one-with-your-own-hands-mpg3offv0","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/sql-server-deadlock-build-one-with-your-own-hands-mpg3offv0"},"datePublished":"2026-08-04T01:34:35.807Z","dateModified":"2026-09-14T07:28:31.757Z","description":"A hands-on walkthrough of SQL Server deadlocks, showing how to deliberately create one using two sessions acquiring locks in opposite order. Covers the full...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/5848b0f9643fbb3a2720ca5512d0e13d?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/5848b0f9643fbb3a2720ca5512d0e13d?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"SQL Authority","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"SQL Authority","logo":"https://media.daily.dev/image/upload/s--reCGnvgi--/f_auto,q_auto/v1780213436/logos/sql-authority?_a=BAMAMiWQ0","url":"https://daily.dev/sources/sql-authority"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/sql-server-deadlock-build-one-with-your-own-hands-mpg3offv0","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"sql,microsoft-sql-server","timeRequired":"PT8M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"SQL Authority","item":"https://daily.dev/sources/sql-authority"},{"@type":"ListItem","position":3,"name":"SQL Server Deadlock: Build One With Your Own Hands"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/sql-server-deadlock-build-one-with-your-own-hands-mpg3offv0#faq","mainEntity":[{"@type":"Question","name":"Why doesn't lowering the isolation level to READ COMMITTED SNAPSHOT fix a deadlock caused by two transactions updating the same rows in opposite order?","acceptedAnswer":{"@type":"Answer","text":"Row versioning options like READ UNCOMMITTED, READ COMMITTED SNAPSHOT, and SNAPSHOT reduce reader-writer conflicts by using row versions for reads, but they do not remove writer-writer dependencies. Both transactions still must protect their uncommitted changes with locks, so a cycle can still form. The actual fix for this pattern is enforcing a consistent resource access order across transactions, not adjusting isolation level. Debugging deadlocks that isolation-level changes won't fix is easier with concrete SQL Server examples surfaced on daily.dev."}},{"@type":"Question","name":"How do I read SQL Server deadlock graphs from the system_health Extended Events session?","acceptedAnswer":{"@type":"Answer","text":"Query sys.fn_xe_file_target_read_file against 'system_health*.xel' filtering on object_name = 'xml_deadlock_report' to extract the deadlock XML, which includes participating processes, requested and owned resources, execution context, and victim info. The system_health session runs automatically on SQL Server and Azure SQL Managed Instance but rolls over, so only recent history is retained; Azure SQL Database lacks this built-in session. Reading files needs VIEW SERVER STATE on SQL Server 2019 and earlier, or VIEW SERVER PERFORMANCE STATE/VIEW DATABASE PERFORMANCE STATE on SQL Server 2022 and later. Tracking permission and tooling changes across SQL Server versions is simpler when relevant deadlock-debugging content is on daily.dev."}},{"@type":"Question","name":"How does SQL Server choose which transaction becomes the deadlock victim?","acceptedAnswer":{"@type":"Answer","text":"SQL Server checks DEADLOCK_PRIORITY first, then estimated rollback cost, and can choose randomly when both are equal. The chosen victim's entire transaction is rolled back, including statements that already succeeded, returning error 1205 with severity 13, while the surviving transaction continues and gets both resources. Applications should never assume the same session will always be the victim. Designing retry logic that survives unpredictable deadlock victim selection is a workload worth following on daily.dev."}}]} ```