22c70484   
   From: esquel@sommarskog.se   
      
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)