Thread: BUG #16454: Mixed isolation levels inside transactions
The following bug has been logged on the website:
Bug reference: 16454
Logged by: Lloyd Albin
Email address: lalbin@scharp.org
PostgreSQL version: 12.2
Operating system: Linux
Description:
I am seeing mixed/hybrid Isolation Levels when setting the isolation level
to SERIALIZABLE READ ONLY DEFERRABLE, this also happens with REPEATABLE READ
READ ONLY. The user tables sees only the data committed before the
transaction begins but the system tables sees data committed by other
transactions after the transaction begins. This means in my example the user
tables are Serializable Isolation Level and the system tables are Read
Committed Isolation Level.
I have testing this with PostgreSQL 10.6 & 12.2. The following reproducible
test case.
-- Writer Connection
CREATE EXTENSION pageinspect;
BEGIN;
CREATE SCHEMA test;
CREATE TABLE test.table ( test TEXT );
INSERT INTO test.table (test) VALUES ('row 1');
COMMIT;
-- Reader Connection
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
SELECT * FROM test.table;
test
-------
row 1
(1 row)
SELECT txid_current();
txid_current
--------------
349902815
(1 row)
SELECT oid, nspname FROM pg_catalog.pg_namespace WHERE nspname = 'test';
oid | nspname
-----------+---------
513537401 | test
(1 row)
-- Use this OID for the next Query
SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_oid
FROM heap_page_items(get_raw_page('pg_catalog.pg_namespace', 0))
WHERE t_oid = '(replace with saved OID)';
tuple | t_xmin | t_xmax | t_cid | t_ctid | t_oid
-------+-----------+-----------+-------+--------+-----------
13 | 349902806 | 0 | 0 | (0,13) | 513537401
(1 row)
-- Writer Connection
BEGIN;
CREATE SCHEMA new_test;
CREATE TABLE new_test.table (test TEXT);
INSERT INTO new_test.table VALUES ('row 2');
ALTER SCHEMA test RENAME TO old_test;
ALTER SCHEMA new_test RENAME TO test;
COMMIT;
-- Reader Connection
SELECT * FROM test.table;
test
------
(0 rows)
SELECT * FROM old_test.table;
test
-------
row 1
(1 row)
SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_oid
FROM heap_page_items(get_raw_page('pg_catalog.pg_namespace', 0))
WHERE t_oid = '(replace with saved OID)';
tuple | t_xmin | t_xmax | t_cid | t_ctid | t_oid
-------+-----------+-----------+-------+--------+-----------
13 | 349902806 | 349902827 | 7 | (0,15) | 513537401
15 | 349902827 | 0 | 7 | (0,15) | 513537401
(2 rows)
In the second half of the Reader Connection, I queried test.table and got 0
results. PostgreSQL actually queried the new table that was created after
the Reader's transaction started. When querying old_test.table, which did
not exist before the Reader's transaction started, I got the results I would
have expected from test.table. This means that the query used the wrong data
row from pg_catalog.pg_namespace. It used a row with an xmin greater than
the current txid. This means that the query used the Read Committed
Isolation Level when talking to the system tables instead of the isolation
level specified in the transaction causing a mixed/hybrid isolation level to
actually be used. I don't see any documentation talking about a mixed/hybrid
isolation level being implemented, so I am presuming this is a bug.
Lloyd Albin
Principal Database Administrator
Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
Vaccine and Infectious Disease Division (VIDD)
Fred Hutchinson Cancer Research Center (FHCRC)
On Thu, May 21, 2020 at 12:59 PM PG Bug reporting form <noreply@postgresql.org> wrote: > I am seeing mixed/hybrid Isolation Levels when setting the isolation level > to SERIALIZABLE READ ONLY DEFERRABLE, this also happens with REPEATABLE READ > READ ONLY. The user tables sees only the data committed before the > transaction begins but the system tables sees data committed by other > transactions after the transaction begins. This means in my example the user > tables are Serializable Isolation Level and the system tables are Read > Committed Isolation Level. That is expected behaviour. Perhaps we're missing a note about it in the manual?
PG Bug reporting form <noreply@postgresql.org> writes:
> I am seeing mixed/hybrid Isolation Levels when setting the isolation level
> to SERIALIZABLE READ ONLY DEFERRABLE, this also happens with REPEATABLE READ
> READ ONLY. The user tables sees only the data committed before the
> transaction begins but the system tables sees data committed by other
> transactions after the transaction begins. This means in my example the user
> tables are Serializable Isolation Level and the system tables are Read
> Committed Isolation Level.
As a general rule, all internal catalog accesses use latest-committed data
(so effectively Read Committed) regardless of what the user-level
visibility semantics are. This has to be so, because it just wouldn't do
to not be working with the latest data. As an example, you don't get to
ignore a CHECK constraint just because it got added after your transaction
started.
This does possibly result in some anomalies. We've judged that living
with that is better than the alternatives, which would involve imposing
far-more-draconian table locking and forced-transaction-rollback
requirements than exist now for serializable transactions. In the
example you give, the only practical alternative to the existing behavior
would be to fail the reader transaction as soon as it tries to access
the renamed schema.
regards, tom lane
> PG Bug reporting form <noreply@postgresql.org> writes: > > I am seeing mixed/hybrid Isolation Levels when setting the isolation > > level to SERIALIZABLE READ ONLY DEFERRABLE, this also happens with > > REPEATABLE READ READ ONLY. The user tables sees only the data > > committed before the transaction begins but the system tables sees > > data committed by other transactions after the transaction begins. > > This means in my example the user tables are Serializable Isolation > > Level and the system tables are Read Committed Isolation Level. > > As a general rule, all internal catalog accesses use latest-committed data (so effectively Read Committed) > regardless of what the user-level visibility semantics are. This has to be so, because it just wouldn't do to > not be working with the latest data. As an example, you don't get to ignore a CHECK constraint just > because it got added after your transaction started. I agree with your CHECK constraint use case, IF you were adding data then you are using the READ WRITE property, BUT thistransaction was set to use the READ ONLY property. The READ ONLY property would be better served to by matching dataand system tables instead of using the mixed/hybrid isolation level. Per the docs https://www.postgresql.org/docs/current/sql-set-transaction.html The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY. When all threeof these properties are selected for a transaction, the transaction may block when first acquiring its snapshot, afterwhich it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of contributingto or being canceled by a serialization failure. This mode is well suited for long-running reports or backups. Due to the above statement, I presume backups are using the SERIALIZABLE READ ONLY DEFERRABLE, which means this is also afailure point for backups. This could also be fixed by having READ ONLY property use the matching system tables. Lloyd Albin
"Albin, Lloyd P" <lalbin@scharp.org> writes: >> As a general rule, all internal catalog accesses use latest-committed data (so effectively Read Committed) >> regardless of what the user-level visibility semantics are. This has to be so, because it just wouldn't do to >> not be working with the latest data. As an example, you don't get to ignore a CHECK constraint just >> because it got added after your transaction started. > I agree with your CHECK constraint use case, IF you were adding data then you are using the READ WRITE property, BUT thistransaction was set to use the READ ONLY property. The READ ONLY property would be better served to by matching dataand system tables instead of using the mixed/hybrid isolation level. [ shrug... ] You are trying to wish away a very complicated set of problems. The CHECK case was merely an easy-to-grasp example; it's by no means the only issue. As a different example, suppose that someone DROPs an enormous table. Should we block the drop from really happening (and hence not reclaim the disk space) because somewhere there is a serializable transaction that could perhaps try to read that table later? You might say "I'm okay with having the serializable transaction fail with a serialization error if it does try to access such a table later". But then we'd be looking at trying to classify each internal catalog access as to how it should be handled in a serializable transaction, maybe with different rules for read-only vs read-write transactions. That would be a herculean task fraught with possibilities for error, and it might well require major code refactoring to ensure that there was a unique answer for each case. I don't think anybody's interested in tackling that job for such small returns in terms of practical usefulness of the system. (Admittedly, whether the returns are small is a judgment call ... but you've not even tried to make a case for why this anomaly is a problem for you, or why you couldn't dodge it by doing things differently.) I do concede that this isn't adequately documented. https://www.postgresql.org/docs/current/mvcc-caveats.html talks about some holes in the MVCC semantics model, but it doesn't mention this set of issues. regards, tom lane