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

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

isolation level snapshot, how does it work?

Show all headers | View raw

Hi!
I've got a problem that I do not really understand,
and I am of course wondering if anyone could
enlighten me.

I have a system, ie several processes,
using ODBC to access a sql-server 2008 database.

At first, I was running with default transaction
isolation mode, but I ran into problems.

I would occasionally get a deadlock.
one process doing SELECT on a join of 4 tables,
and other processes were updating at least 3 of those
4 tables. probably the same records involved in the select.
This lead to sql-server rolling back the transaction
of the process doing select, with a dead-lock msg.

Not good, since the (big legacy) application
does not handle this situation on selects.
It counts on that a select is ALWAYS ok to do
(written for Oracle, this is a port to sql-server)

OK, looking at hinting selects with nolock, but
I don't want dirty reads to happen.
(industrial automation system, big things are moving,
and they cannot move just because I get a dirty read)

So, isolation level snapshot looks really good.
However now I get this situation:

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

restart PROCESS B, it looks for ALL records marked 'process B'
and it finds the record.

OK, timing issue you say.
So, between 4 and 5, I sleep 20 seconds. Still no go

Ok remove the sleep, and skip step 6,
and the record is found.

I thought that setting the snapshot level was
done when the statement was issued.
Here, it looks like process B creates the snapshot
BEFORE process A commits, thus missing the insert.
Correct ? (I hope not)

So, what can I do to
* avoid dirty reads
* avoid locks when doing selects
* still see stuff COMMITTED by others
?
/Björn

Back to comp.databases.ms-sqlserver | Previous | Next — 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