BUG #13681: Serialization failures caused by new multixact code of 9.3 (back-patch request)

From: odo(at)odoo(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13681: Serialization failures caused by new multixact code of 9.3 (back-patch request)
Date: 2015-10-14 16:48:44
Message-ID: 20151014164844.3019.25750@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13681
Logged by: Olivier Dony
Email address: odo(at)odoo(dot)com
PostgreSQL version: 9.3.10
Operating system: Ubuntu 14.04 LTS

This is a back-patch request of 05315498012530d44cd89a209242a243374e274d to
9.3 and 9.4.

As discussed in the -general list[1], both 9.3 and 9.4 show spurious
serialization failures when faced with the use case included below.

In 9.2, T2 used to block until T1's commit, but then continued without
error, and in 9.5 both T1 and T2 proceed without blocking nor error.

Kevin Grittner located[2] the root cause as a regression that was fixed by
Álvaro at 0531549 [3].

For what it's worth, our system uses many long-running transactions
(background jobs, batch data imports, etc.) that are frequently interrupted
and rolled back by micro-transactions coming from users who just happen to
update minor data on their records (such as their last login date). So this
bug appears to cause more than just a performance regression.

Let me know if there is anything I can do to help the back-patch happen...

# The use case

-- 1. 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');

-- 2. Run 2 concurrent transactions: T1 and T2
T1 T2

UPDATE orders
SET name = 'order of foo',
user_id = 1
WHERE id = 1;


UPDATE users
SET date = now()
WHERE id = 1;


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"

# --

Many thanks,


[1] http://www.postgresql.org/message-id/flat/560AA479(dot)4080807(at)odoo(dot)com


Browse pgsql-bugs by date

  From Date Subject
Next Message doppalapudisaikumar 2015-10-15 01:19:38 BUG #13682: error while connecting to database
Previous Message b.yordanov2 2015-10-14 16:08:18 BUG #13680: PostgreSQL backend process crashes on jsonb_object_agg() in plpgsql recursive function