All we need is an easy explanation of the problem, so here it is.
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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂