MySQL Foreign Keys Deadlock

mysql-foreign-keys-deadlock.sql

--
-- This example shows how bad MySQL is when handling foreign key locks.
-- Deadlock happens when multiple transactions are inserting child records 
-- pointing to the same parent and then try to update the parent record.
-- 
-- The solution is to always try to update the parent record first 
-- but it could be hard to track the when database schema is more complex.
--
-- Based on: https://bugs.mysql.com/bug.php?id=48652
--  

-- 1. Setup
set session transaction isolation level read committed;
set autocommit = 0;

CREATE TABLE  parents (
	id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	counter int NOT NULL DEFAULT 0
);

CREATE TABLE children (
  id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  parent_id BIGINT NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES parents(id) ON DELETE CASCADE
);


INSERT INTO parents (id, counter) VALUES (1, 0);
COMMIT;


-- 2. client A
set session transaction isolation level read committed;
set autocommit = 0;

INSERT INTO children (parent_id) VALUES(1);

-- 3. client B
set session transaction isolation level read committed;
set autocommit = 0;

INSERT INTO children (parent_id) VALUES(1);
UPDATE parents SET counter = counter + 1 WHERE id = 1;

-- 4. client A
UPDATE parents SET counter = counter + 1 WHERE id = 1;
-- BOOM! DEADLOCK!