StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>SQL Server locking is based on the concepts in <a href="http://rads.stackoverflow.com/amzn/click/1558601902" rel="noreferrer">Transaction Processing: Concepts and Techniques</a>. This book explains in great detail how locks are to be acquired, what locks are needed and <em>why</em> things must be the way they are.</p> <p>The resources Marc linked are good coverage on the topic, but the details are scattered and you need to know where to look. Here is a primer to start you up:</p> <p>The transaction isolation levels only affect read locks. Under normal <code>read committed</code> when reading a row an S-lock is acquired that is released immediately after the read. If the isolation level is elevated to <code>repeatable read</code> then the S-locks are held until the transaction ends. On higher <code>serializable</code> level <a href="http://msdn.microsoft.com/en-us/library/ms191272.aspx" rel="noreferrer">range locks</a> are placed instead of simple row locks, and they are held until the transaction commits. The snapshot modes are different in that they don't necessarily affect the type of lock, but the source of the read: rows are retrieved from the version store instead.</p> <p>Lock order/hierarchy is always the same: </p> <ul> <li>an Sch-S lock is placed on the metadata at the start of any DML operation. DDL operations require Sch-M locks and thus conflict, so DML can be assured of the 'stability' of the schema on which it operates (object schema, not database schema...). </li> <li>The lock hierarchy path to a row is table-page-row. The actual granularity decided by the engine is <a href="http://msdn.microsoft.com/en-us/library/ms189286.aspx" rel="noreferrer">dynamic</a>. <em>Typically</em> is row.</li> <li>No matter the granularity, the path to the actual locked resource is protected with <a href="http://msdn.microsoft.com/en-us/library/ms175519.aspx" rel="noreferrer">intent locks</a>. Ie. to S-lock a row, the reader must acquire IS-lock on the table and the page. To S-lock a page, it needs an IS-lock on table.</li> <li>Single partition operations acquiring more that 5000 locks on a scan may trigger <a href="http://msdn.microsoft.com/en-us/library/ms184286.aspx" rel="noreferrer">lock escalation</a>. Escalation is always an <em>attempt</em> (ie. will never block if failed). Escalation in practice goes always from row-level locking to table (partition in 2008) level locking.</li> </ul> <p>The lock hints can never change the <em>order</em> of locks, they can only change:</p> <ul> <li>the type of lock (U-lock or X-lock when an S-lock would be required)</li> <li>the granularity (enforce table, or page or row)</li> <li>the duration (hold S-locks)</li> <li>the blocking behavior (readpast to skip incompatible rows). </li> </ul> <p>I did not talk too much about insert/update/deletes since they are quite uninteresting: they require X locks, that's it. The only interesting thing about it is the way update works because it first acquire an U-lock that is later converted to an X-lock. This behavior is needed to leverage the <a href="http://rusanu.com/2008/04/09/chained-updates/" rel="noreferrer">U-lock asymmetry</a> that allows pending S-locks to drain before the update proceeds.</p> <p>With this I hope you can go and find all the details left out from the articles and books linked.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload