I'm having a very strange problem. I'm working on a project with PHP 7.1.17 and MariaDB 5.5.56. The tables use the InnoDB engine.
The dynamic is as follows. A user will place an order with an X number of items. When they make this request, the data is processed (create invoice, modify stocks, etc ...) and it is at this moment, when they occur, in an unknown way, errors when making UPDATE.
Following up for several days, I have not managed to find an error pattern (from a block with the same order id, sometimes it is the first, others the third, etc ...). However, I generated a log in which, among other data, I stored the error returned by MariaDB and it tells me:
Deadlock found when trying to get lock; try restarting transaction
Reviewing the documentation, it shows us that this is a matter of a deadlock between transactions. However, there is a paradox that there are no transactions in all this development. In fact, this UPDATE is in a foreach for each of the aforementioned items.
For reasons that are not relevant, on my server I can not do SHOW ENGINE INNODB STATUS, however, I can obtain this data by reviewing the current status of the server:
Innodb row lock time 90.1 k
Innodb row lock time avg 154
Innodb row lock time max 4.8 k
Innodb row lock waits 584
Innodb deadlocks 0
The query that the vast majority of times fails is the same:
UPDATE datos_pedido SET entregado='1',unidades='2',almacen='2',fin_de_garantia='2020-5-14 17:34:42' WHERE pedido='355860' AND producto='46880'
All fields are indexes, but they are not related to other tables. In development, AUTOCOMMIT is set to True.
What could be happening? It seems a very obvious error and however, I can not find information of similar cases (Deadlock without using such transactions properly). What more data would you need to collect in order to solve it?
Thank you very much in advance.