You need to drastically reduce the amount of time you spend inside those transactions (or get rid of them altogether), collapse multiple queries for different parameter values into a single, point-in-time query, build better indexes so that escalation doesn't happen, stop forcing repeatable read, created a clustered index (if I'm reading the resource descriptor right, that's a heap!?), or all of the above.Deadlocks are rough to work with. If you replay the deadlock (I wrote about this functionality here, back when I was a Technical PM), you will see all of these different sessions holding their transactions open for relatively long periods of time (5+ minutes, which is crazy, though the animation really hides that), and issuing multiple queries intermittently. Using Plan Explorer to open the deadlock file, if you expand the waiter/owner list, you see they are all trying to access the same resource (presumably the entire table):Īlso a slightly less scary way to see this is to optimize layout and use force directed: This definitely seems to be a bad combination of isolation level, lock escalation, and many sessions each issuing multiple queries inside the same transaction. Next to checking up on the isolation level, you should look into speeding up the queries inside the transactions by adding indexes. You should contact the application team / vendor and see if this a necessary evil, because blocking will be more prevalent. As a result of being in the repeatable read isolation level they do not want to give up their locks, resulting in deadlocks. because of a previous select) into an Exclusive lock (Update statement). Meaning that a reasonable explanation is that all these transactions are trying to convert their own shared lock (e.g. Pending IX locks could have been acquired on an earlier query in the In Repeatable Read the object-level S locks that conflict with the Whereas the requestType="convert" explains that the processes are trying to convert the shared lock into an IX lock. Where the several processes have Shared locks on this entire object: Īnd these same processes have several intent exclusive locks on these objects The blocked queries where waiting on the resource: waitresource="OBJECT: 11:1142295129:0 " Transactions blocking eachother, with lock escalation under the repeatable read isolation level can cause blocking and as a byproduct of blocking, deadlocks. However, it does not prevent other transactionsįrom inserting new rows into the tables which have been selected inĪ part of the explanation on repeatable read here Transactions are unable to modify the data that has been read by theĬurrent transaction. Locks are held until the transaction completes. It is achieved by placing shared locks onĪll data that is read by each statement in a transaction and all the The Repeatable Read SQL Server isolation level prevents dirty readsĪnd not-repeatable reads. This is done at the connection level with this command: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ Your default isolation level might be Read Committed Snapshot,īut the isolation level set by your application = repeatable read (3)įor all the update statements in the deadlock, these are the isolationlevels: isolationlevel="repeatable read (3)" 0 (X64) 01:37:51 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor) When I opened this deadlock graph in Sentry One plan Explorer, it was scary. Isolation level is set as Read Committed Snapshot at database level. Unknown bigint)update Table_Name set STATUS_ID= where id= Deadlocks occur mostly involving two or three processes however I noticed yesterday, it was having 9 SPIDs involved.Ĭan somebody please help me in understanding this deadlock graph and solution on how to avoid this. I am supporting vendor based application, which is filled with blocks and deadlocks.
0 Comments
Leave a Reply. |