GilaMonster (1/9/2013)
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
Yep. Two "modes" of one isolation level is how I think about them. And SNAPSHOT is another animal completely, its own isolation level unto itself.
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)
That's a new one to me. I will have to look into it. Thanks.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato