opc.three (1/9/2013)
GilaMonster (1/9/2013)
opc.three (1/9/2013)
The nice thing is that no queries need to change, not even the ones with the NOLOCK hint applied, and you'll automatically get transactionally consistent reads.Queries with nolock in them run under read uncommitted (for the hinted table anyway), regardless of whether the default is read committed or read committed snapshot. Hints will override the default or requested isolation level.
That's right. I was thinking of trying to explicitly set READ COMMITTED, which is not honored when READ_COMMITTED_SNAPSHOT is ON (i.e. DBCC USEROPTIONS still shows read committed snapshot). Setting READ UNCOMMITTED (and NOLOCK) would still be honored.
Well, it's kinda honoured. Technically read committed and read committed snapshot aren't two different isolation levels, they're one isolation level with two possible methods of being enforced, and that method is chosen with an ALTER DATABASE statement
If you have read committed snapshot on and really want the old locking behaviour (and there's some good reasons to do so), then you can only do that with a hint: WITH (READCOMMITTEDLOCK)
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