The question:
I am trying to make sense of a deadlock. The table in question mytb is only used in the following query:
delete mytb with (uplock,holdlock) where [email protected]
called through sp_executesql. Both processes mentioned afterwards use the same code, apart from the key value.
I’m using trace flag 1222, here is the resource-list part from the server log:
resource-list
objectlock lockPartition=0 objid=1433553235 subresource=FULL dbid=16 objectname=mydb.dbo.mytb id=lock5620a3480 mode=IX associatedObjectId=1433553235
owner-list
owner id=process4bd94c8 mode=IX
waiter-list
waiter id=process4c85288 mode=X requestType=convert
objectlock lockPartition=0 objid=1433553235 subresource=FULL dbid=16 objectname=mydb.dbo.mytb id=lock5620a3480 mode=IX associatedObjectId=1433553235
owner-list
owner id=process4c85288 mode=IX
waiter-list
waiter id=process4bd94c8 mode=X requestType=convert
My understanding is the following: Both processes (let’s call them “c8” and “88” from their two last id characters) managed to get the same IX lock on the table, which then try to turn into an X lock to delete the neccessary row, but one process blocks the other.
Is my understanding correct? If yes, why are the processes sharing the IX lock? Shouldn’t the system deny access to a IX lock to the process that race-conditions it slower, which would cause the first process to complete first, then the second could begin?
Post comment addition: While digging through more, I discovered that an index on the where condition does not exists, whereas I expected it to be the clustered primary key. Could the absence of this index be the cause of the deadlock?
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
Could the absence of this index be the cause of the deadlock?
Yes. Each query will start with an IX lock, and then start acquiring U locks on key ranges. It will use range locking because of the holdlock hint, otherwise it would be regular U locks on the individual keys.
but after scanning several thousand rows with (updlock,holdlock) each will attempt to escalate the lock to a table-level X lock (object locks are partitioned)
Neither session can get an X lock on the table, since the other has an incompatible IX lock, so deadlock.
With a selective index, each session would only need to read and lock a handful of key values, and so wouldn’t escalate to a table lock.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0