1069
Chapter 47: Managing Transactions, Locking, and Blocking
47
transaction can grab a shared lock. The intent exclusive lock isn’t a full exclusive lock, but
it lays claim to gaining an exclusive lock in the future.Schema Lock (Sch-M, Sch-S)
Schema locks protect the database schema. SQL Server applies a schema stability (Sch-S)
lock during any query to prevent a data defi nition language (DDL) command from modify-
ing the structure that the statement is querying.A schema modifi cation lock (Sch-M) is applied only when SQL Server is adjusting the physi-
cal schema. If SQL Server is in the middle of adding a column to a table, the schema lock
prevents any other transactions from viewing or modifying the data during the schema-
modifi cation operation.Controlling Lock Timeouts
If a transaction is waiting for a lock, it continues to wait until the lock is available. By
default no timeout exists — it can theoretically wait forever.Fortunately, you can set the lock time using the set lock_timeout connection option.
Set the option to a number of milliseconds or set it to infi nity (the default) by setting it to
-1. Setting the lock_timeout to 0 means that the transaction instantly gives up if any
lock contention occurs. The application will be fast and ineffective.The following query sets the lock timeout to 2 seconds (2,000 milliseconds):SET Lock_Timeout 2000When a transaction does time out while waiting to gain a lock, a 1222 error is raised.Lock Duration
The third lock property, lock duration, is determined by the transaction isolation level of
the transactions involved — the more stringent the isolation, the longer the locks will be
held. SQL Server implements four transaction isolation levels. (Transaction isolation levels
are detailed in the next section.)Index-Level Locking Restrictions
Isolation levels and locking hints are applied from the connection and query perspective.
The only way to control locks from the table perspective is to restrict the granularity of
locks on a per-index basis. Using the alter index command, rowlocks and pagelocks may
be disabled for a particular index, as follows:ALTER INDEX AK_Department_Name
ON HumanResources.Department
SET (ALLOW_PAGE_LOCKS = OFF)c47.indd 1069c47.indd 1069 7/31/2012 10:23:30 AM7/31/2012 10:23:30 AM
http://www.it-ebooks.info