Deadlock on common IX lock
I am trying to make sense of a deadlock. The table in question
mytbis only used in the following query:
delete mytb with (uplock,holdlock) where key_value=@value
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
wherecondition 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?
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
holdlockhint, 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.