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

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

Re: isolation level snapshot, how does it work?

Show all headers | View raw

On 3 Juni, 22:12, Erland Sommarskog <esq...@sommarskog.se> wrote:
> björn lundin (b.f.lun...@gmail.com) writes:
> > Is there a way to query the db or ODBC-driver whether I'm already in a
> > transaction or not?
>
> "SELECT @@trancount" returns your current level of transaction nested. Any
> number >= 1 means that you have a transaction in progress.

Ok, that is good to know


> There is a command, SET IMPLICIT_TRANSACTION ON. When this setting is in
> effect, the first command that performs an update will start a transaction.
> This setting is off by default, which is different from other product
> where implicit transactions is the norm. Implicit transactions is also
> what ANSI mandates.

This is, as i understand it, the underlaying mechanism for
the turning auotcommit on/off in the ODBC API.

> Personally, I don't like what is being is said in the ODBC manual. I much
> rather submit BEGIN/COMMIT/ROLLBACK TRANSACTION commands myself than
> have the API to perform things behind my back.

I agree. Would be much clearer if I could execute those statements and
expect it to work.

<Ćhunk of code snipped/>

> I don't know exactly what this code is doing, but it seems to me that
> it could be implemented with a single UPDATE statement for better
> performance and less risk for deadlocks.

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.
* The statements are prepared, and late on bound.

That may or may not be relevant to know or someone answering my
questions.
I'd rather put in some extra info, than leaving it out.
Of course, since you were looking into the logic of the statement,
I was not very clear with the purpose of putting it there.


> > This  READ_COMMITTED_SNAPSHOT, when I set it, does it apply to all
> > coming sessions in the database?
>
> All sessions that use the READ COMMITTED isolation level.

Ok.


> > I get the reply that there are no current transactions...
>
> Once your application exists, all these transactions will of course
> be rolled back.

Hmm, yes, I probably looked to late.

I will try swith to READ_COMMITTED_SNAPSHOT next week,
but for the sake of clearity I'd like to say that it now seems to
work. (with ISOLATION SNAPSHOT)

Turns out that the following sequence describes my INTENTIONS with the
code

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

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, the service I used did what I described as Process A from the
start.
However, that service was running in the context of another
transaction :-(

This of course meant that signaling via IPC was too fast, and process
B did not
see the record (as it should not)

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.
Erland, thanks again for the comments.

/Björn

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