- SQL Server Locking Mechanism
- Transaction Isolation Levels
- Lock Types and Compatibility
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Lock Types and Compatibility
Depending on the activity performed by a transaction, SQL Server might acquire various types of locks. The way your application performs will depend on whether the various locks acquired by SQL Server are compatible with each other. Table 2 gives you a brief overview of each lock type, and shows you how to determine lock compatibility.
Table 2 Lock Types Supported by SQL Server 2000
|
Lock Type |
Description |
|
Intent |
The intent lock shows the future intention of SQL Server's lock manager to acquire locks on a specific unit of data for a particular transaction. SQL Server uses intent locks to queue exclusive locks, thereby ensuring that these locks will be placed on the data elements in the order the transactions were initiated. Intent locks come in three flavors: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX). IS locks indicate that the transaction will read some (but not all) the resources in the table or page by placing shared locks. IX locks indicate that the transaction will modify some (but not all) resources in the table or page by placing exclusive locks. SIX locks indicate that the transaction will read all resources, and modify some(but not all) of them. This will be accomplished by placing the shared locks on the resources read and exclusive locks on the rows modified. Only one SIX lock is allowed per resource at one time; therefore, SIX locks prevent other connections from modifying any data in the resource (page or table), although they do allow reading the data in the same resource. |
|
Shared |
Shared locks (S) allow transactions to read data with SELECT statements. Other connections are allowed to read the data at the same time; however, no transactions are allowed to modify data until the shared locks are released. |
|
Update |
Update locks (U) are acquired just prior to modifying the data. If a transaction modifies a row, then the update lock is escalated to an exclusive lock; otherwise, it is converted to a shared lock. Only one transaction can acquire update locks to a resource at one time. Using update locks prevents multiple connections from having a shared lock that want to eventually modify a resource using an exclusive lock. Shared locks are compatible with other shared locks, but are not compatible with Update locks. |
|
Exclusive |
Exclusive locks (X) completely lock the resource from any type of access including reads. They are issued when data is being modified through INSERT, UPDATE and DELETE statements. |
|
Schema |
Schema modification locks (Sch-M) are acquired when data definition language statements, such as CREATE TABLE, CREATE INDEX, ALTER TABLE, and so on are being executed. Schema stability locks (Sch-S) are acquired when store procedures are being compiled. |
|
Bulk Update |
Bulk update locks (BU) are used when performing a bulk-copy of data into a table with TABLOCK hint. These locks improve performance while bulk copying data into a table; however, they reduce concurrency by effectively disabling any other connections to read or modify data in the table. |
Table 3 shows the compatibility among different types of locks.
Table 3 Lock Compatibility in SQL Server 2000
|
Existing lock type (Across) Lock type requested (Down) |
IS |
S |
U |
IX |
SIX |
X |
BU |
Sch-M |
Sch-S |
|
IS |
Y |
Y |
Y |
Y |
Y |
N |
N |
Y |
N |
|
S |
Y |
Y |
Y |
N |
N |
N |
N |
Y |
N |
|
U |
Y |
Y |
N |
N |
N |
N |
N |
Y |
N |
|
IX |
Y |
N |
N |
Y |
N |
N |
N |
Y |
N |
|
SIX |
Y |
N |
N |
N |
N |
N |
N |
Y |
N |
|
X |
N |
N |
N |
N |
N |
N |
N |
Y |
N |
|
BU |
N |
N |
N |
N |
N |
N |
Y |
N |
N |
|
Sch-M |
Y |
Y |
Y |
Y |
Y |
Y |
N |
Y |
N |
|
Sch-S |
N |
N |
N |
N |
N |
N |
N |
N |
N |