Why do I need a useless insert statement before a delete statement to prevent a deadlock in this MySQL scenario, and is there a better way?

user_116223 0
Viewed 164

I have run into a scenario where deleting from and inserting into a table with foreign keys in MySQL is, I believe, causing gap locks to occur that result in a deadlock situation. I am trying to trouble-shoot how to fix this, and the only solution I've come up with is an additional insert before the delete and insert. I was wondering if anyone can explain why these useless inserts are needed and why "select for update" is not similarly locking the rows. I am using MySQL version 5.7.23.

Example schema and initial rows:

CREATE TABLE parent (
  id INT UNSIGNED NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE child (
  name VARCHAR(127) NOT NULL,
  parent INT UNSIGNED NOT NULL,
    FOREIGN KEY (parent) REFERENCES parent(id)
) ENGINE=InnoDB;

INSERT INTO parent VALUES (1);
INSERT INTO parent VALUES (2);

In my code, I need to replace all of the current children for a target parent with a new group of children (zero or more rows), and there may or may not already be children present for this target parent. The current code looks like this, and causes deadlocks when there are no children present in the table for these particular parents:

transaction a:
BEGIN WORK;
DELETE FROM child WHERE parent=1;

transaction b:
BEGIN WORK;
DELETE FROM child WHERE parent=2;

transaction a:
INSERT INTO child VALUES ('a', 1);
-- client a hangs waiting for lock

transaction b:
INSERT INTO child VALUES ('b', 2);
-- client b aborts: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

This will reliably trigger a deadlock if you run these transaction statements in this order in different client sessions. I believe this is because of gap locking. When I attempted to switch the transaction to a READ COMMITTED isolation level, it will avoid the deadlock, but potentially phantom rows will occur if the two transactions are operating on children of the same parent.

Inserting a useless row for the parent prior to the delete appears to fix the deadlock. There is no deadlock in the following scenario:

transaction a:
BEGIN WORK;
INSERT INTO child VALUES ('fake', 1);
DELETE FROM child WHERE parent=1;

transaction b:
BEGIN WORK;
INSERT INTO child VALUES ('fake', 2);
DELETE FROM child WHERE parent=2;
-- client b hangs waiting for lock

transaction a:
INSERT INTO child VALUES ('a', 1);
COMMIT;
-- no deadlock; client b now has lock

transaction b:
INSERT INTO child VALUES ('b', 2);
COMMIT;

I thought maybe instead of this insert, I could substitute a select statement to get the same locks as the useless insert, but the following does not prevent the deadlock:

transaction a:
BEGIN WORK;
SELECT * FROM child WHERE parent=1 FOR UPDATE;
DELETE FROM child WHERE parent=1;

transaction b:
BEGIN WORK;
SELECT * FROM child WHERE parent=2 FOR UPDATE;
DELETE FROM child WHERE parent=2;

transaction a:
INSERT INTO child VALUES ('a', 1);
-- client a hangs waiting for lock

transaction b:
INSERT INTO child VALUES ('b', 2);
-- client b aborts: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Why do neither the delete nor "select for update" statements retrieve the same lock as inserting a useless row, and is there a better way to accomplish this task and avoid deadlocks? Keep in mind that the child table may or may not have one or more rows already existing for the target parent and I'd like to delete all of the current rows in the child table for the target parent and replace with zero or more new children. Thank you!

deadlock foreign-keys transactions mysql
2 Answers

Without a PRIMARY KEY on Child, most actions need to do a full table scan.

In any case, your code must plan for deadlocks, even if you might be able to avoid most deadlocks.

In general, the way to handle deadlocks is to test for such after each SQL statement. When one occurs, start over the transaction. (It is likely that the second attempt will avoid other connections that helped cause it.)

Note that this means that transactions should be coded to be "short". The longer a transaction is, the more other connections might be blocked.

user_17668310

There two things. First of all, you should definitely define a primary key for the child table. When there is not one, MySQL is trying to identify a field that can be used for indexing and if not found, an internal one will be generated. This will cause overhead and is just a bad habit. More on this for example in here https://vettabase.com/blog/why-tables-need-a-primary-key-in-mariadb-and-mysql/.

Second, and the most important thing is, why are you using two transactions? If you are about to edit the same table and to do both operations as an atomic transaction, do then inside of one commit:

BEGIN WORK;

DELETE FROM child WHERE parent=1;
DELETE FROM child WHERE parent=2;

INSERT INTO child VALUES ('a', 1);

INSERT INTO child VALUES ('b', 2);

COMMIT;
user_61609510
Related