If I have a stored procedure with set transaction isolation level read uncommitted
, will it affect update statements?
I know that you should not use with (nolock)
on update/delete statements, and this does pretty much the same thing but not sure if SQL ignores it on update statements in the procedure or if I should be careful not to use it if there are update statements.
EDIT:
Sorry for the confusion. I'm not trying to figure out what the effect of using this type of locking on manipulation statements would be or whether it's a good idea. In fact I do NOT want to use this kind of locking on manipulation statements, and so my question is whether putting "set transaction …" at the top of my stored proc is ever going to be honored by update/delete statements or whether it will be ignored. My hope is that it is just ignored.
I am fully aware of the effects (with its pros and cons) it will have on select statements.
Best Answer
"Transaction isolation level" mainly affects (in my understanding) the behavior of the read operation, i.e. whether a read operation will issue some locks. In the case of "read uncommitted", here is a quote from MSDN
So to your question, the answer is NO, the update will not be affected by "read uncommitted" transaction isolation level inside the same stored procedure.
-- Update (a sample to prove this logic) In SSMS, we open two windows, and in Window 1 (W1 hereafter), run the following
In another Window (W2), run the following (see the comments for the behavior)
This means the UPDATE statement in W2 (with READ UNCOMMITTED) is not impacted by the transaction isolation level (i.e. still behaves as expected) as the SELECT statement.
-- UPDATE 2: According to MSDN UPDATE t-sql,
So my understanding is that when you run UPDATE statement, in SQL Server, there is no way that you can update on dirty data (i.e. uncommitted data) even if you can read the dirty data in your session.