Обсуждение: Set Transaction Isolation level bug

Your name               :       Alois Maier
Your email address      :       almaier@yahoo.com


System Configuration
---------------------
Architecture (example: Intel Pentium)   :   Intel Pentium
Operating System (example: Linux 2.0.26 ELF)    :   Linux 2.0.36 ELF
PostgreSQL version (example: PostgreSQL-6.5.1):   PostgreSQL-6.5.1
Compiler used (example:  gcc 2.8.0)             :   egcs-2.91.66

Please enter a FULL description of your problem:
------------------------------------------------
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  does not set the
transaction isolation level to serializable.

------------------------------------------------------------------
Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
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


__________________________________________________
Do You Yahoo!?
Bid and sell for free at http://auctions.yahoo.com

Alois Maier <almaier@yahoo.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

Tom Lane wrote:
>
> 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?
  ^^^^^^^^^^^^

My thoughts were to use ALTER SESSION for this.

Vadim