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:

• JOINa A with B and C and other tables
• Has conditions on all the three of them
• Uses the field A.date_created in both WHERE and ORDER BY
• Uses FOR UPDATE condition

The query is executed inside a transaction that:

• Executes the query (SELECT)
• UPDATEs the record returned by the query

and then it COMMITs.

The problem is that other views in the application ask to execute INSERT queries on A, B or C and sometimes these INSERTs causes the error:

Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction  

These INSERTs always make part of larger transactions which include other INSERTs, some SELECTs and/or UPDATEs.

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