Microsoft® SQL Server® 2012 Bible

1067

Chapter 47: Managing Transactions, Locking, and Blocking

47

For best performance, the SQL Server lock manager tries to balance the size of the lock

against the number of locks. The struggle is between concurrency (smaller locks allow more

transactions to access the data) and resource usage. (Fewer locks are faster because each

lock requires memory in the system to hold the information about the lock.)

SQL Server automatically manages the granularity of locks by trying to keep the lock size

small and escalating to a higher level only when it detects memory pressure.

Lock Mode

Locks not only have granularity, but also a mode that determines their purpose. SQL Server

has a rich set of lock modes (such as shared, update, and exclusive). Understanding lock

modes can dramatically aid in not only developing well-designed database applications, but

can also assist you in troubleshooting locking problems.

Lock Contention

The interaction and compatibility of the locks plays a vital role in SQL Server’s transactional

integrity and performance. Certain lock modes block other lock modes, as detailed in

Table 47-2. For example, if Transaction 1 has a shared lock (S), and transaction 2 requests an

exclusive lock (X), then the request is denied because a shared lock blocks an exclusive lock.

TABLE 47-2 Lock Compatibility

T2 Requests

T1 Has IS S U IX SIX X

Intent shared (IS) Yes Yes Yes Yes Yes Yes

Shared (S) Yes Yes Yes No No No

Update (U) YesYesNoNoNoNo

Intent exclusive (IX) YesNoNoYesNoNo

Shared with intent exclusive (SIX) Yes No No No No No

Exclusive (X) NoNoNoNoNoNo

Exclusive locks are ignored unless the page in memory has been updated, that is, is dirty.

Shared Lock (S)

Shared locks are taken anytime data is in the database using the default isolation level,

Read Committed, or with any pessimistic isolation level of lower concurrency. Assuming

the default isolation level, two processes are allowed to read the same data by use of shared

locks. However, because shared locks are not granted on resources that are intended to be,

or currently are, locked, this is why you often see a read operation being blocked: Shared

locks can never be taken on rows/pages/tables that are currently being modifi ed.

c47.indd 1067c47.indd 1067 7/31/2012 10:23:30 AM7/31/2012 10:23:30 AM

http://www.it-ebooks.info