Thread

1. Re: [BUGS] Set Transaction Isolation level bug

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-09-08T22:01:18Z

   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
   
   

2. Re: [BUGS] Set Transaction Isolation level bug

   Vadim Mikheev <vadim@krs.ru> — 1999-09-09T01:10:25Z

   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