4

i have a table A with a clustered index on the primary key and a non-clustered index on a second column (act_id) (foreign Key to table B)

Two update statements are generating following deadlock: Deadlock 1

This deadlock seems not to be an bookmark lookup deadlock, because it's on the same index and the same objid. Including the pk in the non-clustered index has not brought any success. I did understand, that the objid is not the recordid, but a range in the index.

The queries (e.g. update a set act_id = 1 where act_id = 2), leading to this deadlock, have the following execution plan: Execution plan

Sometimes the same deadlock is on another table with only one clustered index: Same deadlock on another table

Has anybody an idea how this deadlock can be avoided?

Dominik
  • 1,016
  • 11
  • 30
Littlefield
  • 51
  • 6
  • Are there multiple statements in your transactions? Can you post the SQL for the whole transaction? – usr Sep 19 '18 at 09:56

2 Answers2

2

Both queries seem to be updating the same part of the B-Tree of your Clustered Index. There's multiple answers to this concurrency issue. One would be to get faster hardware. Another one would be to not have a primary key that can change.

I would first ask you: are you updating your primary key from 2 separate queries?
Do you have a natural key?
Do you have cascading updates set on your primary/foreign keys?
What's your other blocking query?

Including the pk in the non-clustered index has not brought any success

That's because the pk is always in all the non-clustered indexes, how else would it do a bookmark lookup?

DrTrunks Bell
  • 46
  • 3
  • He's changing a non-PK column. Normally, U-locks prevent many kinds of deadlocks for simple single-row updates. For example, concurrent updates by PK on the same row never deadlock. I'm not sure about updates through non-PK columns in this regard. – usr Sep 19 '18 at 10:19
  • Both queries are of the same kind (update A set act_id where act_id = XX), it's possible that there are more than one row per act_id – Littlefield Sep 19 '18 at 12:19
1

Thank you for your answers. I drawed a picture to make it more clear: enter image description here

We have tasks, which have one action. Each action can have multiple parameters. Our application consists of several deployments. Each deployment can upload these tasks. In the example i have three deployments. 2 addons and one base-module. The 2 addons are waiting until the base-module is ready, then both modules are calling .upload() in the base-module in own transactions. At one point the tasks will be updated (if there were changes). So in both addons were changes in tasks, this generates the deadlock.

Littlefield
  • 51
  • 6