Deadlock on common IX lock



  • 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 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 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?



  • 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.

    enter image description here

    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)

    enter image description here

    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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2