INF: Shared Access to Modified Data

• Microsoft SQL Server version 4.2 for 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.

MORE INFORMATION

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 query words: Optimization and tuning

Keywords          : kbusage SSrvServer 
Version           : 4.2
Platform          : OS/2 
Issue type        : 

Last Reviewed: March 6, 1999