I have a stored procedure with the following two transactions under Begin and Commit tran.
UPDATE mytable
SET UserID = @ToUserID
WHERE UserID = @UserID
DELETE FROM mytable
WHERE UserID = @UserID
On running that Store Procedure with multiple executions I get dead lock. Here is deadlock Graph:
<deadlock-list>
<deadlock victim="process16409057468">
<process-list>
<process id="process16409057468" taskpriority="0" logused="912" waitresource="RID: 6:1:2392:152" waittime="3022" ownerId="6283339" transactionname="user_transaction" lasttranstarted="2019-02-08T21:08:24.663" XDES="0x16401b98490" lockMode="U" schedulerid="8" kpid="23924" status="suspended" spid="92" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-02-08T21:08:24.667" lastbatchcompleted="2019-02-08T21:08:24.667" lastattention="1900-01-01T00:00:00.667" clientapp=".Net SqlClient Data Provider" hostname="GYAAN" hostpid="5624" loginname="sa" isolationlevel="read uncommitted (1)" xactid="6283339" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="mytable" line="377" stmtstart="33320" stmtend="33540" sqlhandle="0x030006004f6bf63211085201eaa9000001000000000000000000000000000000000000000000000000000000">
UPDATE mytable
SET UserID = @ToUserID
WHERE UserID = @UserID
</frame>
</executionStack>
<inputbuf>
Proc [Database Id = 6 Object Id = 855010127]
</inputbuf>
</process>
<process id="process163feab3088" taskpriority="0" logused="912" waitresource="RID: 6:1:2392:149" waittime="99" ownerId="6282851" transactionname="user_transaction" lasttranstarted="2019-02-08T21:08:22.107" XDES="0x16401b20490" lockMode="U" schedulerid="3" kpid="33220" status="suspended" spid="81" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-02-08T21:08:22.103" lastbatchcompleted="2019-02-08T21:08:22.107" lastattention="1900-01-01T00:00:00.107" clientapp=".Net SqlClient Data Provider" hostname="GYAAN" hostpid="5624" loginname="sa" isolationlevel="read uncommitted (1)" xactid="6282851" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="mytable" line="382" stmtstart="33650" stmtend="33848" sqlhandle="0x030006004f6bf63211085201eaa9000001000000000000000000000000000000000000000000000000000000">
DELETE FROM mytable
WHERE UserID = @UserID
</frame>
</executionStack>
<inputbuf>
Proc [Database Id = 6 Object Id = 855010127]
</inputbuf>
</process>
</process-list>
<resource-list>
<ridlock fileid="1" pageid="2392" dbid="6" objectname="mytable" id="lock164096b7800" mode="X" associatedObjectId="72057594051493888">
<owner-list>
<owner id="process163feab3088" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process16409057468" mode="U" requestType="wait"/>
</waiter-list>
</ridlock>
<ridlock fileid="1" pageid="2392" dbid="6" objectname="mytable" id="lock163f7fb2c80" mode="X" associatedObjectId="72057594051493888">
<owner-list>
<owner id="process16409057468" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process163feab3088" mode="U" requestType="wait"/>
</waiter-list>
</ridlock>
</resource-list>
</deadlock>
</deadlock-list>
Can some one explain how and what could have cause the dead lock?
I am having some hard time understanding this X and U lock flow at this moment.
Can you explain the flow what could have occurred X and U to stuck between dead lock?
If single query transactions are accumulating page level locks one at a time, deadlocks can still occur. At least two transactions must be involved and one transaction must be modifying rows.
Useful ways to avoid and minimize SQL Server deadlocksTry to keep transactions short; this will avoid holding locks in a transaction for a long period of time. Access objects in a similar logical manner in multiple transactions. Create a covering index to reduce the possibility of a deadlock.
Like blocking, a deadlock involves two processes that need specific resources to complete. However, unlike blocking, the two processes are not trying to get the same resource. A deadlock occurs when Process 1 is locking Resource A and Process 2 is locking Resource B.
The process of creating a deadlock is simple. First execute the first update statement from the first transaction and then execute the first update statement from the second transaction. This will create locks on table1 and table2. Now execute the second update statement from transaction1.
You have not provided sufficient details of your queries, but the deadlock graph you have shared clearly shows that it’s a “writer-writer” deadlock due to parallelism, as all the locks granted or requested are either X or U.
<resource-list>
<ridlock fileid="1" pageid="2392" dbid="6" objectname="xx" id="lock164096b7800" mode="X" associatedObjectId="72057594051493888">
<owner-list>
<owner id="process163feab3088" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process16409057468" mode="U" requestType="wait"/>
</waiter-list>
</ridlock>
<ridlock fileid="1" pageid="2392" dbid="6" objectname="mytable" id="lock163f7fb2c80" mode="X" associatedObjectId="72057594051493888">
<owner-list>
<owner id="process16409057468" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process163feab3088" mode="U" requestType="wait"/>
</waiter-list>
</ridlock>
</resource-list>
One important thing about the writer-writer deadlocks is that SQL Server holds exclusive locks until the transaction commits, unlike shared locks which in the default read committed
isolation level.
Without query details it is not going to essay to figure out the exact cause of the error. Normally you need to re-factor your queries to avoid the deadlocks like
SELECT
queries outside the transactions, so that it only returns the
committed data, rather than returning data containing modifications
that might roll back.MAXDOP
hint to the query to force it run serially will remove
any change of an intra-query parallelism deadlock.Other common reason of deadlock is when you read data with the intention to update or delete it later by just putting a shared lock, the following UPDATE
statement can’t acquire the necessary Update Locks, because the resource is already blocked by another process causing the deadlock.
To resolve this you can select the records using WITH (SERIALIZABLE)
like following
UPDATE mytable WITH (SERIALIZABLE)
SET UserID = @ToUserID
WHERE UserID = @UserID
This will take the necessary Update lock on the record and will stop other process to acquire any lock (shared/exclusive) on the record and will prevent from any deadlocks.
You also need to look for ordering of your queries, a wrong order can lead to a Cycle Deadlock. In this scenario a query waits for another one in different transactions to complete.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With