Serialization errors despite KEY SHARE/NO KEY UPDATE
От | Olivier Dony |
---|---|
Тема | Serialization errors despite KEY SHARE/NO KEY UPDATE |
Дата | |
Msg-id | 560AA479.4080807@odoo.com обсуждение исходный текст |
Ответы |
Re: Serialization errors despite KEY SHARE/NO KEY UPDATE
|
Список | pgsql-general |
Using 9.3.9 and 9.4.4 I'm experiencing serialization errors that I have trouble explaining based on the documentation. The concurrent transactions are framework-driven and all of them are running in REPEATABLE READ isolation level. The error can be reproduced as shown below. T1 fails when acquiring the second KEY SHARE lock for the same row, but does not fail if only one of the T1 UPDATEs is executed, either the first or the second one. Is there some hidden read/write dependency at play here? My understanding of the KEY SHARE/NO KEY UPDATE locks introduced in 9.3 was that they would avoid side-effects/blocking between transactions that are only linked via FK constraints, as long as the target PK was not touched. Isn't it the case here? If not, is there a reliable way to make T2 fail instead of T1 in such situations? I've tried adding an explicit "SELECT date FROM users WHERE id = 1 FOR UPDATE NOWAIT" at the beginning of T2 but that doesn't block at all. Thanks for the clarifications! -- Setup tables CREATE TABLE users ( id serial PRIMARY KEY, name varchar, date timestamp ); CREATE TABLE orders ( id serial PRIMARY KEY, name varchar, user_id int REFERENCES users (id) ); INSERT INTO users (id, name) VALUES (1, 'foo'); INSERT INTO orders (id, name) VALUES (1, 'order 1'); -- Run 2 concurrent transactions: T1 and T2 T1 T2 |-----------------------------|----------------------------------| BEGIN ISOLATION LEVEL REPEATABLE READ; UPDATE orders SET name = 'order of foo', user_id = 1 WHERE id = 1; BEGIN ISOLATION LEVEL REPEATABLE READ; UPDATE users SET date = now() WHERE id = 1; COMMIT; UPDATE orders SET name = 'order of foo (2)', user_id = 1 WHERE id = 1; T1 fails with: ERROR: could not serialize access due to concurrent update CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."users" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x" -- Olivier
В списке pgsql-general по дате отправления: