Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #18824: Inconsistent results for isolation level settings

BUG #18824: Inconsistent results for isolation level settings

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      18824
Logged by:          dlyixue
Email address:      2322370369@qq.com
PostgreSQL version: 17.3
Operating system:   Ubuntu 22.04
Description:

When I was trying to set the isolation level, I realized that different
methods of setting the same isolation level gave inconsistent results. 
This makes me wonder if the current setting is taking effect.
In my opinion, use case 1 and use case 2 should have the same results. Why
are the results of the queries different?
Is this a design problem? I feel like the documentation needs to explain the
difference as well.

The test cases are as follows:
Case 1:
reproduce=# BEGIN;
BEGIN
reproduce=*# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL
SERIALIZABLE;
SET
reproduce=*# SHOW TRANSACTION ISOLATION LEVEL;
 transaction_isolation 
-----------------------
 read committed
(1 row)

Case 2:
reproduce=# BEGIN;
BEGIN
reproduce=*# SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET
reproduce=*# SHOW TRANSACTION ISOLATION LEVEL;
 transaction_isolation 
-----------------------
 serializable
(1 row)

Re: BUG #18824: Inconsistent results for isolation level settings

From
Greg Sabino Mullane
Date:

The SET TRANSACTION command sets the characteristics of the current transaction. It has no effect on any subsequent transactions. SET SESSION CHARACTERISTICS sets the default transaction characteristics for subsequent transactions of a session

In practical usage, I rarely use SET SESSION, I prefer to set it at the transaction level.

For your demo, try also examining SHOW default_transaction_isolation;


Cheers,
Greg

--
Enterprise Postgres Software Products & Tech Support