Database Reference
In-Depth Information
Figure 20-5. Output from sys.dm_tran_locks showing the intent locks granted at higher levels
The (IX) lock at the table level ( PAGE ) indicates that the DELETE statement intends to acquire an (X) lock at a page,
row, or key level. Similarly, the (IX) lock at the page level ( PAGE ) indicates that the query intends to acquire an (X) lock
on a row in the page. The (IX) locks at the higher levels prevent another transaction from acquiring an incompatible
lock on the table or on the page containing the row.
Flagging the intent lock—(IS) or (IX)—at a corresponding higher level by a transaction, while holding the lock
at a lower level, prevents other transactions from acquiring an incompatible lock at the higher level. If the intent
locks were not used, then a transaction trying to acquire a lock at a higher level would have to scan through the lower
levels to detect the presence of lower-level locks. While the intent lock at the higher levels indicates the presence of a
lower level lock, the locking overhead of acquiring a lock at a higher level is optimized. The intent locks granted to a
transaction are held until the end of the transaction.
Only a single (SIX) lock can be placed on a given resource at once. This prevents updates made by other
transactions. Other transactions can place (IS) locks on the lower-level resources while the (SIX) lock is in place.
Furthermore, there can be a combination of locks requested (or acquired) at a certain level and the intention of
having a lock (or locks) at a lower level. For example, there can be (SIU) and (UIX) lock combinations indicating that
an (S) or a (U) lock has been acquired at the corresponding level and that (U) or (X) lock(s) are intended at a
lower level.
Schema Modification (Sch-M) and Schema Stability (Sch-S) Modes
Schema Modification and Schema Stability locks are acquired on a table by SQL statements that depend on the
schema of the table. A DDL statement, working on the schema of a table, acquires an (Sch-M) lock on the table and
prevents other transactions from accessing the table. An (Sch-S) lock is acquired for database activities that depend
on the schema but do not modify the schema, such as a query compilation. It prevents an (Sch-M) lock on the table,
but it allows other locks to be granted on the table.
Since, on a production database, schema modifications are infrequent, (Sch-M) locks don't usually become a
blocking issue. And because (Sch-S) locks don't block other locks except (Sch-M) locks, concurrency is generally not
affected by (Sch-S) locks either.
Bulk Update (BU) Mode
The Bulk Update lock mode is unique to bulk load operations. These operations are the older-style bcp (bulk copy),
the BULK INSERT statement, and inserts from the OPENROWSET using the BULK option. As a mechanism for speeding
up these processes, you can provide a TABLOCK hint or set the option on the table for it to lock on bulk load. The key
to (BU) locking mode is that it will allow multiple bulk operations against the table being locked but prevent other
operations while the bulk process is running.
 
Search WWH ::




Custom Search