Re: transaction isolation

Hi David,

David Cressey schreef:
>
> > I think I see incorrect behaviour in the processing of transactions by a
> > reasonably well-known relational database product, especifically in
> showing the
> > contents of one transaction within another transaction environment. I 'd
> like to
> > know whether you agree that this behaviour is not what it should be, that
> is:
> > not ANSI-compatible. If you think it is ANSI-compatible, could you please
> > explain where I have gone astray?
>
> [snip]
>
> > Back to session 2:
> >
> > Query the table again:
> >
> > select * from test;
> >
> > This returns:
> > ID DESCRIPTION
> > -- -----------
> > (this is not correct, as the rows were committed in session 1!)
> >
> > Now commit the work in session 2:
> >
> > commit work;
>
> I disagree with you, and I agree with the way the (unnamed) product works.
>
> If the two rows were invisible to session 2 at the start of the transaction,
> then they must remain invisible to
> session 2 throughout the transaction. If new rows could suddenly "appear"
> in the middle of the session 2 transaction,
> then transaction 2 would not be seeing a consistent view of the database.

I can see your point here. Now I also see that I might want to rephrase my question and perhaps also rewrite the experiment. What bothers me in the (unnamed) product is that I do not seem to have any control on where a transaction starts. It 's just the first statement that is taken as a starting point. That might be alright (it is quite likely to be alright when the first statement is a DML-statement), but it is not necessarily so. Especially, there is no reason to take some arbitrary select statement as the start of a transaction. So I will try again without the inserts in session 2.

Incidently, most of my RDBMS work has been on Oracle. I have done the same experiment in an Oracle environment, and it works as I expect it would work (fair chance that my expectations are shaped by Oracle, after 10 years ;-). I don't know, however, whether a stricter transaction isolation level would make for the same behavior I observed in the other product.

>
> To take a simplistic approach, let's ask the question "did the session 1
> insert transaction take place logically before or logically after the
> session 2 query?" If the relational DBMS were to work as you suggest, the
> answer would be "neither". That's enough to convince me that your
> suggestion makes the behavior "non serializable". That's supposedly a
> no-no.

I would think the insert logically takes place at the moment it is committed. Nothing undetermined about that.

Once again thank for your remarks. I 'll delve deeper into this.

Regars
>
> --
> Regards,
> David Cressey
> www.dcressey.com
Received on Tue Oct 30 2001 - 23:46:56 CET