Deadlock on local_groups and local_users tables

[wiltonsr]

OCS Inventory informations

Ocs server version: Docker Image v2.9.2

Problem's description

My system has a large number of machines inventorying daily, around 110k, after upgrading to v2.9.2 Docker Image I got many errors 500.

On logs I get:

DBD::mysql::db do failed: Deadlock found when trying to get lock; try restarting transaction at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Update.pm line 103.

Analisying DB connections I get:

MariaDB [ocsweb_prd]> show open tables where in_use>0;
+------------+--------------+--------+-------------+
| Database   | Table        | In_use | Name_locked |
+------------+--------------+--------+-------------+
| ocsweb_prd | local_groups |   1014 |           0 |
| ocsweb_prd | local_users  |    976 |           0 |
+------------+--------------+--------+-------------+

When I run the SHOW PROCESSLIST I get many output from the following queries:

DELETE FROM local_users WHERE HARDWARE_ID=12342;
DELETE FROM local_groups WHERE HARDWARE_ID=10842;

Maybe related with:

• https://github.com/OCSInventory-NG/OCSInventory-ocsreports/commit/245ed07a9c97b366bd8c59d3587e0e45b4869db6
• https://github.com/OCSInventory-NG/OCSInventory-Server/commit/db17075363a39101c604410118f2db9e6733e6b5

• 360

[gillesdubois]

Hi @wiltonsr

We are going to review all the pull requests when the team is back from holidays :)

Update : the PR is not created yet but we optimized a few things on this side.

Regards, Gilles.

[gillesdubois]

Hi,

We made a lot of improvement with the 2.11.1 version on the database queries. Could you try with the new version ?

Regards, Gilles.