Database Reference
In-Depth Information
•
Schema:
•
Schema Modification (Sch-M)
•
Schema Stability (Sch-S)
•
Bulk Update (BU)
•
Key-Range
Shared (S) Mode
Shared mode is used for read-only queries, such as a
SELECT
statement. It doesn't prevent other read-only queries
from accessing the data simultaneously because the integrity of the data isn't compromised by the concurrent reads.
However, concurrent data modification queries on the data are prevented to maintain data integrity. The (S) lock is
held on the data until the data is read. By default, the (S) lock acquired by a
SELECT
statement is released immediately
after the data is read. For example, consider the following transaction:
BEGIN TRAN
SELECT *
FROM Production.Product AS p
WHERE p.ProductID = 1;
--Other queries
COMMIT
The (S) lock acquired by the
SELECT
statement is not held until the end of the transaction; instead, it is released
immediately after the data is read by the
SELECT
statement under
read_ committed
, the default isolation level. This
behavior of the (S) lock can be altered by using a higher isolation level or a lock hint.
Update (U) Mode
Update mode may be considered similar to the (S) lock, but it also includes an objective to modify the data as part of
the same query. Unlike the (S) lock, the (U) lock indicates that the data is read for modification. Since the data is read
with an objective to modify it, SQL Server does not allow more than one (U) lock on the data simultaneously. This rule
helps maintain data integrity. Note that concurrent (S) locks on the data are allowed. The (U) lock is associated with
an
UPDATE
statement, and the action of an
UPDATE
statement actually involves two intermediate steps.
1.
Read the data to be modified.
2.
Modify the data.
Different lock modes are used in the two intermediate steps to maximize concurrency. Instead of acquiring an
exclusive right while reading the data, the first step acquires a (U) lock on the data. In the second step, the (U) lock is
converted to an exclusive lock for modification. If no modification is required, then the (U) lock is released; in other
words, it's not held until the end of the transaction. Consider the following example, which demonstrates the locking
behavior of the
UPDATE
statement:
BEGIN TRANSACTION LockTran1
UPDATE Sales.Currency
SET Name = 'Euro'
WHERE CurrencyCode = 'EUR';
COMMIT
