December 23, 2015 at 2:39 am
Hi there,
IF OBJECT_ID('Emp') is not null
begin
DROP TABLE Emp
end
create table Emp(ID int,Name Varchar(50),Salary Int)
insert into Emp(ID,Name,Salary)
values( 1,'David',1000)
insert into Emp(ID,Name,Salary)
values( 2,'Steve',2000)
insert into Emp(ID,Name,Salary)
values( 3,'Chris',3000)
Session 1:
begin tran
update emp set Salary=123 where ID=3
waitfor delay '00:00:15'
commit
Session 2:
run the below query with in 15 sec after running the above query in different query window.
set transaction isolation level read committed
select Salary from Emp where ID=3
So this will give output after 15 sec. Because currently transaction is not committed.
Now the salary of id 3 is 123.
If I again update with the same value for the id. Even if the transaction is not completed session give the output.
Try running the same query again. session 2 will not wait for the transaction to complete.
What is the reason behind it?
December 23, 2015 at 7:27 am
same on 2012!
when I change the value of the update, it goes back to waiting for the transaction.
how cool is that?!?!
edit: set isolation mode serializable and it waits all the time no matter the value of the update or last read.
December 23, 2015 at 11:21 am
I actually can't reproduce this. I'm using SQL Server 2014, and Session 2 WILL wait for Session 1 to commit on the 2nd run, which is what we'd normally expect with "read committed" isolation.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 23, 2015 at 11:23 am
It's an optimisation, SQL can tell that you're updating a column to itself and hence doesn't take the locks. I can't remember what the transaction log entry looks like for such an update, but the locks aren't needed to protect the changing data as nothing's changing.
There's probably some requirement of serialisable that requires it to take the range locks even in that case.
I think it's the same in 2008, iirc that's where I tested it before.
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
December 23, 2015 at 1:37 pm
Eric M Russell (12/23/2015)
I actually can't reproduce this. I'm using SQL Server 2014, and Session 2 WILL wait for Session 1 to commit on the 2nd run, which is what we'd normally expect with "read committed" isolation.
To follow up on what Gail said about the optimization, it's the optimization Paul White wrote about here: http://sqlblog.com/blogs/paul_white/archive/2010/11/01/read-committed-shared-locks-and-rollbacks.aspx. Specifically, from that article:
SQL Server contains an optimization that allows it to avoid taking row-level shared (S) locks in some circumstances. Specifically, it can skip shared locks if there is no risk of reading uncommitted data without them.
The SELECT query above did not block simply because no shared locks were issued. We can verify that this is the case by checking the locks acquired using Profiler:
I ran into the same behavior Eric saw at first (second session was always blocked, because it was still taking an S lock on the RID, and not skipping that per the optimization), and I think it's because I ran it in a database with ALLOW_SNAPSHOT_ISOLATION=ON (for me it was master, where that's always the case).
In a database with ALLOW_SNAPSHOT_ISOLATION off, the non-updating update shows only the LOP_BEGIN_XACT and LOP_COMMIT_XACT log records, and if you use DBCC IND to find the page for the table and check sys.dm_os_buffer_descriptors, is_modified for that page stays at 0 after the non-updating update. In those conditions, the optimization described by Paul kicked in and the SELECT did not take an S lock on the RID, and was not blocked by the non-updating update.
If I alter the same database so that ALLOW_SNAPSHOT_ISOLATION is on, the non-updating update also logs a LOP_MODIFY record, and is_modified for the page does get set to 1, which understandably prevents the optimization.
It's not the first time I've forgotten that master and msdb always have ALLOW_SNAPSHOT_ISOLATION on, but hopefully it'll be the last 🙂
Cheers!
EDIT: As Eric pointed out, an explicit mention of whether I could reproduce the non-blocking behavior was conspicuously absent from the first version of this post. I edited it so that was more clear.
December 23, 2015 at 2:32 pm
Yeah, but could you reproduce the non-blocking behaviour?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 23, 2015 at 2:39 pm
Eric M Russell (12/23/2015)
Yeah, but could you reproduce the non-blocking behaviour?
Heh, got wrapped up in trying to explain all the results and left out that fairly important part. :hehe: Yes, so long as ALLOW_SNAPSHOT_ISOLATION was off, then the optimization Paul talked about kicked in, the SELECT didn't take out an S lock on the RID, and thus wasn't blocked by the non-updating update.
Cheers!
December 24, 2015 at 2:52 am
SNAPSHOT is the row-versioning equivalent of SERIALIZABLE, so probably whatever requires the lock in SERIALIZABLE also requires it in case anyone's running SNAPSHOT and doing (probably) a mod to the table at the same time
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply