Database Reference
In-Depth Information
Table 17-2.
Shared (S), update (U), and exclusive (X) locks compatibility matrix
(IS)
(S)
(IU)
(U)
(IX)
(X)
(IS)
Yes
Yes
Yes
Yes
Yes
No
(S)
Yes
Ye s
Yes
Ye s
No
No
(IU)
Yes
Yes
Yes
No
Yes
No
(U)
Yes
Ye s
No
No
No
No
(IX)
Yes
No
Yes
No
Yes
No
(X)
No
No
No
No
No
No
A few key points related to the shared (S) locks:
•
Shared (S) locks are compatible with each other, which means that multiple sessions can read
the same data simultaneously without blocking each other.
•
Shared (S) locks are compatible with update (U) locks, which means that update scans and
readers do not block each other.
•
Shared (S) locks are incompatible with exclusive (X) locks. This means that when readers need
to acquire shared (S) locks they would not be able to read uncommitted data modified by the
other sessions.
What controls locking behavior? This is when transaction isolation levels come in to the picture. Historically,
SQL Server uses four
pessimistic
transaction isolation levels when dealing with concurrency:
read uncommitted
;
read
committed
;
repeatable read,
and
serializable
. Let's start with them. There are also two optimistic transaction isolation
levels:
read committed snapshot
and
snapshot
which we will cover later in the topic.
■
sQL server always works with the data in transaction context. Client can control the scope of transaction by
explicitly specifying BeGIN TraN/CoMMIT statements. otherwise sQL server starts implicit transaction for the duration
of the statement. even seLeCT statements live within their own lightweight transactions. sQL server does not write to the
transaction log while processing seLeCT statements although all locking and concurrency rules still apply.
Note
As we already know, SQL Server holds exclusive (X) locks until the end of the transaction, regardless of
transaction isolation level used. With pessimistic isolation levels, update (U) locks behave in a similar manner. When
SQL Server searches for the data to update or delete, it issues update (U) locks. Again, regardless of transaction
isolation level used.
■
Note
even in a read uncommitted transaction, isolation level writers can block other writers.
The key difference between pessimistic transaction isolation levels is how SQL Server deals with the shared (S)
locks.
In read uncommitted transaction isolation level shared (S) locks are not acquired, which means that readers
would not be blocked while reading the rows modified by other sessions, that is, rows with exclusive (X) locks held.
This explains why the isolation level is often called
dirty read.
From a concurrency standpoint it means that there is no
