Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 7 down vote favorite
1

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 a S mode page lock that the 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?

share|improve this question
2  
Are the two procedures in question simple enough to post a version of? There are several routes for handling this, more easily explained with an example to modify. – Mark Storey-Smith Dec 13 '11 at 16:39
Unfortunatly not. Protected by an NDA as well. – Martin Brown Dec 13 '11 at 22:55
1  
So you want us to fix your problem without showing us what's causing the problem? – Simon Righarts Dec 13 '11 at 23:25
1  
This will depend upon the access methods used. Please provide simplified / anonymised versions of what your procedures are doing. – Martin Smith Dec 14 '11 at 10:21
@Simon: No I don't want you to fix my problem. I want an answer to the question: "How does SQL Server determine what order to lock pages in while doing INSERTs and SELECTs?" The answer to this will help me fix the problem on my own. – Martin Brown Dec 20 '11 at 9:11
show 1 more comment

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

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
Shame that, I thought I might actually get to solve the issue rather than fudging a compromise. Still thanks for your help. I recon I can solve the issue using a READPAST hint on the select, but it is not an ideal solution. – Martin Brown Dec 13 '11 at 16:35
up vote 1 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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