Post reply

Setting Transaction Isolation Level

  • May 6, 2015 at 1:41 pm

    #303606

    Hi everybody,

    I have probably a simple question that I need the answer to it.

    By setting the TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; is this automatically sets all the joined tables to NOLOCK?

    Or, in order this statement to work right, this needs to be only done inside BEGIN TRAN > COMMIT (ROLLBACK) statement?

    Your help with this question would be greatly appreciated.

    Thank you.

    Alex

  • May 6, 2015 at 1:45 pm

    #1796057

    AER (5/6/2015)

    By setting the TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; is this automatically sets all the joined tables to NOLOCK?

    In effect, yes.

    After you set the isolation level, a query will not issue a shared lock preventing data it is reading for being changed, and will be able to read data currently locked by other transactions.

    In other words, its usually a spectacularly bad idea.

  • May 6, 2015 at 1:58 pm

    #1796061

    Thanks,

    So, in other words, if I do not set my script in a transaction, but only would have the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED statement, then none of the tables in the script joins would be locked.

    Is it a true statement?

    Thank you.

    Alex

  • May 6, 2015 at 4:11 pm

    #1796077

    Not exactly. Snapshot isolation creates a copy of the row while it's being modified. That copy can be read while the modification is occurring. The lock on the table is still going on.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • May 6, 2015 at 4:18 pm

    #1796078

    Thanks Grant,

    Now I am completely lost. I was asking about the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED statement; not a snapshot isolation.

    Can you advice please?

    Thank you.

    Alex

  • May 7, 2015 at 2:31 am

    #1796124

    Yes, that setting (which does not need a transaction) means that all tables are read without shared locks. There are still schema stability locks, modifications still take locks. Read Uncommitted allows both dirty reads and duplicate reads and missing rows.

    If you're going to do this, make sure that it's in a scenario where if the query results are wrong a fair portion of the time there will not be business consequences. Basically, make sure before doing this that the exact values returned aren't important as they can be wrong if there is *any* concurrent inserts or updates which increase the row size on the table, even if it's not rows that the query will affect.

    In a test I did, under read uncommitted, 60% of queries which counted the rows in a table returned an incorrect row count.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 7, 2015 at 4:33 am

    #1796153

    AER (5/6/2015)

    Thanks Grant,

    Now I am completely lost. I was asking about the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED statement; not a snapshot isolation.

    Can you advice please?

    Thank you.

    Alex

    I completely misread everything in this thread. Disregard my statement.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • May 7, 2015 at 7:48 am

    #1796226

    Thanks a lot to everybody,

    Gila, that is exactly what I needed, thanks.

    Alex

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply