1

I would like to know why a shared lock is set when I use a foreign key on an INSERT statement, but not when I use a foreign key on an UPDATE or DELETE statement.

Specifications:

  • version: 10.1.47-MariaDB-0ubuntu0.18.04.1
  • innodb_version: 5.6.49-89.0
  • tx_isolation: READ-COMMITTED
  • innodb_strict_mode: ON
  • sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  • autocommit: ON

Here's my setup:

CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number_a` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_a` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_a` (`id_a`),
  CONSTRAINT `b_ibfk_2` FOREIGN KEY (`id_a`) REFERENCES `a` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `a` (`id`, `number_a`) VALUES
(1, 300);

Tests

I first run a certain (INSERT/UPDATE/DELETE) statement on Session 1 and then sleep, and then quickly run Session 2.

Test 1

Session 1: use INSERT statement (REPLACE INTO has the same result)

START TRANSACTION;
INSERT INTO b (id_a) VALUES (1); -- holds an S lock on table a, row 1
SELECT SLEEP(10);
UPDATE a SET number_a = 300 WHERE id = 1; -- tries to upgrade it to an X lock, deadlock
COMMIT;

Session 2:

START TRANSACTION; 
UPDATE a SET number_a = 200 WHERE id = 1; -- tries to acquire an X lock, deadlock
COMMIT;

Deadlock.

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-01-12 14:51:06 7f68be5c7700
*** (1) TRANSACTION:
TRANSACTION 9979531, ACTIVE 8 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 779, OS thread handle 0x7f68bf476700, query id 15307 localhost bb updating
UPDATE a SET number_a = 200 WHERE id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 686427 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx table locks 1 total table locks 3  trx id 9979531 lock_mode X locks rec but not gap waiting lock hold time 8 wait time before grant 0 
*** (2) TRANSACTION:
TRANSACTION 9979530, ACTIVE 10 sec starting index read
mysql tables in use 2, locked 2
5 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 777, OS thread handle 0x7f68be5c7700, query id 15312 localhost bb updating
UPDATE a SET number_a = 300 WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 686427 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx table locks 3 total table locks 3  trx id 9979530 lock mode S locks rec but not gap lock hold time 10 wait time before grant 0 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 686427 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx table locks 3 total table locks 3  trx id 9979530 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0 
*** WE ROLL BACK TRANSACTION (1) 

Test 2

Session 1: use DELETE statement

START TRANSACTION;
DELETE FROM b WHERE id_a=1;
SELECT SLEEP(10);
UPDATE a SET number_a = 300 WHERE id = 1;
COMMIT;

Session 2:

START TRANSACTION; 
UPDATE a SET number_a = 200 WHERE id = 1; 
COMMIT;

No deadlock.

Test 3

Session 1: use UPDATE statement

START TRANSACTION;
UPDATE b set id_a=2 WHERE id_a=1;
SELECT SLEEP(10);
UPDATE a SET number_a = 300 WHERE id = 1;
COMMIT;

Session 2:

START TRANSACTION; 
UPDATE a SET number_a = 200 WHERE id = 1;
COMMIT;

No deadlock.

Why does an INSERT/REPLACE statement set an S lock and an UPDATE/DELETE statement doesn't?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.