7

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.

  • Have you tested this? I would be surprised if it even let you set set transaction isolation level read uncommitted with an update or delete. Just how could it take un update lock in read uncommitted? – paparazzo Aug 23 '16 at 17:15
  • 1
    Can you explain why you want to do this? – datagod Aug 23 '16 at 17:15
  • Read carefully - sqlperformance.com/2015/04/t-sql-queries/… – Kin Shah Aug 23 '16 at 17:20
  • 1
    @VBernon, let me rephrase. Why do you want to use "with (nolock)" or change the default isolation level to read uncommitted? What is the problem you are trying to solve? "with (nolock)" is not a turbo button to make SQL faster. Underlying performance problems should be addressed prior to resorting to it, in my opinion. – datagod Aug 23 '16 at 17:58
  • 1
    @datagod Sorry, I don't mean to be rude but my question is pretty straightforward and it seems like your motive is more to help me understand the ramifications of read uncommitted than to answer my question. I understand the ramifications. I just want to know if putting that statement at the top of my proc will affect update/delete statements or not. If it will, then I don't want to use it. If not, then I do want to use it for the sake of the select statements in the proc. – BVernon Aug 23 '16 at 18:05
3

"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

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions

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

use tempdb
create table dbo.t (a int)
go
insert into dbo.t (a) values (10)
go
begin tran
update dbo.t 
set a = 20
where a = 10

-- commit tran

In another Window (W2), run the following (see the comments for the behavior)

use TempdB
set transaction isolation level read uncommitted
select * from dbo.t -- you can have dirty read, showing [a]=20, which is an uncommitted UPDATE in W1

go
-- the following update will wait (before proper locks are granted)
update dbo.t 
set a = 30
where a= 10

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,

WITH ( Table_Hint_Limited )

Specifies one or more table hints that are allowed for a target table. The WITH keyword and the parentheses are required. NOLOCK and READUNCOMMITTED are not allowed. For information about table hints, see Table Hints (Transact-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.

  • I beg difference, if you issue an UPDATE (in session 1 where you have read uncommitted transaction level), the update statement still needs to issue update lock and exclusive lock BEFORE it can even start. I tested myself in such scenario, and I will update my answer to give out my proof. – jyao Aug 23 '16 at 17:15
  • 1
    The link you gave still demos that READ itself will read bad data (just as expected), it does not touch UPDATE at all. You can think of this way, when I need to do an UPDATE, yes, the UPDATE will read and locate the target rows first, BUT, even if the UPDATE can read the data, it still cannot put an U (or X) lock on the dirty data. – jyao Aug 23 '16 at 17:36
  • @jyao I'm not yet sure if your examples are a good proof or not (not saying they are or aren't... I just don't know yet) of your answer, however I gave you +1 for being the only person so far to actually understand my question :) – BVernon Aug 23 '16 at 17:47
  • Thanks @BVernon, I will update my answer with more info that address your question about UPDATE. You are right that NOLOCK is ignored (actually not allowed according to MSDN) – jyao Aug 23 '16 at 17:55
  • 2
    Jeffrey - when I'm wrong I don't mind admitting it! – Max Vernon Aug 23 '16 at 18:31
5

If I have a stored procedure with set transaction isolation level read uncommitted, will it affect update statements?

Read uncommitted allows dirty reads. An X lock will be taken on the row or higher level (in the data page or index) before it is made dirty. Rows accessed directly by the query when locating a row to update will take a U lock and be blocked.

However it is still possible for an Update to be affected by the isolation level.

Connection 1

CREATE TABLE T1
(
X INT NULL,
Y INT NULL
);

INSERT INTO T1 DEFAULT VALUES;

BEGIN TRAN
UPDATE T1 SET X = 100;    

WAITFOR DELAY '00:00:10'

ROLLBACK;

Connection 2 (run this within 10 seconds of firing off connection 1)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

UPDATE T1
SET    Y = (SELECT SUM(X)
            FROM   T1);

SELECT *
FROM   T1; 

Result

enter image description here

The read operation read the uncommitted value and the final result was used in the UPDATE even though the 100 that was read was eventually rolled back.

3

Based on your edit, now the question is more clear ..

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.

Transaction isolation level should be thought in terms of read operations. Isolation levels control how the read operations are protected from the other write operations.

The database engine governs the locking behavior of all write operations, and you cannot change that behavior at the database level.

From BOL :

All isolation levels always issue exclusive locks for write operations and hold the locks for the entire duration of the transaction.

Read up :

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.