January 6, 2016 at 2:32 am
Hi There,
Example :
Query Window 1 :
drop table temp
create table temp(id int )
insert into temp values (1)
begin tran a
update temp set id = 2
waitfor delay '00:00:10'
commit
Query Window 2:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN b
SELECT * FROM temp
rollback
In this example transaction 'B' Will wait till transaction 'A' finishes. But in one DB it is not waiting, returning the old records
ie, Tran B resulting as 1
If I run the same query in some other db
Tran B resulting as 2
Please explain
January 6, 2016 at 2:45 am
Mosgt likely, the second database has READ_COMMITTED_SNAPSHOT isolation enabled. That feature was introduced in SQL Server 2008 (if I remember correctly) and changes the way locking works - when a row is uncommitted, it will not wait but it will instead return the last committed version of the data. That's why you get 1 returned and not 2 (as you would have if you had used NOLOCK).
January 6, 2016 at 2:51 am
That would be my guess as well. Unlike SNAPSHOT isolation level, the READ_COMMITTED_SNAPSHOT setting changes how READ COMMITTED works, changing it to use row versions not locks. If it's turned on for the DB, then any session running under READ COMMITTED will use row versions not locks and will give exactly the behaviour you see.
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
January 6, 2016 at 5:18 am
Hugo Kornelis (1/6/2016)
Mosgt likely, the second database has READ_COMMITTED_SNAPSHOT isolation enabled. That feature was introduced in SQL Server 2008 (if I remember correctly) and changes the way locking works - when a row is uncommitted, it will not wait but it will instead return the last committed version of the data. That's why you get 1 returned and not 2 (as you would have if you had used NOLOCK).
got it.. and resolved now working fine
January 11, 2016 at 8:42 am
Hugo Kornelis (1/6/2016)
Mosgt likely, the second database has READ_COMMITTED_SNAPSHOT isolation enabled. That feature was introduced in SQL Server 2008 (if I remember correctly) and changes the way locking works - when a row is uncommitted, it will not wait but it will instead return the last committed version of the data. That's why you get 1 returned and not 2 (as you would have if you had used NOLOCK).
Yes. Small correction - Read_Committed_Snapshot_Isolation released in SQL 2005
Regards,
Raj
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply