Skip to content

ZSQLCatalog: force using READ COMMITTED isolation for indexation.

Kazuhiko Shiozaki requested to merge index-read-committed into isolation-level

This merge request supersedes !1997 (closed).

For the same reason that we are safe to use innodb_locks_unsafe_for_binlog, i.e. we don't use INSERT ... SELECT pattern, it should be safe to use READ COMMITTED isolation level in indexation.

With software/erp5/test/test/benchmarks.py in SlapOS repository, here is the difference of the number of deadlocks.

(below, I use 45448334 as the base ERP5 revision. Deadlock count details below are taken with simple grep and they can be counted twice but the still you can see the trend).

  • (ref: MariaDB 10.4, total duration 10376.7 seconds)
     30 INSERT INTO category
     72 REPLACE INTO delivery

    606 SELECT FOR UPDATE query in CMFActivity (outside SQLLock)
     88 UPDATE query in CMFActivity
  • (ref: MariaDB 11.8, total duration 18002.3 seconds)
     35 INSERT INTO stock
     65 INSERT INTO `quantity_unit_conversion`
    270 DELETE FROM category
    301 REPLACE INTO delivery
  69387 INSERT INTO category

    202 SELECT FOR UPDATE query in CMFActivity (outside SQLLock)
    131 UPDATE query in CMFActivity
     39 INSERT INTO stock
     28 INSERT INTO `quantity_unit_conversion`
    260 DELETE FROM category
    313 REPLACE INTO delivery
  73064 INSERT INTO category

    752 SELECT FOR UPDATE query in CMFActivity (outside SQLLock)
     72 UPDATE query in CMFActivity
     60 REPLACE INTO delivery
     69 INSERT INTO category

    584 SELECT FOR UPDATE query in CMFActivity (outside SQLLock)
     22 UPDATE query in CMFActivity

And here is the comparison of the performance, that is even faster than 10.4 with innodb_locks_unsafe_for_binlog.

11.8-2159

Edited by Kazuhiko Shiozaki

Merge request reports