ID Number: Q45542
1.10 1.11 4.20
OS/2
Summary:
When a user modifies a table, other users cannot be permitted to read
the tentative modifications if J.N. Gray's (see below) consistency
level 2 is to be maintained (restrict access to "dirty" data). Because
the finest granularity of locking supported by SQL Server is the page,
no page containing uncommitted updates can be read by another user. If
many pages are locked, SQL Server may decide that it is more efficient
to lock the entire table.
SAVE TRANSACTION does not commit. It is an intermediate rollback
point. As always, dirty data cannot be accessed by others until it is
committed.
According to J.N. Gray in "Granularity of Locks and Degrees of
Consistency in a Shared Data Base" (1979) there are four levels of
consistency:
1. In Level 0, updates are permitted to uncommitted (dirty) data. This
can result in inconsistency because if the tentative update is
updated again by another user, and the first user aborts or rolls
back, the second user's update will be lost.
2. In Level 1, updates to uncommitted updates are prohibited, but
reads to uncommitted updates are permitted. This can result in
decisions being made on data that is still tentative (may be rolled
back).
3. In Level 2, reading uncommitted updates is prohibited. This is the
lowest level of consistency supported by SQL Server. Occasionally,
there are reasons to want to read uncommitted updates, but SQL
Server provides no option to do so at this time. SQL Server
implements this level of consistency by holding "exclusive" locks
on all updated pages for the duration of the logical unit of work
(LUW). If BEGIN TRAN is not used, the locks are held for the
duration of the SQL statement that caused them. If BEGIN TRAN is
used, the locks are held until COMMIT TRAN is executed.
4. In Level 3, other users are prohibited from updating data that has
been read by a particular user until that user commits. This allows
a user to "see" an unchanging image of the data for the duration of
the transaction. Other users may read the data, but no other users
may update it. SQL Server implements this level of consistency by
holding "share" locks on all pages in tables read with the HOLDLOCK
option until the transaction commits. Exclusive locks are placed on
updated pages, just as in level 2. Locks are released at the end of
the LUW, just as in level 2.
Additional reference words: Optimization and tuning