Draft: ZSQLCatalog: force using READ COMMITTED isolation for indexation.
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- !2151 only, total duration 18393.5 seconds
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 CMFActivityAnd here is the comparison of the performance, that is even faster than 10.4 with innodb_locks_unsafe_for_binlog.
