{"id":1749,"date":"2014-01-16T00:00:00","date_gmt":"2014-01-16T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-deadlocks-by-example\/"},"modified":"2026-03-18T14:33:01","modified_gmt":"2026-03-18T14:33:01","slug":"sql-server-deadlocks-by-example","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/performance-sql-server\/sql-server-deadlocks-by-example\/","title":{"rendered":"SQL Server Deadlocks: Types, Causes & Prevention Guide"},"content":{"rendered":"
\n

A SQL Server deadlock occurs when two or more sessions form a circular lock chain – each session holds a lock that the other needs, and neither can proceed. SQL Server automatically detects deadlocks via its lock monitor and kills one session (the \u201cdeadlock victim,\u201d error 1205), rolling back its transaction. The key difference from blocking: blocking is transient (the head blocker will eventually finish), while a deadlock is permanent without intervention.<\/p>\n

This guide demonstrates the most common deadlock types – bookmark deadlocks, serializable key-range deadlocks, and cascading multi-session deadlocks – with reproducible code and deadlock graphs you can run on your own instance, plus strategies for preventing each type.<\/p>\n

Introduction<\/h2>\n

For each type of deadlock, we’ll review ‘typical’ deadlock graphs and discuss the signature that distinguishes each one, so that you can recognize it if you see it on your own systems. We’ll also consider the root causes of each type of deadlock, the code patterns that make them a possibility, how to avoid them recurring, and the need to deal with deadlocks, and all other SQL Server errors gracefully, with error handling and retries.<\/p>\n

The Difference between Severe Blocking and Deadlocking<\/h1>\n

In my experience, developers and DBAs often think that their SQL Server instance is experiencing deadlocks when, really, it is experiencing severe blocking.<\/p>\n

Blocking occurs when session A requests a lock on a resource (typically a row, page or table), but SQL Server cannot grant that lock because session B already holds a non-compatible lock on that resource.<\/p>\n

For example, let’s assume that session B is in the process of modifying a row in the Invoices<\/code> table. The session’s associated process (thread) currently holds an Intent-Exclusive (IX) lock on both the table and the page that contains the row, and an X lock on the row. Simultaneously, session A needs to read a few pages on same table. Its associated process acquires an Intent-Shared (IS) lock on the table (since IS and IX lock mode are compatible) and then attempts to acquire an S lock on the pages it needs to read. However, session B’s process holds an IX lock on one of the pages that contains some of the rows session A needs. S locks and IX locks are incompatible, and so session B’s thread blocks session A’s until the former completes its work and releases the locks.<\/p>\n

\n

Lock modes and lock compatibility<\/h4>\n

I don’t have space in this piece for a fuller discussion of lock modes and compatibility. See Further Reading<\/em> at the end of the article for some useful references.<\/p>\n<\/div>\n

This is a transient situation and can be completely resolved by the session B completing its work and releasing its locks. It is possible to have extensive blocking chains where multiple sessions are blocked waiting for a session that itself is blocked waiting for another session that is blocked and so on, repeating multiple time. However, at the head of the blocking chain will be a head ‘blocker’ that is not waiting for a lock. It may be waiting for some other resource, such as a latch, memory, or IO, but at least one session will not be waiting for a lock, and the blocking chain will clear as soon as the head blocker can continue processing.<\/p>\n

A deadlock<\/strong> is different; it occurs when two or more sessions are waiting for each other, in such a way that none can complete. A deadlock can be viewed as a circular lock chain, where every single process in the blocking chain is waiting for one or more other processes in that same blocking chain.<\/p>\n

Consider the simplest possible deadlock, with two sessions, two processes and two resources (later sections will demonstrate deadlocks that are more complex). A deadlock occurs in two steps. In the first, each of the two processes requests and acquires a lock. This will be within a transaction, explicit or not, and hence neither process will release these locks immediately.<\/p>\n

In the second step, each of the two processes requests a lock on the resource on which the competing session holds a lock that is incompatible with the requested lock.<\/p>\n

At this point, process 1 cannot continue until it receives the lock that it wants on page 1:4224. It cannot get that lock until process 2 finishes and releases its lock on that page. Process 2 cannot continue until it receives the lock that it wants on page 1:1370. It cannot get that lock until process 1 finishes and releases its lock on that page.<\/p>\n

At this point, neither process can proceed; we have a deadlock. Without intervention, these two processes would sit forever waiting for each other. Fortunately, SQL Server automatically detects deadlocks and intervenes on our behalf.<\/p>\n

Read also: <\/strong>SQL Server blocked process report<\/a><\/p>\n

SQL Server’s Automatic Deadlock Detection and Resolution<\/h2>\n

SQL Server’s lock monitor has a deadlock detector that periodically checks the locks to see if there are any circular locking chains. If it finds any, it selects one of the sessions associated with a suspended thread, kills it, rolls back its transaction and releases its locks. This allows the other session to continue executing.<\/p>\n

The killed session, known as the deadlock victim<\/strong>, receives error 1205:<\/p>\n

Transaction (Process ID 75) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.<\/span><\/p>\n

The lock monitor picks the deadlock victim based, firstly, on the setting of DEADLOCK_PRIORITY<\/code> for each session and, secondly (in the event of a tie) on the amount of work that it will take to roll back each of the open transactions.<\/p>\n

The DEADLOCK_PRIORITY<\/code> is a session-scoped setting that establishes the relative importance that the session completes its work should it become embroiled in a deadlock. It can be set to HIGH<\/code>, NORMAL<\/code> or LOW<\/code>, with NORMAL<\/code> being the default. If we’d prefer SQL Server not to pick a certain session as a deadlock victim, we can set its DEADLOCK_PRIORITY<\/code> to high.<\/p>\n

\n

Preventing Deadlocks<\/h2>\n

It’s convenient that SQL Server will detect and resolve deadlocks automatically, but that doesn’t mean a DBA can just ignore them. After all, the deadlock victim may well be an important business operation and its failure to run will cause considerable disruption.<\/p>\n

A DBA needs to know when a deadlock occurs in one of their SQL Server instances by alerting on 1205 errors, to capture the information, the deadlock graph<\/strong>, which will detail which processes were involved in the deadlock, and then set about trying to ensure that it does not occur again.<\/p>\n

How to Capture a SQL Server Deadlock Graph<\/h3>\n

A deadlock graph shows us the sessions and resources that were involved in a deadlock. Rather than repeat information ably covered elsewhere, I’m going to refer you to Jonathan Kehayias’ article, Handling Deadlocks in SQL Server<\/a>, for background details of the various techniques by which to capture deadlock graphs, including various Trace Flags, the Profiler deadlock graph event, Service Broker event notifications, and Extended Events.<\/p>\n

Prior to SQL Server 2008, if a deadlock occurred in SQL Server, we’d have to enable trace flags, or set up a server-side trace, and wait for the deadlock to recur. In SQL Server 2008 and later, we can retrieve deadlock graphs retrospectively from the extended events system_health<\/code> session. I used the technique in this article, as frankly it is the most straightforward way to get the deadlock graphs, using Jonathan’s query. Listing 1 shows how to return the deadlock graph from the ring_buffer<\/code> target the<\/code>system_health<\/code> event session (the code download also contains the equivalent code for the event_file<\/code> target). This version of the query is specific to SQL Server 2012; see the previously referenced article for the 2008 version.<\/p>\n

SELECT XEvent.query('(event\/data\/value\/deadlock)[1]') AS DeadlockGraph \nFROM ( SELECT XEvent.query('.') AS XEvent \n       FROM ( SELECT CAST(target_data AS XML) AS TargetData \n              FROM sys.dm_xe_session_targets st \n                   JOIN sys.dm_xe_sessions s \n                   ON s.address = st.event_session_address \n              WHERE s.name = 'system_health' \n                    AND st.target_name = 'ring_buffer' \n              ) AS Data \n              CROSS APPLY \n                 TargetData.nodes \n                    ('RingBufferTarget\/event[@name=\"xml_deadlock_report\"]')\n              AS XEventData ( XEvent ) \n      ) AS src;<\/pre>\n
Listing 1: Returning the deadlock graph from the system_health<\/code> event session<\/p>\n
The deadlock graph obtained from the system_health<\/code> extended events session is extremely similar both to the error log output for traceflag 12<\/code>2<\/code>2<\/code> and to the XML captured by SQL Profiler when it is tracing for the deadlock graph event.<\/p>\n
How to Read a Deadlock Graph<\/h3>\n
In the next section, we’ll start our tour of common types of deadlocks and their resolution. In order to follow along, you’ll need to know your way around a deadlock graph, so let’s take a brief tour.<\/p>\n
I generated an example deadlock by executing two stored procedures, UpdateCustomerLatestOrderStatus<\/code> and AddOrder<\/code> (which we’ll use again, and discuss in more detail, later) though any simple deadlock will do at this early stage, since we’re only interested at this stage in the overall structure of the resulting deadlock graph.<\/p>\n
Generate a deadlock and then run retrieve the deadlock graph, for example by running Listing 1 to retrieve it from the system_health<\/code> event session. Figure 1 shows my deadlock graph, in XML format.<\/p>\n
\"1926-8252646b-cc9d-4f3b-85a6-dff9c3af700\"<\/p>\n
Figure 1: A sample deadlock graph showing the processes and resources sections<\/p>\n
The Extended Events live data viewer, built into SSMS in SQL Server 2012, as well as tools such as Profiler, can present a GUI representation of the deadlock graph. However, I prefer looking at the ‘raw’ XML version. While most of the information in the deadlock graph is accessible from the GUI representation, it’s not as easy to find and is not all in one place. I find myself clicking on the various sessions and resources multiple times to get the whole picture.<\/p>\n
An XML deadlock graph has two main sections, labelled (1) and (2) in Figure 1.<\/p>\n
    \n
  1. Processes<\/strong> section – details all the processes involved in the deadlock, what they were running, what isolation level they were in and more<\/li>\n
  2. Resources<\/strong> section – lists all the resources that were involved in the deadlock, which locks each process acquired and which locks they requested.<\/li>\n<\/ol>\n
    I like to start by looking at the resources section, to see which resources the processes were fighting over and what types of locks they were requesting. Once I have a picture of what locks were involved, then I go back and look at the details of the processes to see what they were doing.<\/p>\n
    There will be at least two entries here, but there may be more. Each entry starts with a description of the resource and then lists the processes that either held a lock on that resource or requested a lock on that resource. Locks here will mainly be key, RID, page or table, with more exotic range locks possible if the query was running under SERIALIZABLE<\/code> isolation. It’s also possible to have non-lock related resources here, especially if either a query was running in parallel. Start by noting the granularity and mode of the locks and the objects involved. Are there table level locks held or requested? How many different tables are involved? How many indexes are involved?<\/p>\n
    Next, we move on to the processes section, which contains an entry for every thread involved in the deadlock. Notice that I say thread, not session, because if a session is running a parallel query, it can appear multiple times in this section. This section of the graph provides a wealth of information, including login names, host names, isolation level, times, session settings and more.<\/p>\n
    The most useful information, generally, is the isolation level under which each query was running and the details of which statement completed the deadlock. I say ‘completed’ the deadlock, because the statement listed in the deadlock graph can be just the one that the session was running at the point that the deadlock detector identified that this session was part of a deadlock.<\/p>\n
    Common types of SQL Server Deadlocks<\/h2>\n
    Here we begin our dissection of the most common types of SQL Server deadlock. We’ll review deadlock graphs for each, discuss what distinguishes each type, and then consider what causes them and how to avoid them in future.<\/p>\n
    In each case, the deadlock graphs I present are representative of real graphs, produced on real SQL Server instances operating under concurrent workloads. Some require some tricks and contrivances to reproduce on an isolated test instance, other are simply very hard to reproduce ‘on demand. The code download for this article contains code samples that will allow you to reproduce the reader-writer, writer-writer, key lookup and serializable deadlock types.<\/p>\n
    However, please bear in mind, firstly, that your output won’t match exactly what I present in this article, though the basic signature will be similar. Secondly, that the code download examples are contrived specifically to produce the deadlock. They in no way represent code you’d hope or expect to see on a production system.<\/p>\n
    Reader-Writer Deadlocks<\/h3>\n
    A reader-writer deadlock is a deadlock between a statement that is reading and a statement that is performing some form of data modification.<\/p>\n
    When you look at the resources involved, you’ll see that the signature of this form of deadlock is locks that are all either a shared (S) lock granted and an exclusive (X) lock requested or an X lock granted and an S lock requested.<\/p>\n
    In its default isolation level (READ COMMITTED<\/code>), SQL Server hold S locks only until the statement completes. As such, reader-writer deadlocks are most prevalent in code that has data modifications followed by a select<\/code> query, within a transaction, or in code that has requested a higher isolation level, meaning that SQL Server holds S locks till the end of the transaction, either intentionally or because the client libraries default to a higher isolation level.<\/p>\n
    There is one very easy fix for this form of deadlock, and that is to use a row-version based isolation level, either READ COMMITTED SNAPSHOT<\/code> or SNAPSHOT<\/code>. In the row-version based isolation levels, readers do not take locks and instead use row versions for isolation. No shared locks means no reader-writer deadlocks.<\/p>\n
    \n
    Transaction Isolation levels<\/h4>\n
    Again, there is not room in this article for a more detailed description of the different transaction isolation levels, and how each prevents read phenomena, such as dirty reads, non-repeatable reads and so on. See Further Reading<\/em>.<\/p>\n<\/div>\n
    The sample deadlock graph in Figure 1 is, in fact, one generated by a reader-writer deadlock and we’re going to drill into the details of that graph here. As discussed earlier, we’ll start with the resources section.<\/p>\n
    <resource-list> \n   <pagelock fileid=\"1\" pageid=\"649\" dbid=\"23\" objectname=\"\" id=\"lock5e00300\"\" \n          mode=\"X\" associatedObjectId=\"72057594038845440\"> \n      <owner-list> <owner id=\"process5c13048\"\"\"\" mode=\"X\" \/> \n      <\/owner-list> \n      <waiter-list> \n         <waiter id=\"process5c4ebc8\"\"\"\" mode=\"S\" requestType=\"wait\" \/> \n      <\/waiter-list> \n   <\/pagelock> \n   <pagelock fileid=\"1\" pageid=\"192\" dbid=\"23\" objectname=\"\" id=\"lock62da600\"\" \n             mode=\"X\" associatedObjectId=\"7205759403877904\"> \n      <owner-list> \n         <owner id=\"process5c4ebc8\"\"\"\" mode=\"X\" \/> \n      <\/owner-list> \n      <waiter-list> \n         <waiter id=\"process5c13048\"\"\"\" mode=\"S\" requestType=\"wait\" \/> \n      <\/waiter-list> \n   <\/pagelock> \n<\/resource-list>\n<\/pre>\n
    Listing 2: The resources section for a reader-writer deadlock<\/p>\n
    We have two processes here, for the moment I’m going to call them 48<\/code> and c8<\/code> (the last two letters of their owner_id<\/code>). There are two resources, page 649 in database 23 and page 192 in database 23. With no object name given for the page lock (key locks and object locks give the name), we’re going to have to do a little bit of work to do to identify the table.<\/p>\n
    The first step, however, is to identify the database, using the db_name()<\/code> function. Once we have the database name, we can use the associatedObjectID<\/code> (which in this case is not an objectID<\/code>) to get the table name.<\/p>\n
    SELECT OBJECT_NAME(p.object_id) AS TableName , \n       i.name AS IndexName \nFROM sys.partitions AS p \n     INNER JOIN sys.indexes AS i ON p.object_id = i.object_id \n                                    AND p.index_id = i.index_id \nWHERE partition_id = 72057594038845440\n<\/pre>\n
    Listing 3: Obtaining object names from partition IDs.<\/p>\n
    We can identify the second resource the same way and it turns out that the involved tables were Customers<\/code> and Orders<\/code>, the clustered index of both.<\/p>\n
    To work out the sequence of events that lead to the deadlock, we look at the owner-list<\/code> and waiter-list<\/code> for each resource. The process listed in the owner-list<\/code> is the one that had the lock, the process or processes in the waiter-list<\/code> are the ones that had requested the lock and were waiting for it.<\/p>\n
    Using those lists, we can see that Process 48<\/code> had an exclusive lock on the page in Orders<\/code> and Process c8<\/code> had an exclusive lock on the page in Customers<\/code>. That was the first step. Then Process 48<\/code> requested a read lock on the locked page in Customers<\/code> and Process c8<\/code> requested a read lock on the locked page in Orders<\/code>.<\/p>\n
    At this point, even without looking at the processes section, there’s enough information to consider potential fixes. If the order of either, or both, sets of statements were reversed and the queries are running in READ<\/code>COMMITTED<\/code>, then this deadlock wouldn’t occur because under that isolation level shared locks are released no later than the end of the query that requested the locks and so the shared locks would be released before the update started. If we could move either SELECT<\/code> outside the transaction, then this deadlock wouldn’t occur. These won’t necessarily be the actual solutions implemented, but they’re worth keeping in mind.<\/p>\n
    For now, however, let’s move on to the process section. In Listing 4, I’ve removed some bits from the XML to keep the size down and applied some manual formatting to make the listing easier to read on the page.<\/p>\n
    <process-list> \n   <process id=\"process5c4ebc8\"\"\"\" waitresource=\"PAGE: 23:1:649\"\n           waittime=\"2377\" ownerId=\"533054\" \n           transactionname=\"user_transaction\" \n           lasttranstarted=\"2013-09-27T17:38:55.823\" XDES=\"0x8a8c0e80\" \n           lockMode=\"S\" schedulerid=\"8\" kpid=\"7464\" status=\"suspended\" \n           spid=\"52\" sbid=\"0\" ecid=\"0\" priority=\"0\" trancount=\"2\" \n           lastbatchstarted=\"2013-09-27T17:38:55.823\" \n           lastbatchcompleted=\"2013-09-27T17:38:55.770\" \n           clientapp=\"Microsoft SQL Server Management Studio - Query\" \n           hostname=\"MyHost\" hostpid=\"6188\" loginname=\"MyLogin\" \n           isolationlevel=\"read committed (2)\" xactid=\"533054\" currentdb=\"23\" \n           lockTimeout=\"4294967295\" clientoption1=\"671090784\" \n           clientoption2=\"390200\"> \n      <executionStack> \n      <frame procname=\"\" line=\"18\" stmtstart=\"688\" stmtend=\"794\" \n             sqlhandle=\"0x030017005a33f607c1db0e0146a200000100000000000000\" \/> \n      <frame procname=\"\" line=\"2\" stmtstart=\"24\" stmtend=\"222\" \n             sqlhandle=\"0x0100170066684218307e8f8a000000000000000000000000\" \/> \n      <\/executionStack> \n      <inputbuf> \n       EXEC dbo.UpdateCustomerLatestOrderStatus @CustomerID= 2831, \n                                                @OrderStatus = 'F' \n      <\/inputbuf> \n   <\/process> \n   <process id=\"process5c13048\"\"\"\" waitresource=\"PAGE: 23:1:192\" \n            waittime=\"6290\" ownerId=\"533059\" \n            transactionname=\"user_transaction\" \n            lasttranstarted=\"2013-09-27T17:39:01.863\" XDES=\"0x8c4eae80\" \n            lockMode=\"S\" schedulerid=\"2\" kpid=\"6180\" status=\"suspended\" \n            spid=\"57\" sbid=\"0\" ecid=\"0\" priority=\"0\" trancount=\"3\" \n            lastbatchstarted=\"2013-09-27T17:39:01.863\" \n            lastbatchcompleted=\"2013-09-27T17:39:00.773\" \n            clientapp=\"Microsoft SQL Server Management Studio - Query\" \n            hostname=\"MyHost\" hostpid=\"6188\" loginname=\"MyLogin\" \n            isolationlevel=\"read committed (2)\" xactid=\"533059\" currentdb=\"23\" \n            lockTimeout=\"4294967295\" clientoption1=\"671090784\" \n            clientoption2=\"390200\"> \n      <executionStack> \n         <frame procname=\"\" line=\"7\" stmtstart=\"204\" stmtend=\"452\" \n                sqlhandle=\"0x030017009357ea08c5db0e0146a200000100000000000000\" \/> \n         <frame procname=\"\" line=\"2\" stmtstart=\"24\" stmtend=\"142\" \n                sqlhandle=\"0x01001700f2826f05f07e548d000000000000000000000000\" \/> \n      <\/executionStack> \n      <inputbuf> \n         EXEC dbo.AddOrder @CustomerID= 2831, @OrderTotal = 137.42 \n      <\/inputbuf> \n   <\/process> \n<\/process-list><\/pre>\n
    Listing 4: The processes section for a reader-writer deadlock<\/p>\n
    There’s a lot of information in there. To start, the process ID matches the process IDs listed in the resources section. We can see the client application, host name and login name of both sessions. If multiple applications use the server, this can help narrow down the culprit. Occasionally, you may find that the deadlocks originate from ad-hoc queries from Management Studio. In this case, fixing the deadlock may be as simple as asking the user to stop running that query or to run it elsewhere or at another time.<\/p>\n
    We can see that the transactionname<\/code> is user_transaction<\/code>, indicating that the code formed part of an unnamed, explicit transaction.<\/p>\n
    The wait<\/code>resource<\/code> shows the database, objects and pages on which the processes are deadlocked, and reflect what we saw in the resources section. If the isolationlevel<\/code> indicates that an application or procedure has requested a higher isolation level, it’s worth investigating whether or not this is a true requirement, or just a default, but unnecessary, setting.<\/p>\n
    The input buffer (inputbuf<\/code>) lists which statements each session sent to SQL Server. In both cases, in this example, it’s a call to a stored procedure, so our next step is to investigate these procedures. Process c8<\/code><\/code>calls the procedure U<\/code>pdateCustomerLatestOrderStatus<\/code> and process 48<\/code> calls the procedure AddOrder<\/code>.<\/p>\n
    CREATE PROCEDURE UpdateCustomerLatestOrderStatus\n    (\n      @CustomerID INT ,\n      @OrderStatus CHAR(1)\n    )\nAS \n    BEGIN TRANSACTION\n    UPDATE  Customers\n    SET     LatestOrderStatus = @OrderStatus\n    WHERE   CustomerID = @CustomerID\n\n    SELECT  *\n    FROM    Orders\n    WHERE   CustomerID = @CustomerID \n    COMMIT\nGO\n\nCREATE PROCEDURE AddOrder\n    (\n      @CustomerID INT ,\n      @OrderTotal NUMERIC(10, 2)\n    )\nAS \n    BEGIN TRANSACTION\n\n    INSERT  INTO Orders\n            ( CustomerID ,\n              OrderDate ,\n              OrderTotal ,\n              OrderStatus\n            )\n    VALUES  ( @CustomerID,\n              GETDATE() ,\n              @OrderTotal ,\n              'A'\n            );\n\n    SELECT  CustomerID,\n            CustomerName ,\n            RegionID ,\n            OrderLimit ,\n            'A' AS LatestOrderStatus\n    FROM    dbo.Customers AS c\n    WHERE   CustomerID = @CustomerID\n\n    UPDATE  Customers\n    SET     LatestOrderStatus = 'A'\n    WHERE   CustomerID = @CustomerID;\n\n    COMMIT\nGO<\/pre>\n
    Listing 5: The UpdateCustomerLatestOrderStatus<\/code> and AddOrder<\/code> stored procedures<\/p>\n
    For the purposes of this article, please try to ignore the complete lack of error handling in these procedures. If they were real production code, there would be a lot more verification, error handling and checks. If they also appear oddly written, that’s intentional as I wrote them in a way that ensured they would cause a deadlock.<\/p>\n
    To understand the deadlock, we need to match the code that ran to the locks listed in the resource<\/code>s<\/code> section of the deadlock graph.<\/p>\n
    We’ll start with process c8<\/code>, which the resources section told us had taken an exclusive (X) lock on a page in Customer<\/code>s<\/code> and then requested a Shared (S) lock on a page in Orders<\/code>. Since UpdateCustomerLatestOrderStatus<\/code> contains only two queries, we can deduce easily that the X lock this process hold results from the update<\/code> of customers<\/code> and the shared lock it requested results from the select<\/code> on Orders<\/code>.<\/p>\n
    Process <\/code>4<\/code>8<\/code> first took an X lock on the page in Orders<\/code>, which would be the INSERT<\/code> into Orders<\/code>. It then requested an S lock to perform a select<\/code> against customers<\/code>, but could not proceed as process c8<\/code> had an incompatible lock on the page it needed. As this point, we had a deadlock and process 48 never even reached the subsequent update of customers<\/code>.<\/p>\n
    Now we know what caused the deadlock, it’s relatively easy, in this case, to prevent it. Let’s start with the UpdateCustomerLatestOrderStatus<\/code> stored procedure. The explicit transaction in this procedure is only necessary if there are multiple data modification statements that need to form an atomic unit, or if the result of the update<\/code> modification could affect the result of the subsequent select<\/code>. In fact, we have a single data modification followed by a select<\/code> on a different table, which will return the same results regardless of whether the update<\/code> commits or rolls back. We can also safely say that there’s no trigger because there are no indication of it in the deadlock graph (it would appear in the executionStack<\/code> sub-section).<\/p>\n
    In short, we can remove the explicit transaction, as shown in Listing 6.<\/p>\n
    CREATE PROCEDURE UpdateCustomerLatestOrderStatus (\n   @CustomerID INT,\n   @OrderStatus CHAR(1)\n   )\nAS\n   UPDATE Customers SET LatestOrderStatus = @OrderStatus\n   WHERE CustomerID = @CustomerID\n   \n   SELECT * FROM Orders WHERE CustomerID = @CustomerID GO\n<\/pre>\n
    Listing 6: Remove the explicit transaction from UpdateCustomerLatestOrderStatus<\/code><\/p>\n
    This procedure can no longer cause the deadlock. However, to be sure, let’s fix the AddOrder<\/code> procedure too. This one’s a little harder.<\/p>\n
    The select<\/code> is against the same rows as the update<\/code> right after it. If we look at what it’s doing, the select<\/code> is returning the customer row as it will be after the update<\/code> completes (it’s specifying the LatestOrder<\/code>Status<\/code> as A<\/code>, which is the value to which the update<\/code> sets it).<\/p>\n
    There’s a potential bug here though. If that update<\/code> never commits, the select may return ‘dirty’ data. This may be the developer’s intent but probably isn’t, so I’ll move the select<\/code> outside the transaction and remove the hardcoded value for LatestOrderStatus<\/code>, just letting the select return the value that’s in the table.<\/p>\n
    CREATE PROCEDURE AddOrder (@CustomerID INT, @OrderTotal NUMERIC(10,2))\nAS\n\nBEGIN TRANSACTION\n\n  INSERT INTO Orders (CustomerID, OrderDate, OrderTotal, OrderStatus)\n  VALUES (@CustomerID, GETDATE(), @OrderTotal, 'A');\n\n  UPDATE Customers SET LatestOrderStatus = 'A'\n    WHERE CustomerID = @CustomerID;\nCOMMIT\n\nSELECT  CustomerID ,\n        CustomerName  ,\n        RegionID  ,\n        OrderLimit  ,\n        LatestOrderStatus  \nFROM dbo.Customers AS c WHERE CustomerID= @CustomerIDGO\n<\/pre>\n
    Listing 7: Modifying the AddOrder<\/code> stored procedure to prevent deadlocks<\/p>\n
    If the exact current behavior is required and correct, I could instead move the select<\/code> so that it runs as-is, after the transaction commits.<\/p>\n
    Now these procedures will no longer deadlock, but there is still one potential problem. These two procedures still access the same objects but in different orders. UpdateCustomerLatestOrderStatus<\/code> touches Customers<\/code> first then Orders<\/code>, and AddOrder<\/code> does the reverse order. If a future change wraps the first procedure’s contents in a transaction again, they may well start deadlocking again.<\/p>\n
    One general rule for preventing deadlocks is always access objects in the same order, so let’s make one more fix to UpdateCustomerLatestOrderStatus<\/code>.<\/p>\n
    CREATE PROCEDURE UpdateCustomerLatestOrderStatus (\n CustomerIDINT,\n  @OrderStatus CHAR(1)\n  )\nAS\n  SELECT * FROM Orders WHERE CustomerID= @CustomerID\n  \n  UPDATE Customers SET LatestOrderStatus = @OrderStatus\n  WHERE CustomerID= @CustomerID\n  GO<\/pre>\n
    Listing 8: Modifying UpdateCustomerLatestOrderStatus<\/code> so that it accesses objects in the same order as AddOrder<\/code><\/p>\n
    That should ensure that these two procedures never deadlock again.<\/p>\n
    Writer-Writer Deadlocks<\/h3>\n
    In a writer-writer deadlock both the granted lock and requested lock on a resource are update or exclusive locks. In other words, both operations attempt data modifications.<\/p>\n
    One important thing to note when investigating writer-writer deadlocks is that SQL Server holds exclusive locks until the transaction commits, unlike shared locks which in the default read committed<\/code> isolation level SQL Server holds no longer than the end of the statement (and can in fact be released as soon as it reads the row, before the statement completed).<\/p>\n
    Note also that the Snapshot isolation levels won’t help us with writer-writer deadlocks, as these levels affect only SQL Server’s behavior with regard to S locks. SQL Server will still take exclusive locks for data modifications.<\/p>\n
    This aside, we adopt more or less the same approach to fixing a writer-writer deadlock as we did for fixing a reader-writer deadlock, so I’m not going to go into immense detail. Again, we’ll start with the resources section of the deadlock graph.<\/p>\n
    <resource-list> \n   <pagelock fileid=\"1\" pageid=\"649\" dbid=\"23\" objectname=\"\" \n             id=\"lock61fb800\"\" mode=\"X\" associatedObjectId=\"72057594038845440\"> \n      <owner-list> \n         <owner id=\"process5e3ae08\"\"\"\" mode=\"X\" \/> \n      <\/owner-list> \n      <waiter-list> \n         <waiter id=\"process5e4ebc8\"\"\"\" mode=\"X\" requestType=\"wait\" \/> \n      <\/waiter-list> \n   <\/pagelock> \n   <pagelock fileid=\"1\" pageid=\"192\" dbid=\"23\" objectname=\"\" \n             id=\"lock61fa180\"\" mode=\"X\" associatedObjectId=\"72057594038779904\"> \n      <owner-list> \n         <owner id=\"process5e4ebc8\"\"\"\" mode=\"X\" \/> \n      <\/owner-list> \n      <waiter-list> \n      <waiter id=\"process5e3ae08\"\"\"\" mode=\"X\" requestType=\"wait\" \/> \n      <\/waiter-list> \n   <\/pagelock> \n<\/resource-list><\/pre>\n
    Listing 9: The resources section of a writer-writer deadlock<\/p>\n
    All locks involved are exclusive (X) locks. This means we can’t consider one of the snapshot isolation levels, nor will we be able to fix this by moving statements outside of a transaction.<\/p>\n
    Once again, we see that two processes (c8<\/code> and 08<\/code>) engaged in the deadlock. Once again, we use the db_name()<\/code> function and Listing 3 to identify the objects involved and it’s the tables Orders<\/code> and Customers<\/code>.<\/p>\n
    According to the resource section, the order of events was as follows<\/p>\n
      \n
    1. process 08<\/code> takes an exclusive lock on a page in Orders<\/code><\/li>\n
    2. process c8<\/code> takes an exclusive lock on a page in Customers<\/code><\/li>\n
    3. process 08<\/code> requests an exclusive lock on a page in Customers<\/code><\/li>\n
    4. process c8<\/code> requests an exclusive lock on a page in Orders<\/code>.<\/li>\n<\/ol>\n
      The processes section is near identical to the one for the reader-writer deadlock with the primary difference being in the content of the input buffers.<\/p>\n
      <process-list> \n   <process id=\"process5e4ebc8\"\"\"\" waitresource=\"PAGE: 23:1:649\" \n            waittime=\"2499\" transactionname=\"user_transaction\"\n            lockMode=\"X\" status=\"suspended\" spid=\"57\" \n            clientapp=\"Microsoft SQL Server Management Studio - Query\" \n            hostname=\"MyHost\" loginname=\"MyLogin\" \n            isolationlevel=\"read committed (2)\" currentdb=\"23\"> \n      <executionStack> \n         <frame procname=\"\" line=\"11\" stmtstart=\"412\" stmtend=\"554\" \n                sqlhandle=\"0x03001700cc7bde093cad600152a200000100000000000000\" \/> \n         <frame procname=\"\" line=\"2\" stmtstart=\"24\" stmtend=\"146\" \n                sqlhandle=\"0x0100170033cf901120240080000000000000000000000000\" \/> \n      <\/executionStack> \n      <inputbuf> \n         EXEC dbo.DispatchOrder @CustomerID= 2831, @OrderID = 100097\n     <\/inputbuf> \n   <\/process> \n   <process id=\"process5e3ae08\"\"\"\" waitresource=\"PAGE: 23:1:192\" \n            waittime=\"2500\" transactionname=\"user_transaction\" \n            lockMode=\"X\" schedulerid=\"6\" status=\"suspended\" spid=\"54\" \n            clientapp=\"Microsoft SQL Server Management Studio - Query\" \n            hostname=\"MyHost\" loginname=\"MyLogin\" \n            isolationlevel=\"read committed (2)\" currentdb=\"23\"> \n      <executionStack> \n         <frame procname=\"\" line=\"11\" stmtstart=\"512\" stmtend=\"672\" \n                sqlhandle=\"0x030017009357ea0869a9600152a200000100000000000000\" \/> \n         <frame procname=\"\" line=\"2\" stmtstart=\"24\" stmtend=\"142\" \n                sqlhandle=\"0x01001700f2826f05a0b8d485000000000000000000000000\" \/> \n      <\/executionStack> \n      <inputbuf> \n         EXEC dbo.AddOrder @CustomerID= 2831, @OrderTotal = 137.42\n      <\/inputbuf> \n   <\/process> \n<\/process-list><\/pre>\n
      Listing 10: Processes section of a deadlock graph for a writer-writer deadlock<\/p>\n
      It’s our old friend AddOrder<\/code>, fixed to prevent reader-writer deadlock, but now engaged in a writer-writer deadlock with a different procedure, DispatchOrder<\/code>, shown in Listing 11.<\/p>\n
      ALTER PROCEDURE dbo.DispatchOrder\n    (\n      @CustomerID INT ,\n      @OrderID INT\n    )\nAS\n    BEGIN TRANSACTION\n    UPDATE  Customers\n    SET     LatestOrderStatus = 'D'\n    WHERE   CustomerID= @CustomerID;\n\n    UPDATE  dbo.Orders\n    SET     OrderStatus = 'D'\n    WHERE   OrderID = @OrderID;\n\n    SELECT  *\n    FROM    dbo.Customers AS c\n            INNER JOIN dbo.Orders AS o ON c.CustomerID= o.CustomerID\n    WHERE   c.CustomerID= @CustomerID\n            AND OrderID = @OrderID;\n    COMMIT\n\nGO<\/pre>\n
      Listing 11: The DispatchOrder<\/code> stored procedure<\/p>\n
      The sequence of the deadlock is as follows<\/p>\n

Integer values for DEADLOCK_PRIORITY<\/code><\/h4>\n

We can also set the DEADLOCK_PRIORITY<\/code> to any integer value between -10 and +10, HIGH<\/code> is equivalent to +5, NORMAL<\/code> to 0 and LOW<\/code> to -5. I recommend sticking with the named options. If someone is setting very fine-grained deadlock granularities, for example setting sessions to deadlock priorities 2, 3 or 7, there is likely a larger problem at play (i.e. <\/em>lots of deadlocks and someone spending a lot of time prioritizing which processes are most important, rather than fixing what is causing the deadlocks).<\/p>\n<\/div>\n

If two sessions deadlock, the lock monitor will select as the deadlock victim the one with the lower value for DEADLOCK_PRIORITY<\/code>. If each has identical values for DEADLOCK_PRIORITY<\/code>, then the lock monitor considers the resources required to roll back the ‘competing’ transactions; the one that requires the least work to roll back will be the deadlock victim. The lock monitor takes no account of how long a transaction has been running or how much work it has done; just the cost of rolling it back. If a deadlock occurs between session A, which has been running a SELECT<\/code> for an hour, and session B that is running a single-row UPDATE<\/code>, and both have the same DEADLOCK_PRIORITY<\/code>, session A will be the deadlock victim as it made no data changes and hence costs nothing to roll back.<\/p>\n

Read also:
<\/strong>Extended Events for monitoring<\/a>
The default trace for auditing<\/a>
How to avoid conditional JOINs<\/a>
Identifying and solving index scan problems<\/a><\/p>\n