Datasheet

SQL Server utilizes six lock types, or more accurately, six resource lock modes:
❑ Shared
❑ Update
❑ Exclusive
❑ Intent
❑ Schema
❑ Bulk Update
Shared, Update, Exclusive, and Intent locks can be applied to rows of tables or indexes, pages (8-kilobyte
storage page of an index or table), extents (64-kilobyte collection of 8contiguous index or table pages),
tables, or databases. Schema and Bulk Update locks apply to tables.
Shared Locks
Shared locks allow multiple connections and transactions to read the resources they are assigned to. No
other connection or transaction is allowed to modify the data as long as the Shared lock is granted. Once
an application successfully reads the data the Shared locks are typically released, but this behavior can
be modified for special circumstances. Shared locks are compatible with other Shared locks so that many
transactions and connections can read the same data without conflict.
Update Locks
Update locks are used by SQL Server to help prevent an event known as a deadlock. Deadlocks are bad.
They are mostly caused by poor programming techniques. A deadlock occurs when two processes get
into a stand-off over shared resources. Let’s return to the banking example: In this hypothetical banking
transaction both my wife and I go online to transfer funds from our savings account to our checking
account. We somehow manage to execute the transfer operation simultaneously and two separate pro-
cesses are launched to execute the transfer. When my process accesses the two accounts it is issued
Shared locks on the resources. When my wife’s process accesses the accounts, it is also granted a Shared
lock to the resources. So far, so good, but when our processes try to modify the resources pandemonium
ensues. First my wife’s process attempts to escalate its lock to Exclusive to make the modifications. At
about the same time my process attempts the same escalation. However, our mutual Shared locks pre-
vent either of our processes from escalating to an Exclusive lock. Because neither process is willing to
release its Shared lock, a deadlock occurs. SQL Server doesn’t particularly care for deadlocks. If one
occurs SQL Server will automatically select one of the processes as a victim and kill it. SQL Server selects
the process with the least cost associated with it, kills it, rolls back the associated transaction, and noti-
fies the responsible application of the termination by returning error number 1205. If properly captured,
this error informs the user that “Transaction ## was deadlocked on x resources with another process and
has been chosen as the deadlock victim. Rerun the transaction.” To avoid the deadlock from ever occur-
ring SQL Server will typically use Update locks in place of Shared locks. Only one process can obtain an
Update lock, preventing the opposing process from escalating its lock. The bottom line is that if a read is
executed for the sole purpose of an update, SQL Server may issue an Update lock instead of a Shared
lock to avoid a potential deadlock. This can all be avoided through careful planning and implementation
of SQL logic that prevents the deadlock from ever occurring.
17
Introducing Transact-SQL and Data Management Systems
04_57955x ch01.qxd 9/19/05 12:44 PM Page 17