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

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

Re: isolation level snapshot, how does it work?

Show all headers | View raw

On 5 Juni, 14:35, Erland Sommarskog <esq...@sommarskog.se> wrote:
> björn lundin (b.f.lun...@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.

Well, the code is there. ~5000 files that works well today
with Oracle. It is not my choice to write code that depends on
MARS functionality, it is already present. But
I was a bit surprised when I found out that MARS was added as
late as 2005.

> Still a stored procedure that performs all the business logic will
> more efficient that sending data forth and back between server and client.

Yes, but that is no option.
This system went from VAX/VMS on Mimer, RDB, then to Oracle,
via AIX, and in the late 90's to WinNT, then up to w2k8.
The VAX branch is dead, but now we also got Linux, ie
We got Aix, Win and Linux. Both Unices do Oracle only,
and win does Oracle, and soon sql-server.
I forsee Postgresql as an option within 5 years for all 3 os:es.
So, anything more than a simple trigger for traceability
is no option.
(And that was no fun. The trigger functionality is very different
between Oracle and Sql-server,
especially the lack of 'For each row' firing scheme found in Oracle -
which OF COURSE is used.
The concept of 'inserted/deleted' tables are fine, but different...
This is solved, but was a challenge)

> 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.

But of course. Thanks for enlightening me.

> 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.

That situation is what I had here. And in this system, it is regarded
as a bug.
Never ever signal before commit.

Process A here is a test-process written
just a couple of days ago, to aid in testing this db-port.
It i ironic that the tools to find bugs with, contains bugs  ;-)
But it helped gaining better understanding...

Thanks
/Björn

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