SQL Server handles all
locking decisions. It chooses which type of lock to use after it determines the
query plan. However, the way you write a query or transaction can affect the
type of lock the server chooses. You can also force the server to make certain
locks more or less restrictive by including the holdlock, noholdlock, or shared keywords with your
queries or by changing the transaction's isolation level. These options are
described later in this chapter.
SQL Server has two
levels of locking: page locks and table locks. Page locks are generally less
restrictive (or smaller) than table locks. A page lock locks all of the rows on
the page; table locks lock entire tables. SQL Server attempts to use page locks
as frequently as possible to reduce the contention for data among users and to
improve concurrency.
SQL Server uses table
locks to provide more efficient locking when it determines that an entire
table, or most of a table's pages, will be accessed by a statement. Locking
strategy is directly tied to the query plan, so the query plan can be as
important for its locking strategies as for its I/O implications. If an update or delete statement has no useful index, it does a table
scan and acquires a table lock. For example, the following statement generates
a table lock:
update account set balance = balance * 1.05
If the update or delete statement uses an index, it begins by acquiring
page locks, and only attempts to acquire a table lock if a large number of rows
are affected.
Whenever possible, SQL
Server tries to satisfy requests with page locks. However, once a statement
accumulates more page locks than the lock promotion threshold allows, SQL Server tries to issue a table lock on that object. If
it succeeds, the page locks are no longer necessary and are released.
Table locks also provide
a way to avoid lock collisions at the page level. SQL Server automatically uses
table locks for some commands.
Page Locks
The following describes
the different types of page locks:
Shared locks
SQL
Server applies shared locks for read operations. If a shared lock has been
applied to a data page, other transactions can also acquire a shared lock even
when the first transaction is not finished. However, no transaction can acquire
an exclusive lock on the page until all shared locks on it are released. That
is, many transactions can simultaneously read the page, but no one can write to
it while the shared lock exists.
By
default, SQL Server releases shared page locks after the scan is complete on
the page. It does not hold them until the statement completes or until the end
of its transaction. Transactions that need an exclusive page lock wait or
"block" for the release of the shared page locks before continuing.
Exclusive locks
SQL
Server applies exclusive locks for data modification operations. When a
transaction gets an exclusive lock, other transactions cannot acquire a lock of
any kind on the page until the exclusive lock is released at the end of its
transaction. Those other transactions wait or "block" until the
exclusive lock is released, before continuing.
Update locks
SQL
Server applies update locks during the initial portion of an update, delete, or fetch (for cursors declared for update) operation when the pages are being read. The update locks allow
shared locks on the page, but do not allow other update or exclusive locks.
This is an internal lock to help avoid deadlocks. Later, if the pages need to
be changed and no other shared locks exist on the pages, the update locks are
promoted to exclusive locks.
In general, read
operations acquire shared locks, and write operations acquire exclusive locks.
However, SQL Server can apply page-level exclusive and update locks only if the
column used in the search argument is part of an index.
The following examples
show what kind of page locks SQL Server uses for the respective statement
(assuming that indexes are used on the search arguments):
select balance from account
Shared page lock
where acct_number = 25
insert account values(34, 500)
Exclusive page lock
delete account
Update page locks
where balance < 0
Exclusive page locks
update account set balance = 0
Update page lock
where acct_number = 25
Exclusive page lock
Table Locks
The following describes
the types of table locks.
Intent lock
An intent lock indicates that certain types of page-level locks
are currently held in a table. SQL Server applies an intent table lock with
each shared or exclusive page lock, so intent locks can be either intent
exclusive locks or intent shared locks. Setting an intent lock prevents other
transactions from subsequently acquiring a shared or exclusive lock on the
table that contains that locked page. Intent locks are held as long as the
concurrent page locks are in effect.
Shared lock
This
lock is similar to the shared page lock, except that it affects the entire
table. For example, SQL Server applies shared table locks with a select with holdlock that does not use an index, and for thecreate
nonclustered index statement.
Exclusive lock
This
lock is similar to the exclusive page lock, except that it affects the entire
table. For example, SQL Server applies exclusive table locks during the create clustered index command. update anddelete statements generate exclusive table locks if
their search arguments do not reference indexed columns of the object.
The following examples
show the respective page and table locks issued for each statement (assuming
that indexes are used in their search arguments):
select balance from account
Intent shared table lock
where acct_number = 25
Shared page lock
insert account values(34, 500)
Intent exclusive table lock
Exclusive page lock
delete account
Intent exclusive table lock
where balance < 0
Update page locks
Exclusive page
locks
This next example
assumes that there is no index on acct_number;
otherwise, SQL Server would attempt to issue page locks for the statement:
update account set balance = 0
Exclusive table lock
where acct_number = 25
Demand Locks
SQL Server sets a demand lock to indicate that a transaction is next in line
to lock a table or page. Demand locks prevent any more shared locks from being
set. This avoids situations in which read transactions acquire overlapping
shared locks, which monopolize a table or page so that a write transaction
waits indefinitely for its exclusive lock.
After waiting on several
different read transactions, SQL Server gives a demand lock to the write
transaction. As soon as the existing read transactions finish, the write
transaction is allowed to proceed. Any new read transactions must then wait for
the write transaction to finish, when its exclusive lock is released.
Demand locks are
internal processes, and are not visible when using sp_lock.
No comments:
Post a Comment