NB: If you were wondering why some row level S locks appear to be missing this is an optimisation explained here. However you are then just left relying on implementation details to produce the behaviour that you want. In practice you may well not get this issue for the exact query you have posted as SQL Server would not choose this join type by default. Looking at the other join types in Profiler it appears that this issue could not arise under either the merge join or nested loops plan for this particular query (no locks get released until all are acquired) but the point remains that read committed just guarantees you do not read dirty data it makes no promises about consistency.
This post demonstrates the difference with a great example using black and white marbles.
READ COMMITTED ISOLATION LEVEL SQL SERVER 2012 UPDATE
Update person SET name = CASE WHEN name='foo' then 'bar' ELSE 'foo' END Serializable vs Snapshot Isolation Level These both allow a very high level of isolation, but they are not exactly functionally equivalent. I'm finding it hard finding an online source that unambiguously states this but quoting from p.648 of "Microsoft SQL Server 2008 Internals"Īlso see this MSDN blog post Setup Script CREATE TABLE person You would then be guaranteed statement level consistency. Q2: Yes the answer to this question would change if read_committed_snapshot is on. At read committed level shared locks are released as soon as the data is read (not at the end of the transaction or even the end of the statement) read committed just guarantees you do not read dirty data it makes no promises about consistency. Q1: Yes this is perfectly possible at least in theory.
0 kommentar(er)
