Re: transaction isolation

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

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.

--
Regards,
    David Cressey
    www.dcressey.com