locked
Transaction Isolation level RRS feed

  • Question

  • I am trying to understand the transaction isolation levels. A select statement within a transaction with isolation level read committed does not read the data modified by other transactions but not yet committed. But this happens only if the other transaction has isolation level SERIALIZABLE. When the other transaction isolation level is not serializable, the current transaction with isoloation level read committed is reading the data even if the other transaction has not committed. Is this the intended behavior? I expected that if other transaction, whatever the isolation level be, has not committed, the current transaction with read committed would not read data.
    Monday, February 12, 2018 3:28 AM

Answers

  • If your transaction isolation level is read committed, you will not be able to read changed, but uncommitted data.  It makes no difference what the other sessions transaction isolation level is.  If you attempt to read the changed data, your connection will be blocked until the other connection either commits or rolls back the change.

    Note that the only data you are guaranteed you cannot read is the data that was changed.  Depending on what your query is and what indexes you have you may well be able to read other rows in that table.  It's even possible with the right query and indexes to successfully read other columns i the row that was changed.  But if a column in a row has been changed, you will never be able to read the changed row/column until the change is committed when you are using read committed.

    If you think you have a case where that is not true, please post an example showing this.  But here is an example showing that read committed does block reads of changed data

    In one SSMS window, run

    use tempdb
    go
    set transaction isolation level read committed;
    go
    create table foo(a int primary key, b int, c int, d int);
    create index foob on foo(b);
    insert foo(a,b,c,d) values(1,2,3,4);
    go
    
    begin tran
    update foo set c=5 where a = 1;
    

    then open another SSMS window and run

    use tempdb
    go
    set transaction isolation level read committed;
    go
    select * from foo where a = 1;

    This second window will be blocked and not return any data until you go back to the first window and either do a commit or a rollback.

    Tom

    Monday, February 12, 2018 6:10 AM
  • Hi

    I believe if you read below article by Paul White you would understand better

    The Read Committed Isolation Level


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, February 12, 2018 6:26 AM

All replies

  • Hello,

    If you want to understand the concept of Transaction Isolation Level, you can refer to the following links:

    https://technet.microsoft.com/en-us/library/ms189122(v=sql.105).aspx

    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql

    These will help you out.

    Monday, February 12, 2018 4:39 AM
  • Not fully clear

    Can you post a sample code to indicate what you're trying to do?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, February 12, 2018 4:48 AM
  • If your transaction isolation level is read committed, you will not be able to read changed, but uncommitted data.  It makes no difference what the other sessions transaction isolation level is.  If you attempt to read the changed data, your connection will be blocked until the other connection either commits or rolls back the change.

    Note that the only data you are guaranteed you cannot read is the data that was changed.  Depending on what your query is and what indexes you have you may well be able to read other rows in that table.  It's even possible with the right query and indexes to successfully read other columns i the row that was changed.  But if a column in a row has been changed, you will never be able to read the changed row/column until the change is committed when you are using read committed.

    If you think you have a case where that is not true, please post an example showing this.  But here is an example showing that read committed does block reads of changed data

    In one SSMS window, run

    use tempdb
    go
    set transaction isolation level read committed;
    go
    create table foo(a int primary key, b int, c int, d int);
    create index foob on foo(b);
    insert foo(a,b,c,d) values(1,2,3,4);
    go
    
    begin tran
    update foo set c=5 where a = 1;
    

    then open another SSMS window and run

    use tempdb
    go
    set transaction isolation level read committed;
    go
    select * from foo where a = 1;

    This second window will be blocked and not return any data until you go back to the first window and either do a commit or a rollback.

    Tom

    Monday, February 12, 2018 6:10 AM
  • Hi

    I believe if you read below article by Paul White you would understand better

    The Read Committed Isolation Level


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, February 12, 2018 6:26 AM