locked
READ UNCOMMITTED Isolation level honors the lock? RRS feed

  • Question

  • I have written a stored procedure which first truncates a table and then fills it. This truncate and insert happens within a Transaction block.

    SET XACT_ABORT ON;
      BEGIN TRY
        BEGIN TRANSACTION

      TRUNCATE TABLE TableName

      INSERT INTO Table1(Col1, Col2)
      SELECT Col1, col2 FROM Ref_Table1 tab1
      INNER JOIN Ref_Table2 tab2
      ON tab1.Id = tabl2.Id

      INSERT INTO Table2(Col1, Col2)
      SELECT Col1, col2 FROM Ref_Table1 tab1
      INNER JOIN Ref_Table2 tab2
      ON tab1.FirstName = tabl2.FirstName

        COMMIT TRANSACTION
      END TRY
      BEGIN CATCH 
        DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
        SELECT @ErrMsg = ERROR_MESSAGE(),
       @ErrSeverity = ERROR_SEVERITY()

        RAISERROR(@ErrMsg, @ErrSeverity, 1)
      END CATCH


    This stored proc may take a lot of time to finish its execution.

    Now I have another stored proc in which I want to read data from Table2. But SELECT statement continues to wait untill first stored proc finishes its execution. To overcome this waiting situtation I have used SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; in the second stored proc but it seems that Read Uncommitted also honors the lock. Is it true? If it is so then is there any way I can read Table2 while Stored proc 1 is still running.

    Alos I have found a msdn link "http://msdn.microsoft.com/en-us/library/ms175065(SQL.90).aspx" which says READ UNCOMMITTED Isolation level is "Not guaranteed" to honor the lock.

    Thanks

    Tuesday, December 6, 2011 3:04 PM

Answers

  • Do you really sure that you changed the isolation level to read uncommitted for the second connection that is selecting the data from table2.

    Connection 1:

    exec <procname> {parameterlist}

    GO

    Connection 2:

    set transaction isolation level read uncommitted

    select * from table2

    OR

    select * from table2(nolock)

    GO

     

    Yes What you have read is correct that read uncommitted will not honour the lock , the locks are just ignored.

    I tried the same stuff and its working for me.


    Thanks and regards, Rishabh , Microsoft Community Contributor
    • Marked as answer by Kalman Toth Tuesday, December 20, 2011 11:02 PM
    Tuesday, December 6, 2011 3:30 PM
  • This stored proc may take a lot of time to finish its execution.

    Now I have another stored proc in which I want to read data from Table2. But SELECT statement continues to wait untill first stored proc finishes its execution.

    You need to think over the architecture. In an OLTP system you have think small & quick. How about loading in batches so sproc 2 can read?

    Anything big, try to shift it somewhere: night, reporting server, data warehouse, etc.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Edited by Kalman Toth Tuesday, December 20, 2011 11:08 PM
    • Marked as answer by Kalman Toth Tuesday, December 20, 2011 11:08 PM
    Thursday, December 15, 2011 5:53 PM

All replies

  • Do you really sure that you changed the isolation level to read uncommitted for the second connection that is selecting the data from table2.

    Connection 1:

    exec <procname> {parameterlist}

    GO

    Connection 2:

    set transaction isolation level read uncommitted

    select * from table2

    OR

    select * from table2(nolock)

    GO

     

    Yes What you have read is correct that read uncommitted will not honour the lock , the locks are just ignored.

    I tried the same stuff and its working for me.


    Thanks and regards, Rishabh , Microsoft Community Contributor
    • Marked as answer by Kalman Toth Tuesday, December 20, 2011 11:02 PM
    Tuesday, December 6, 2011 3:30 PM
  • Yes Rishabh, I am sure about changing the islolation level to Read Uncommitted.

    Also in the msdn link I have posted above, the word used is "Not Guaranteed" and if I am getting it right then it means Read Uncommitted may or may not honor the lock.

    Tuesday, December 6, 2011 3:46 PM
  • did you tried the same way that I showed you.???

    and what you are reading applies to meta data not on your tables.


    Thanks and regards, Rishabh , Microsoft Community Contributor
    • Edited by Rishabh K Tuesday, December 6, 2011 4:22 PM
    Tuesday, December 6, 2011 4:15 PM
  • yes...I have tried both way, once only using the "set transaction isolation level read uncommitted" statement and then using the nolock but in vein.

    However I have got another post related to somewhat similar issue: http://social.msdn.microsoft.com/forums/en-us/sqldatabaseengine/thread/068D3FC7-648A-4EAA-B2C2-BE0E8E26AD82

    First reply in the post by "Marcel van der Holst - MSFT" is: READ UNCOMMITTED transactions will not take any database locks, but will still have to read databases pages to read the actual data. If other transactions are writing these pages at the same time, their might be some blocking between the two.
    Internally in the engine, we do not allow any transactions to read a page while a write is being in progress (we use latches to guarantee this). If a lot of transactions are writing while your big queries are going on, the big read might still become blocked.

    I think that in some cases READ UNCOMMITTED can be blocked and that is what happening in my case.

    Wednesday, December 7, 2011 6:51 AM
  • Yes it may be but I haven't seen or experienced this yet.

    Thanks and regards, Rishabh , Microsoft Community Contributor
    Wednesday, December 7, 2011 7:05 AM
  • This stored proc may take a lot of time to finish its execution.

    Now I have another stored proc in which I want to read data from Table2. But SELECT statement continues to wait untill first stored proc finishes its execution.

    You need to think over the architecture. In an OLTP system you have think small & quick. How about loading in batches so sproc 2 can read?

    Anything big, try to shift it somewhere: night, reporting server, data warehouse, etc.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Edited by Kalman Toth Tuesday, December 20, 2011 11:08 PM
    • Marked as answer by Kalman Toth Tuesday, December 20, 2011 11:08 PM
    Thursday, December 15, 2011 5:53 PM