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

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

Re: isolation level snapshot, how does it work?

Show all headers | View raw

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

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