9cb321a8   
   From: esquel@sommarskog.se   
      
   björn lundin (b.f.lundin@gmail.com) writes:   
   > 1, Process A sets isolation level to snapshot   
   > 2, Process A starts new transaction by setting autocommit to off   
   > 3, Process A inserts a record in db   
   > 4, process A commits (sqlEndTran(commit))   
   > 5, process A signals via ipc to process B, sending the key of this   
   > record   
   > 6, Process B sets isolation level to snapshot   
   > 7, Process B starts new transaction by setting autocommit to off   
   > 8, Process B selects on the table, with the given key as where clause   
   > 9, Process B get no hit. record is not found   
   >   
   > Looking with sqlcmd in db, the record IS there   
      
   Note that in SQL Server there is no such thing as "SET AUTOCOMMIT OFF".   
   There is only BEGIN TRANSACTION. It sounds from your description that   
   B already has a transaction in progress, possibly orphaned.   
      
   Rather than using true snapshot isolation, you could consider setting   
   the database in READ_COMMITTED_SNAPSHOT. In this case, the default   
   isolation level READ COMMITTED will be implemented through the snaphot.   
   In this, you don't need to start to explicit transactions to use the   
   snapshot and avoid that readers and writers block each other.   
      
   Particularly, in this situation, even if B has an orphaned transaction,   
   it will still see the row inserted by A, because it reads committed   
   data.   
      
   --   
   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)