Slow Response Due to Deadlocks

Scenario

A large number of row lock conflicts occurred in a database between 14:00 and 15:00. The database response became slow because a large number of update and insert sessions in the kernel were waiting for row lock release and the CPU usage reached about 70%.

The following figure shows the row lock waits and metadata locks on the Cloud Eye console.

Table where a deadlock occurred:

********* 1. row *********
Table: table_test Create Table: CREATE TABLE table_test(
...
CONSTRAINT act_fk_exe_parent FOREIGN KEY (parent_id_) REFERENCES act_ru_execution (id_) ON DELETE CASCADE,
CONSTRAINT act_fk_exe_procdef FOREIGN KEY (proc_def_id_) REFERENCES act_re_procdef (id_),
CONSTRAINT act_fk_exe_procinst FOREIGN KEY (proc_inst_id_) REFERENCES act_ru_execution (id_) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT act_fk_exe_super FOREIGN KEY (super_exec_) REFERENCES act_ru_execution (id_) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Possible Causes

1. Deadlocks occurred in some tables. As a result, the CPU usage increased.
2. If a table contains a large number of foreign keys, updating records in the table requires not only the row lock of the table but also the corresponding locks of the tables associated with its foreign keys. In high concurrency scenarios, lock conflicts or deadlocks are more likely to occur than common tables. For details, see FOREIGN KEY Constraints.
3. If it detects a deadlocked table, RDS for MySQL rolls back the transaction. The tables associated with the foreign keys of the deadlocked table are also impacted. As a result, the database responsiveness slows down.

Solution

Check and optimize deadlocked tables and use the right foreign keys to avoid update conflicts and deadlocks.