Re: [BUGS] Set Transaction Isolation level bug

Alois Maier <almaier(at)yahoo(dot)com> writes:
> test=> SHOW TRANSACTION ISOLATION LEVEL;
> NOTICE: TRANSACTION ISOLATION LEVEL is READ COMMITTED
> SHOW VARIABLE
> test=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> SET VARIABLE
> test=> SHOW TRANSACTION ISOLATION LEVEL;
> NOTICE: TRANSACTION ISOLATION LEVEL is READ COMMITTED
> SHOW VARIABLE

The code is currently set up to reset to READ COMMITTED mode at
the start of each transaction. So you can do

regression=> BEGIN;
BEGIN
regression=> SHOW TRANSACTION ISOLATION LEVEL;
NOTICE: TRANSACTION ISOLATION LEVEL is READ COMMITTED
SHOW VARIABLE
regression=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET VARIABLE
regression=> SHOW TRANSACTION ISOLATION LEVEL;
NOTICE: TRANSACTION ISOLATION LEVEL is SERIALIZABLE
SHOW VARIABLE
...

but the SET's effect goes away at END. If you're not inside a
transaction block then the SET's effect goes away immediately.

This may well be a dumb decision from a user-interface point of view,
but someone designed the code to work that way.

The SQL92 spec seems to say that SET TRANSACTION can only be executed
outside of transaction blocks, which would imply that the present
behavior is not compatible with the standard.

My own inclination is to think that if SET TRANSACTION ISOLATION LEVEL
is executed *inside* a BEGIN block, then it should set the IsoLevel for
that transaction block only, but if executed as a freestanding
transaction then it ought to set the default IsoLevel for subsequent
transactions. Comments?

regards, tom lane