Here are some notes on "SQL Server 2008 Locking" I took while attending an advanced class on SQL Server taught by Kimberly Tripp  (http://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

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>

Other ways to separate readers/writers

  • Backup/Restore, SQL IS, Replication, SQL AS, Snapshots
  • For real-time, mixed, live workloads - only with snapshot isolation

Locks and issues

  • Level 0 - Locks: No row locks, locks not honored
  • Level 0 - Issue: Dirty reads
  • Level 1 - Locks: Reader locks released ASAP. SELECT (*) locks per row.
  • Level 1 - Issue: Inconsistent Analysis, Non-repeatable reads (data can change during transaction)
  • Level 2 - Locks: Reader locks are left behind. SELECT (*) locks all.
  • Level 2 - Issue: Phantoms (new rows can show up)
  • Level 3 - Locks: Locks on ranges (table locks if no index)

READ_COMMITED_SNAPSHOT

  • Starting in SQL Server 2005, database option for READ_COMMITED_SNAPSHOT
  • Level 1, uses versioning instead of locking
  • Can be overridden with READCOMMITEDLOCK hint

Isolation with table hints

  • Level 0 - READUNCOMMITTED, NOLOCK
  • Level 1 - READCOMMITTED
  • Level 2 - REPEATABLEREAD, READCOMMITTEDLOCK (with RCS option on)
  • Level 3 - SERIALIZABLE, HOLDLOCK
  • SELECT...FROM table1 WITH (READUNCOMMITED) JOIN table2 WITH (SERIALIZABLE)

Blocking

  • Locking is good. Guarantees consistency. Prevents conflicts.
  • Blocking is not good. It happens if there's a transaction taking too long.
  • Find the culprit, fix the issue

Tips to minimize blocking

  • Keep transactions short
  • Keep transactions in one batch, no interactions
  • Use indexes to lock only the necessary data
  • Consider estimates instead of long running queries
  • Consider separate analysis services for long running queries
  • Look closely at long running or conflicting transactions

Detecting Blocking

  • Look at sp_blocker_pss80 from http://support.microsoft.com/?id=271509
  • Tells who's in the head of the chain
  • Deadlock events and trace flag 1222
  • Performance data collection and extended events
  • Use sp_who3 (not in the product, search the web)
  • SQL Profiler, Performance Monitor, Extended Events

Deadlocks

  • Two or more transactions request mutually desired resources in wrong order
  • It's some sort of circular references, many specific cases.
  • Classic case: spose moving savings to checking, other spouse moving checking to saving
  • Happens only if you lock the same rows in the reverse sequence, at the same time
  • SQL will identify those and kill the "least expensive" transaction
  • SQL Profiler events, good for quick visual of what was happening
  • You can save the XML format with XDL extension, sent to the developer
  • Extended events, Trace flags flags to send to SQL log

Deadlock resolution

  • You can use SET DEADLOCK_PRIORITY LOW (choose me, choose me)
  • Since SQL Server 2005, you can also do SET DEADLOCK_PRIORITY HIGH (don't choose me)
  • Can use a RANGE from -10 to 10 instead of LOW (-5), MEDIUM (0), HIGH (5)
  • Deadlock victim receives error 1205
  • Books online: http://msdn.microsoft.com/en-us/library/ms186736.aspx
  • Certain distributed or application deadlock might be impossible to detect by SQL

Deadlock avoidance

  • Access resources in the same order
  • Minimize blocking
  • Change isolation level

Snapshot Isolation

  • Introduced in SQL Server 2005
  • There is a cost to implementing this (versioning)
  • Much better option then leaving transactions waiting around

Extra space needed for versioning

  • Older version of the row stored in tempdb as rows are updated
  • 14-byte pointer (LSN) to old version added to row in the data page
  • Carefull with splitting if you turn this on and data pages are full
  • Pointer in the row in the data page does not go away

Statement-level read consistency

  • Read committed isolation using row versioning (aka RCSI)
  • Database option: ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON
  • Consider using the "WITH ROLLBACK AFTER 5" option
  • You can see the database schema modification log
  • This turns it on and uses it for all statements
  • See http://msdn.microsoft.com/en-us/library/ms345124.aspx

Transaction-level read consistency

  • Snapshot isolation
  • Database option: ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON
  • Makes it available, but only uses it if requested in transaction
  • Session setting: SET TRANSACTION ISOLATION LEVEL SNAPSHOT
  • This is per user (session) and tracks it per transaction
  • This potentially holds the versions in tempdb for longer
  • Different transactions updating same record detected
  • See http://msdn.microsoft.com/en-us/library/ms179599.aspx
  • Four choices: None (default), Statement, Transaction, Both