INF: Shared Access to Modified Data

Last reviewed: April 25, 1997
Article ID: Q45542

The information in this article applies to:

  - 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


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 25, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.