Sql server® 2012 t-sql fundamentals

Lock Modes and Compatibility

As you start learning about transactions and concurrency, you should first familiarize yourself with 

two main lock modes—exclusive and shared. 

When you try to modify data, your transaction requests an exclusive lock on the data resource, 

regardless of your isolation level (you’ll learn more about isolation levels later in this chapter). If 

granted, the exclusive lock is held until the end of the transaction. For single-statement transactions, 

this means that the lock is held until the statement completes. For multistatement transactions, this 

means that the lock is held until all statements complete and the transaction is ended by a COMMIT 

TRAN or ROLLBACK TRAN command. 

Exclusive locks are called “exclusive” because you cannot obtain an exclusive lock on a resource if 

another transaction is holding any lock mode on the resource, and no lock mode can be obtained on 

a resource if another transaction is holding an exclusive lock on the resource. This is the way modi-

fications behave by default, and this default behavior cannot be changed—not in terms of the lock 

mode required to modify a data resource (exclusive) and not in terms of the duration of the lock (until 

the end of the transaction). In practical terms, this means that if one transaction modifies rows, until 

the transaction is completed, another transaction cannot modify the same rows. However, whether 

another transaction can read the same rows or not depends on its isolation level.

www.it-ebooks.info

---