Sql server® 2012 t-sql fundamentals

TABLE 9-2

  Lock Compatibility Including Intent Locks

Requested Mode 

Granted 

Exclusive (X)

Granted Shared 

(S)

Granted Intent 

Exclusive (IX)

Granted Intent 

Shared (IS)

Grant request for exclusive?

No

No

No

No

Grant request for shared?

No

Yes

No

Yes

Grant request for intent exclusive?

No

No

Yes

Yes

Grant request for intent shared?

No

Yes

Yes

Yes

SQL Server determines dynamically which resource types to lock. Naturally, for ideal concurrency, 

it is best to lock only what needs to be locked, namely only the affected rows. However, locks require 

memory resources and internal management overhead. So SQL Server considers both concurrency 

and system resources when it is choosing which resource types to lock.

SQL Server might first acquire fine-grained locks (such as row or page locks), and in certain circum-

stances, try to escalate the fine-grained locks to more coarse-grained locks (such as table locks). For 

example, lock escalation is triggered when a single statement acquires at least 5,000 locks, and then 

for every 1,250 new locks, if previous attempts at lock escalation were unsuccessful. 

In SQL Server 2008 and SQL Server 2012, you can set a table option called LOCK_ESCALATION by 

using the ALTER TABLE statement to control the way lock escalation behaves. You can disable lock es-

calation if you like, or determine whether escalation takes place at a table level (default) or a partition 

level. (A table can be physically organized into multiple smaller units called partitions.)

www.it-ebooks.info

---