Contents
Is there a deadlock on the primary key?
According to the deadlock graph, both transactions have U locks on the primary key at the same time. One of them tries to increase it to an X lock and the deadlock occurs. “To avoid this potential deadlock problem, update (U) locks are used.
How does a concurrent update cause a deadlock?
The reading side of the plan accesses the nonclustered index first, then the base table. The update side of the plan accesses the base table first, then the nonclustered index. By touching the same resources in reverse order, multiple concurrent executions of the same plan can deadlock.
How to avoid a deadlock in SQL Server?
You can avoid this specific deadlock by ensuring page-level shared locks are not taken on the same access method after the UPDLOCK statement (row-level shared locks would be ok). This will avoid the SIU page lock. The page granularity explains why operations on two different id values can deadlock.
Why does SQL Server have a Siu lock?
The SIU lock occurs because somewhere between selecting the row in question with the UPDLOCK hint (which takes IX at the object level, IU at the page level, and U at the row level), a statement in the transaction is acquiring a shared ( S) page-level lock on the nonclustered primary key index structure.
How to prevent deadlock on SELECT queries?
Few sessions inserting many rows to the DB. and one session selects from this table. Sometimes I’m getting a deadlock on the SELECT query and I don’t understand why. The lock on the insertions meant to keep the sequence of session insertions in the DB.
How to avoid a deadlock during an update?
“To avoid this potential deadlock problem, update (U) locks are used. Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock.”
Why is there no primary key index in second process?
Sometimes second process has X and tries to get X (or I am reading the graph incorrectly). Primary key index fields are never updated and there is no other index on table. The way business process goes, I can be certain that both select and update are working with records with big overlap.