CODEWITHSUNDEEP
sqlsql-server
onur demir
onur demir

Reputation: 431

Proper way to restore database transaction level from read uncommitted

I have to get data from a table which is excessively updated. Dirty read is not a problem for me. I decided to use read uncommitted in my stored procedure.

Then I added this line before select:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

I learned that this code scope is connection, unlike nolock. I heard you should change it to default after your work is done is that right?

Do I have to just add 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

end of the line? I could not find any example on the web where isolation level is changed back after work is done. Is there any example?

Upvotes: 4

Views: 523

Answers (1)

IVNSTN
IVNSTN

Reputation: 9316

Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed. All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

...

If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

https://msdn.microsoft.com/en-us/library/ms173763.aspx

Upvotes: 3

Related Questions