On Tue, 3 Jan 2017 17:06:47 +0100 Václav Ovsík <vaclav.ov...@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