10

I have two stored procedures that are deadlocking when the system is under load. Proc A is selecting from a table while the Proc B is inserting into the same table. The Lock Graph is showing that Proc A has an S mode page lock that Proc B wants an IX mode lock for, Proc A however is waiting for an S mode page lock for a different page that Proc B already has a IX mode page lock on.

Clearly this could be sorted out by ensuring that both the queries lock pages in the table in the same order, but I can't figure out how to do that.

My question is: How does SQL Server determine what order to lock pages in while doing INSERTs and SELECTs and how can you modify this behaviour?

migrated from stackoverflow.com Dec 13 '11 at 15:35

This question came from our site for professional and enthusiast programmers.

2

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.

1

Currently, proc A has a shared lock.

"No other transactions can modify the data while shared (S) locks exist on the resource." http://msdn.microsoft.com/en-us/library/aa213039%28v=sql.80%29.aspx

If you are on sql 2005 or higher, try using snapshot isolation.

"The snapshot isolation level uses row versioning to provide transaction-level read consistency. Read operations acquire no page or row locks; only SCH-S table locks are acquired. When reading rows modified by another transaction, they retrieve the version of the row that existed when the transaction started. You can only use Snapshot isolation against a database when the ALLOW_SNAPSHOT_ISOLATION database option is set ON. By default, this option is set OFF for user databases." http://msdn.microsoft.com/en-us/library/ms189122.aspx

Since only Sch-S locks are acquired, your' read should not be able to block your' write.

"Schema stability (Sch-S) locks do not block any transactional locks, including exclusive (X) locks." http://msdn.microsoft.com/en-us/library/ms189122.aspx

Keep in mind, Snapshot Isolation Level makes heavy use of tempdb for row versioning, so size it appropriately and stick to best practices for tempdb disk strategies.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.