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.

• !2151 + !2159

      9 REPLACE INTO delivery
    108 INSERT INTO category

      4 SELECT query in CMFActivity
  10680 SELECT FOR UPDATE query in CMFActivity (outside SQLLock)
    112 SELECT FOR UPDATE query in CMFActivity (inside SQLLock)
    504 UPDATE query in CMFActivity

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

     45 REPLACE INTO delivery
     84 INSERT INTO category

    222 SELECT FOR UPDATE query in CMFActivity (outside SQLLock)
    149 SELECT FOR UPDATE query in CMFActivity (inside SQLLock)
      1 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

     51 INSERT INTO stock
    117 INSERT INTO `quantity_unit_conversion`
    210 DELETE FROM category
    230 REPLACE INTO delivery
  86074 INSERT INTO category

  11362 SELECT FOR UPDATE query in CMFActivity (outside SQLLock)
    179 SELECT FOR UPDATE query in CMFActivity (inside SQLLock)
   8393 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 + !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 ?

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