Setting Transaction Isolation Level

Question

Setting Transaction Isolation Level

AER

SSCrazy

Points: 2763
More actions
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

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

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

Nevyn SSChampion Points: 14210 More actions · Answer 1

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.

AER SSCrazy Points: 2763 More actions · Answer 2

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

AER SSCrazy Points: 2763 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 6

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

AER SSCrazy Points: 2763 More actions · Answer 7

Thanks a lot to everybody,

Gila, that is exactly what I needed, thanks.

Alex