SQL Server – Force Update Statements to Take Page Locks in the Same Order

deadlocklockingsql serversql server 2014

I have two update statements on the same table that take page locks in different orders. This leads to deadlocks.

Process A owns an update lock on page 1, and requests an update lock on
page 2.
Process B owns an update lock on page 2, and wants an update
lock on page 1.

How can I force both statements to take page locks in the same order?

Best Answer

How can I force both statements to take page locks in the same order?

You can't. Instead force them both to take a table lock with a TABLOCKX hint, or use an Application Lock to serialize the sessions.

Related Solutions

Sql-server – How does SQL Server determine the order it takes locks in while selecting a table

How does SQL Server determine what order to lock pages in while doing INSERTs and SELECTs a

Undetermined - done by internal processing and depending on query optimizer output.

and how can you modify this behaviour?

Control your isolation. If you read in order to write, tell SQL Server to immediately get a write lock. Point closed.

Sql-server – Alternatives to sp_indexoption for Row Locks and Page Locks in SQL Server 2008

If I understand you correctly, a better way is to use TABLOCK hint to force a table level lock in the query that needs it (example: delete from a heap)

UPDATE SomeTable WITH (TABLOCK)
SET ...


INSERT SomeTable WITH (TABLOCK)
...

Personally, I wouldn't set it globally in the CREATE INDEX statement...

Also, the MSDN page for sp_indexoptions states

This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, use ALTER INDEX (Transact-SQL).

Best Answer

Related Solutions

Sql-server – How does SQL Server determine the order it takes locks in while selecting a table

Sql-server – Alternatives to sp_indexoption for Row Locks and Page Locks in SQL Server 2008

Related Question