SQL Server 2008 Locking

Article
03/19/2009

Here are some notes on "SQL Server 2008 Locking" I took while attending an advanced class on SQL Server taught by Kimberly Tripp (https://sqlskills.com/AboutKimberlyLTripp.asp).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without the right pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

You have more locks than you think

You have a shared database lock every time you connect
This helps someone from making the database read only while you're on
One update on one row will give you three additional locks
Table intent exclusive, page intent exclusive and row exclusive lock
That's already 4 locks
Use sp_lock @@spid to figure out what's going on

Row locks

Shared locks, Update locks, eXclusive locks
S on S = OK, S on U = OK, S on X = WAIT
U on S = OK, U on U = WAIT, U on X = WAIT
X on S = WAIT, X on U = WAIT, X on X = WAIT

Update locks

Updates locks are there to help with concurrency
U acquired in the beginning to isolate rows before change
U does not prevent readers from seeing the data
U updates to X on the row to do the update

Page and Table Locks

Shared, Intent Shared, Intent eXclusive, Shared Intent Exclusive, eXclusive
X on anyting - WAIT, IX on S - WAIT

Not necessarily accurate

You're running SELECT (*) on a table with a clustered index
The first row can be updated to be the last row
You can actually end up count that row twice
You're not reading anything that's not COMMITTED
Better concurrency at the trade off of accuracy

WAIT

If transactions are efficient and short, it's OK
If you end up in a WAIT, SQL will WAIT
SQL Server is incredibily patient :-)
This also causes your transaction log to grow...
You can write code to look at who is WAITing on what
You can write code to kill the transaction that's causing you to WAIT
However, it's best to figure out why that transaction is taking so long
It could be an application transaction with external dependencies

Workarounds

SET LOCK_TIMEOUT is an option at the session level, but be careful
Use DBCC USEROPTIONS to get your session setting
Use "SELECT * from sys.dm_exec_sessions" to get other session settings
SELECT ... WITH (READPAST) hint is an option, but it skips entire pages
With READPAST, make sure you don't care about missing a few rows

Granularity/Escalation

SQL tries to be as granular as possible
When there are too many locks, SQL locks higher in the hierarchy
Escalation: From Row (RID) to Page to Partition to Table (object) to Database
Lock escalation to a Partition lock is new in SQL Server 2008
Change with ALTER TABLE table SET (LOCK_ESCALATION={AUTO|TABLE|DISABLE})
Monitor with SELECT * FROM sys.dm_tran_locks

Hints to control locks

You can use ROWLOCK, PAGLOCK, TABLOCK, UPDLOCK, XLOCK
Careful. This can reduce perfromance or increase concurrency.
You can do "UPDATE table SET @@var=column=<exp>" to avoid a reselect
Look at ISOLATION LEVEL more than these types of locks
Books Online - https://msdn.microsoft.com/en-us/library/ms187373.aspx
Details at https://www.sqlmag.com/articles/index.cfm?articleid=6198&

Isolation Levels

This is the I in the ACID properties, controlled with locks
Level 0 - read uncommited
Level 1 - read commited - This is the default!
Level 2 - repeatable read
Level 3 - serializable
SET TRANSACTION ISOLATION LEVEL <string>

SQL Server 2008 Locking

Additional resources