Database Reference
In-Depth Information
■
In some cases, in a read committed transaction isolation level, sQL server can hold shared (s) locks for the
duration of
SELECT
statement rather than release them immediately after a row is read.
Note
In repeatable read transaction isolation level readers acquire (S) locks and hold them until the end of the
transaction, as shown in Figure
17-7
. This prevents other sessions from modifying the rows that were read because of
the shared (S) locks held on them. As with a read committed transaction isolation level, readers are not able to access
uncommitted data modified by other sessions.
Figure 17-7.
Repeatable read and shared lock behavior
The serializable isolation level works pretty much the same way with repeatable read but instead of using
individual row- level locks it uses key-
range
locks (both shared and exclusive). Range locks protect the key range
intervals rather than individual rows. We can see this behavior in Figure
17-8
.