Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #397

Re: isolation level snapshot, how does it work?

Show all headers | View raw

björn lundin (b.f.lundin@gmail.com) writes:
> Well yes. But I put the code there to illustrate some requirements
> that I have
> * MARS, or multiple active record sets. It is used a lot.

Ugh! I can't say that MARS is anything I recommend. When used correctly, it 
is not likely to be harmful, but it can lure you do things you should not 
do.

> * The statements are prepared, and late on bound.

Still a stored procedure that performs all the business logic will 
more efficient that sending data forth and back between server and client.

> But in reality, there were 3 more steps
> 
> -1, Process A sets isolation level to snapshot
> 0 , Process A starts new transaction by setting autocommit to off
> 5.5 process A commits (sqlEndTran(commit))

So it's not process B that has an uncommitted transaction as I assumed,
but process A. The effect is the same.
 
> However, it all worked in READ_COMMITTED mode, which implies on of two
> things
> * Process B got a dirty read
> * The Snapshot handling is so much slower that Process B was too quick
> reading,
>   so Process A had not yet done its final commit. And when not using
> snapshot,
>   process A send the IPC, and committed, BEFORE Process A got to
> read.
> 
> I prefer to believe its the second option, but will keep my eyes on it
> until I know for sure.

Not really. But without snapshot, B was blocked until A had performed its 
final commit, whereas with snapshot B just reads the old data. No scheme is 
perfect, and with snapshot - either true snapshot isolation or 
read_commited_snapshot - the risk is that you read stale data.

If you have situtaions where a process may be signaled about new data that 
has not yet been committed, that process cannot use snapshot isolation to 
read the data. This is the one situation where enabling RCSI can wreak
havoc on an application. You can use the hint (READCOMMITTEDLOCK) to
prevent this from happening.
-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

isolation level snapshot, how does it work? björn lundin <b.f.lundin@gmail.com> - 2011-06-02 08:22 -0700
  Re: isolation level snapshot, how does it work? Erland Sommarskog <esquel@sommarskog.se> - 2011-06-02 23:49 +0200
    Re: isolation level snapshot, how does it work? björn lundin <b.f.lundin@gmail.com> - 2011-06-03 03:05 -0700
      Re: isolation level snapshot, how does it work? Erland Sommarskog <esquel@sommarskog.se> - 2011-06-03 22:12 +0200
        Re: isolation level snapshot, how does it work? björn lundin <b.f.lundin@gmail.com> - 2011-06-05 03:04 -0700
          Re: isolation level snapshot, how does it work? Erland Sommarskog <esquel@sommarskog.se> - 2011-06-05 14:35 +0200
            Re: isolation level snapshot, how does it work? björn lundin <b.f.lundin@gmail.com> - 2011-06-05 10:31 -0700

csiph-web