Microsoft SQL Server 2012 Bible

Default Locking and Blocking Behavior

SQL Server uses locks, an internal memory structure, to provide transactional integrity between transactions.

There are different types of locks; among these are Shared (reading), Update (getting ready to write), Exclusive (writing), and many more. Some of these locks work well together, that is, two transactions can have Shared locks on a resource. However, when an exclusive lock has been acquired on a resource, no other transaction can acquire locks on that same resource. The locks used by the transactions are blocking other transaction from gaining access to the resource.

The different types of locks and how compatible they are with each other is documented in BOL at: http://msdn.microsoft.com/en-us/library/ms186396(v=sql.105).aspx.

SQL Server's default transaction isolation is read committed, meaning that SQL Server ensures that only committed data is read. When a transaction updates a row, and the data is still yet uncommitted, SQL Server makes other transactions that want to read that data wait until the first transaction is committed.

To demonstrate SQL Server's default locking and blocking behavior, the following code walks through two transactions accessing the same row. Transaction 1 updates the row, whereas transaction 2 attempts to select the row. The best way to see these two transactions is with two query editor windows, as shown ...