Datasheet

18
Chapter 1: Introducing T-SQL and Data Management Systems
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 eight contiguous 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. For instance, a shared lock might be held for an entire transaction
to ensure maximum protection of data consistency by guaranteeing that the data that a transaction is
based on will not change until the transaction is completed. This extended locking is useful for situations
where transactional consistency must be 100% assured, but the cost of holding the locks is that
concurrent access to data is reduced. For example, you want to withdraw $100.00 from your savings
account. A shared lock is issued on the table that contains your savings account balance. That data is
used to confirm that there are enough funds to support the withdrawal. It would be advantageous to
prevent any other connection from altering the balance until after the withdrawal is complete. 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 standoff 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
processes 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
prevent 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 notifies the responsible application of the
termination by returning error number 1205. If properly captured, this error informs the user that
❑
❑
❑
❑
❑
❑
CH001.indd 18CH001.indd 18 3/26/10 11:35:41 AM3/26/10 11:35:41 AM