GitLab

CMFActivity: use 'FOR UPDATE SKIP LOCKED' for MariaDB >= 10.6.

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)

     30 INSERT INTO category
     72 REPLACE INTO delivery

    606 SELECT FOR UPDATE query in CMFActivity (outside SQLLock)
     88 UPDATE query in CMFActivity

• !2151 only

     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

• !2151 + !2158 (this)

     57 INSERT INTO stock
    148 INSERT INTO quantity_unit_conversion
    272 DELETE FROM category
    331 REPLACE INTO delivery
  82585 INSERT INTO category

     77 SELECT FOR UPDATE query in CMFActivity (before outside SQLLock)
      5 UPDATE query in CMFActivity

• !2151 + !2159

     48 REPLACE INTO delivery
     60 INSERT INTO category

    458 SELECT FOR UPDATE query in CMFActivity (outside SQLLock)
     34 UPDATE query in CMFActivity

• !2151 + !2159 + !2158 (this)

     21 REPLACE INTO delivery
     48 INSERT INTO category

     48 SELECT FOR UPDATE query in CMFActivity (inside SQLLock)
     84 SELECT FOR UPDATE query in CMFActivity (outside SQLLock)
      5 UPDATE query in CMFActivity

Much less deadlocks in CMFActivity queries, though the overall performance is not different with/without the changes here.

(description below are old trial with removing GET_LOCK / RELEASE_LOCK)

• !2151 only

     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

• !2151 + !2158 (this) without SQLLock

     90 INSERT INTO stock
    354 DELETE FROM category
    523 REPLACE INTO delivery
  66369 INSERT INTO category

   1438 SELECT FOR UPDATE query in CMFActivity (before outside SQLLock)
  38610 SELECT FOR UPDATE query in CMFActivity (before inside SQLLock)
   4771 UPDATE query in CMFActivity

By removing SQLLock, SELECT FOR UPDATE queries are issued much more frequently thus caused more deadlocks.

Let's compare having !2159 as well.

• !2151 + !2159

     48 REPLACE INTO delivery
     60 INSERT INTO category

    458 SELECT FOR UPDATE query in CMFActivity (outside SQLLock)
     34 UPDATE query in CMFActivity

• !2151 + !2159 + !2158 (this) without SQLLock

    237 REPLACE INTO delivery
     36 INSERT INTO category

    574 SELECT FOR UPDATE query in CMFActivity (before outside SQLLock)
   1956 SELECT FOR UPDATE query in CMFActivity (before inside SQLLock)
   1271 UPDATE query in CMFActivity

With including !2159, we have less deadlocks, maybe because less deadlocks and faster processing in indexation activities ?

Also here is !2151 + !2159 and revert 18b5e4ed.

• !2151 + !2159 without SQLLock

     33 REPLACE INTO delivery
     57 INSERT INTO category

  54819 SELECT FOR UPDATE query in CMFActivity (before outside SQLLock)
  14500 SELECT FOR UPDATE query in CMFActivity (before inside SQLLock)
   3766 UPDATE query in CMFActivity

So 18b5e4ed seems still effective even with READ COMMITTED isolation level.

And here is the comparison of the performance. No significant benefit from the changes here.