Обсуждение: Unexpected serialization error
Hi guys,
I had no luck over at general slack channel. So I'm beginning to treat this as a bug. Here's a test case that unexpectedly fails at last insert. I expect it not to fail because the rows that the two transactions act on are unrelated to one another.
CREATE TABLE t1 ( class integer NOT NULL ); CREATE INDEX ON t1 (class); CREATE TABLE t2 ( class integer NOT NULL ); CREATE INDEX ON t2 (class); ALTER SYSTEM SET enable_seqscan TO off; -- Session 1 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT * FROM t2 WHERE class=1; -- Session 2 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT * FROM t1 WHERE class=2; INSERT INTO t2 VALUES(2); COMMIT; -- Session 1 INSERT INTO t1 VALUES(1); COMMIT;
Postgres version is PostgreSQL 12.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.3.0) 9.3.0, 64-bit.
-- Lule
=?UTF-8?B?THVrYSDFvWl0bmlr?= <luka.zitnik@gmail.com> writes:
> I had no luck over at general slack channel. So I'm beginning to treat this
> as a bug. Here's a test case that unexpectedly fails at last insert. I
> expect it not to fail because the rows that the two transactions act on are
> unrelated to one another.
The bug is that you're assuming exact tracking of the SSI serialization
rules. It's not done that way, because it'd be prohibitively expensive.
(I've not dug into this example in any detail, but I suspect it's
acquiring page-level not tuple-level predicate locks, thus the
transactions conflict because each tries to update a page the other one
already read.)
The short answer is that ANY application that's depending on serializable
mode MUST be prepared to retry serialization failures. You don't get to
skip that just because there theoretically shouldn't be a failure.
regards, tom lane
Got it, thanks. If this particular test case implied worse than expected performance (e.g. significantly more than the 0.03% of failures, http://vldb.org/pvldb/vol5/p1850_danrkports_vldb2012.pdf, RUBiS example), it probably would have been taken into consideration.
Btw, more out of curiosity, is it possible to nudge the test to pass by changing some of these settings?
ssi_test=# select name from pg_settings where name ~ 'pred';
name
--------------------------------
max_pred_locks_per_page
max_pred_locks_per_relation
max_pred_locks_per_transaction
(3 rows)
name
--------------------------------
max_pred_locks_per_page
max_pred_locks_per_relation
max_pred_locks_per_transaction
(3 rows)
Here's the lock types I see right before the last insert:
ssi_test=# SELECT mode, locktype, relation::regclass, page, tuple
FROM pg_locks WHERE mode = 'SIReadLock';
mode | locktype | relation | page | tuple
------------+----------+--------------+------+-------
SIReadLock | page | t1_class_idx | 1 |
SIReadLock | page | t2_class_idx | 1 |
(2 rows)
FROM pg_locks WHERE mode = 'SIReadLock';
mode | locktype | relation | page | tuple
------------+----------+--------------+------+-------
SIReadLock | page | t1_class_idx | 1 |
SIReadLock | page | t2_class_idx | 1 |
(2 rows)
On Sat, Feb 13, 2021 at 6:21 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Luka Žitnik <luka.zitnik@gmail.com> writes:
> I had no luck over at general slack channel. So I'm beginning to treat this
> as a bug. Here's a test case that unexpectedly fails at last insert. I
> expect it not to fail because the rows that the two transactions act on are
> unrelated to one another.
The bug is that you're assuming exact tracking of the SSI serialization
rules. It's not done that way, because it'd be prohibitively expensive.
(I've not dug into this example in any detail, but I suspect it's
acquiring page-level not tuple-level predicate locks, thus the
transactions conflict because each tries to update a page the other one
already read.)
The short answer is that ANY application that's depending on serializable
mode MUST be prepared to retry serialization failures. You don't get to
skip that just because there theoretically shouldn't be a failure.
regards, tom lane