BUG #18824: Inconsistent results for isolation level settings
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)
From the docs at
https://www.postgresql.org/docs/current/sql-set-transaction.html:
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
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support