[rt-users] RT 4.4.1 and transaction isolation level on Postgres

[Alex Vandiver]

On Tue, 3 Jan 2017 17:06:47 +0100
Václav Ovsík <vaclav.ovsik at i.cz> wrote:
> How about the Mysql don't have this problem - is this caused by
> the different default transaction isolation level or not?

MySQL suffers from the exact same problem -- but, as it happens,
both more silently and more catastrophically.  See
https://github.com/bestpractical/rt/commit/e36364c5

> I can change isolation level in postgresql.conf to 'repeatable read'
> and things are different.

I advise against doing that.  Upon inspection, RT is not prepared to
deal with the "could not serialize access due to concurrent update"
errors that arise from updates to rows in multiple transactions in
Postgres' repeatable-read isolation.

Repeatable-read is only possible in MySQL because it has a fascinating
definition of "repeatable":

----------------------------- Process 1 ------------------------
mysql> set transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)

mysql> select id, Subject from Tickets where id = 1;
+----+---------+
| id | Subject |
+----+---------+
|  1 | foo     |
+----+---------+
1 row in set (0.00 sec)

----------------------------- Process 2 ------------------------

mysql> set transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)

mysql> update Tickets set Subject = 'bar' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

----------------------------- Process 1 ------------------------

mysql> select id, Subject from Tickets where id = 1;
+----+---------+
| id | Subject |
+----+---------+
|  1 | foo     |
+----+---------+
1 row in set (0.00 sec)

mysql> select id, Subject from Tickets where id = 1 FOR UPDATE;
+----+---------+
| id | Subject |
+----+---------+
|  1 | bar     |
+----+---------+
1 row in set (0.00 sec)
----------------------------------------------------------------


Contrast this with PostgreSQL, whose definition of repeatable read
acknowledges that fully consistent updates are not possible in all
cases:

----------------------------- Process 1 ------------------------
rt4=# start transaction;
START TRANSACTION
rt4=# set transaction isolation level repeatable read;
SET
rt4=# select id, Subject from Tickets where id = 1;
 id | subject 
----+---------
  1 | foo
(1 row)

----------------------------- Process 2 ------------------------
rt4=# start transaction;
START TRANSACTION
rt4=# set transaction isolation level repeatable read;
SET
rt4=# update Tickets set Subject = 'bar' where id = 1;
UPDATE 1
rt4=# commit;
COMMIT

----------------------------- Process 1 ------------------------
rt4=# select id, Subject from Tickets where id = 1;
 id | subject 
----+---------
  1 | foo
(1 row)

rt4=# select id, Subject from Tickets where id = 1 FOR UPDATE;
ERROR:  could not serialize access due to concurrent update

----------------------------------------------------------------

 ( Yes, MySQL requires SET TRANSACTION ISOLATION _outside_ the
   transaction, and PostgreSQL requires it to be _inside_.  See 
   https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html
   https://www.postgresql.org/docs/9.1/static/sql-set-transaction.html )


> Should I change the default isolation level on Postgres for RT to
> 'repeatable read'?

No.  You should try the 4.4/previewscrips-race branch, which I've just
pushed:

https://github.com/bestpractical/rt/compare/4.4-trunk...4.4/previewscrips-race

The gory details are contained in the commits therein.
 - Alex