I have a group of tables which are heavily used in my application. I can’t show you the tables structure, because they are very large tables and actually it’s not necessary I think for this question.
So let’s say they are table A
, B
and C
. These tables are used by many processes of the application: online shop, payment processing, task scheduling, alert email sending, etc. It’s like A
is the Orders table, B
the Payments table, etc.
There is a scheduling process that launches a new execution of itself every 4 seconds. It launches a query to obtain one ordered record from A
and does something. This query:
JOIN
aA
withB
andC
and other tables- Has conditions on all the three of them
- Uses the field
A
.date_created
in bothWHERE
andORDER BY
- Uses
FOR UPDATE
condition
The query is executed inside a transaction that:
- Executes the query (
SELECT
) UPDATE
s the record returned by the query
and then it COMMIT
s.
The problem is that other views in the application ask to execute INSERT
queries on A
, B
or C
and sometimes these INSERT
s causes the error:
Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
These INSERT
s always make part of larger transactions which include other INSERT
s, some SELECT
s and/or UPDATE
s.
I need to use FOR UPDATE
on the query I explained above because it could possibly happen that the query is executed twice at the same time by two different processes: I must avoid that the same record is obtained by two different processes at the same time.
I’m using MySql 5.7.17 (MariaDB) with InnoDB tables, tx_isolation: READ-COMMITTED
How can I prevent those deadlocks?
Thank you very much