Обсуждение: 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:
TheSET TRANSACTIONcommand sets the characteristics of the current transaction. It has no effect on any subsequent transactions.SET SESSION CHARACTERISTICSsets 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