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

The following bug has been logged on the website:

Bug reference:      13681
Logged by:          Olivier Dony
Email address:      odo@odoo.com
PostgreSQL version: 9.3.10
Operating system:   Ubuntu 14.04 LTS
Description:

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
|-----------------------------|----------------------------------|
    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"

# --

Many thanks,

Olivier


[1] http://www.postgresql.org/message-id/flat/560AA479.4080807@odoo.com
[2]

http://www.postgresql.org/message-id/flat/560AA479.4080807@odoo.com#1354271993.744124.1444079872314.JavaMail.yahoo@mail.yahoo.com
[3]
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0531549