none
transaction isolation level RRS feed

  • Question

  • Hello
    i need to use transactions to make some atomic insert - update on more tables, yet dirty reads are allowed. i've written the following code:

            conn.Open()
            transc = conn.BeginTransaction(Data.IsolationLevel.ReadUncommitted)
    
            Using conn
                Using transc
                    Try
                        .......doing my inserts/updates
                        transc.Commit()
                        ...
    Then , during debugging, i've stopped the application on the commit statement , placing a  breakpoint. A colleague has tried to do a query on one of the records being updated, but it hasn't returned untill i've let the application commit. Have i misunderstood the transaction management ? doesn't the isolation level read uncommitted allows other to read records before the commit happens?

    thanks in advance

    Stefano
    Tuesday, June 23, 2009 4:16 PM

Answers

  • With SQL Server, an explicit transaction is not required to read the latest committed data,  This will occur in either of the row-versioning isolation levels (ReadCommitted with READ_COMMITTED_SNAPSHOT database option ON and also in Snapshot with the ALLOW_SNAPSHOT_ISOLATION database option ON).  Optimistic concurrency conflict detection will works with either of the row-versioning isolation levels so you don't need to redesign your application to use a row-versioning isolation level.
     
    We've mentioned different transaction isolation levels and concurrency behavior in this thread.  Let me summarize the SQL Server behavior here for clarity (see http://msdn.microsoft.com/en-us/library/ms189122.aspx for details).  Keep in mind that writers always block writers:
    ReadUncommitted:  Readers do not block writers and writers don't block readers.  Dirty reads are allowed.
     
    ReadCommitted (default):  Readers block writers (readers typically hold locks only for a short duration while data are read).  Writers block readers to prevent dirty reads of uncommitted data.
     
    ReadCommitted with READ_COMMITTED_SNAPSHOT database option on:  Readers don't block writers and writers don't block readers.  Readers get the latest committed row version from the time the statement began in order to avoid dirty reads.
     
    Snapshot (which requires ALLOW_SNAPSHOT_ISOLATION database option ON):  Readers don't block writers and writers don't block readers.  Readers get the latest committed row version from the time the current transaction began in order to avoid dirty reads.

    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang/
    • Marked as answer by Bit01 Monday, June 29, 2009 2:28 PM
    Saturday, June 27, 2009 2:01 PM

All replies

  • > doesn't the isolation level read uncommitted allows other

    Looks like you got this backwards.  You can't "force" other users to see your uncommitted data.  The ReadUncommitted level allows you to see uncommitted data.

    Instead, the other users need to run their selects under the ReadUncommitted level.

    Note that ReadUncommitted is not a good idea if you care about accuracy.  SQL 2005 and up provide the snapshot isolation level as an alternative to help avoid blocking in a more reasonable way.

    Tuesday, June 23, 2009 10:38 PM
  • To add on to BinaryCoder's response, you can also enable the database READ_COMMITTED_SNAPSHOT isolation level so that writers dong block readers in IsolationLevel.ReadCommitted.  That way, the reading session will see the latest committed version if the data instead of being blocked.  Be aware that this can increase tempdb usage due to its usage as the version store.

    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang /
    "Bit01" wrote in message news:bf37cbf0-5a3c-416 2-be50-1ba278cf662a...
    Hello
    i need to use transactions to make some atomic insert - update on more tables, yet dirty reads are allowed. i've written the following code:

            conn.Open()
            transc = 
    conn.BeginTransaction(Data.IsolationLevel.ReadUncommitted)
    
            Using conn
                Using transc
                    Try
                        .......doing my inserts/updates
                        transc.Commit()
                        ...
    Then , during debugging, i've stopped the application on the commit statement , placing a  breakpoint. A colleague has tried to do a query on one of the records being updated, but it hasn't returned untill i've let the application commit. Have i misunderstood the transaction management ? doesn't the isolation level read uncommitted allows other to read records before the commit happens?

    thanks in advance

    Stefano
    Wednesday, June 24, 2009 12:58 AM
  • thank you both for yours asnswers.
    So, if i've undestood correctly, other user must do their selects (not insert or update) inside their own transaction?

    When this piece of code starts i'm being asked to do the following thing:
    - Accomplish all operations in an atomic manner (and possibly forbid other user to update the records i'm changing)
    - Let other user to read records as they were before i started my transaction

    So the conclusion is : whatever isolation level i use in my trasactions other users are allowed to read original data from tables if they starts their own transaction with isolation level snapshot?

    then now i've got another problem: this application is the porting of an old vb6 app, which uses old ado data manipulation placing locks on db (adlockoptimistic - adlockpessimistic). In general data layer is based on linq to sql and uses the usual strategy : db -> linq to sql data provider -> table showing data, and uses optimistic concurrency for inserts - updates : when it submit changes lock the tables. So i've to move all the "optimistic" transactions from the connected mode to linq to sql , to let readings while i'm doing inserts - updates ?

    regards
    Stefano
    Thursday, June 25, 2009 8:55 AM
  • With SQL Server, an explicit transaction is not required to read the latest committed data,  This will occur in either of the row-versioning isolation levels (ReadCommitted with READ_COMMITTED_SNAPSHOT database option ON and also in Snapshot with the ALLOW_SNAPSHOT_ISOLATION database option ON).  Optimistic concurrency conflict detection will works with either of the row-versioning isolation levels so you don't need to redesign your application to use a row-versioning isolation level.
     
    We've mentioned different transaction isolation levels and concurrency behavior in this thread.  Let me summarize the SQL Server behavior here for clarity (see http://msdn.microsoft.com/en-us/library/ms189122.aspx for details).  Keep in mind that writers always block writers:
    ReadUncommitted:  Readers do not block writers and writers don't block readers.  Dirty reads are allowed.
     
    ReadCommitted (default):  Readers block writers (readers typically hold locks only for a short duration while data are read).  Writers block readers to prevent dirty reads of uncommitted data.
     
    ReadCommitted with READ_COMMITTED_SNAPSHOT database option on:  Readers don't block writers and writers don't block readers.  Readers get the latest committed row version from the time the statement began in order to avoid dirty reads.
     
    Snapshot (which requires ALLOW_SNAPSHOT_ISOLATION database option ON):  Readers don't block writers and writers don't block readers.  Readers get the latest committed row version from the time the current transaction began in order to avoid dirty reads.

    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang/
    • Marked as answer by Bit01 Monday, June 29, 2009 2:28 PM
    Saturday, June 27, 2009 2:01 PM