O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

referenceaeroThe 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required