Database: MySQL

I have a running app with concurrency transactions in it. I noticed that there are so many errors occur related to deadlock.

The error says

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction.

Investigating Deadlock

I tried to read the stack trace from my app and report from SHOW ENGINE INNODB STATUS. I was able to reproduce the deadlock in a simple scenario, but do not understand why does the deadlock happen. Below is the detailed information:

DDL

CREATE TABLE `tg_users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tg_user_id` bigint(20) unsigned NOT NULL,
  `username` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `first_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `photo` bigint(20) unsigned DEFAULT NULL,
  `group_msg_count` bigint(20) unsigned NOT NULL DEFAULT '0',
  `private_msg_count` bigint(20) unsigned NOT NULL DEFAULT '0',
  `is_bot` enum('0','1') CHARACTER SET utf8 NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tg_user_id` (`tg_user_id`),
  KEY `username` (`username`),
  KEY `first_name` (`first_name`),
  KEY `last_name` (`last_name`),
  KEY `group_msg_count` (`group_msg_count`),
  KEY `private_msg_count` (`private_msg_count`),
  KEY `created_at` (`created_at`),
  KEY `updated_at` (`updated_at`),
  KEY `photo` (`photo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Logic:

1. Select user information from database based on tg_user_id.
2. If the user has not been stored in database, then insert it.
3. If the such data exists, then compare and update.

In this case I focus on insert scenario.

Just to emphasize things that may be important to check:

1. tg_user_id is a UNIQUE key.
2. Connection 1 and connection 2 have different tg_user_id on its queries
   (so I think the row lock is not relevant since they are working with different row).

Reproduction Steps:

– (Step 1) Open Connection 1 and do select query. (uniq identity: 341292662)

START TRANSACTION;
-- Query OK, 0 rows affected (0.00 sec)

SELECT 
`id`,`username`,`first_name`,`last_name`,`photo`,`group_msg_count`,`private_msg_count` 
FROM `tg_users` WHERE `tg_user_id` = '341292662' FOR UPDATE;
-- Query OK, 0 rows affected (0.00 sec)

Keep connection 1 open, create connection 2.

– (Step 2) Open Connection 2 and do select query. (uniq identity: 239302521)

START TRANSACTION;
-- Query OK, 0 rows affected (0.00 sec)

SELECT
`id`,`username`,`first_name`,`last_name`,`photo`,`group_msg_count`,`private_msg_count`
FROM `tg_users` WHERE `tg_user_id` = '239302521' FOR UPDATE;
-- Query OK, 0 rows affected (0.00 sec)

– (Step 3) Back to connection 1 and insert (why is it blocking?) (uniq identity: 341292662)

INSERT INTO `tg_users`
(`tg_user_id`,`username`,`first_name`,`last_name`,`photo`,`group_msg_count`,`private_msg_count`,`is_bot`,`created_at`)
VALUES
('341292662', 'derido', 'Derido', 'Novelium', NULL, '0', '0', '0', NOW())
ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`id`);
-- Now the insert query is blocking

– (Step 4) Back to connection 2 and insert (why it gets deadlock?) (uniq identity: 239302521)

INSERT INTO `tg_users`
(`tg_user_id`,`username`,`first_name`,`last_name`,`photo`,`group_msg_count`,`private_msg_count`,`is_bot`,`created_at`)
VALUES
('239302521', 'tomorimo', 'Tomorimo', 'Avede', NULL, '0', '0', '0', NOW())
ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`id`);
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Questions

1. Why does the query in step 3 get blocking? Even though the row in step 3 is not involved in connection 2 (which is issuing IX lock).
2. Why does the deadlock happen?
3. What can I do to prevent the deadlock?

Deadlock Report from SHOW ENGINE INNODB STATUS

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-08-03 22:35:26 0x9e08eb40
*** (1) TRANSACTION:
TRANSACTION 293921, ACTIVE 24 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1080, 2 row lock(s), undo log entries 1
MySQL thread id 6309, OS thread handle 2406095680, query id 41333 192.168.50.1 ammarfaizi2 update
INSERT INTO `tg_users`
(`tg_user_id`,`username`,`first_name`,`last_name`,`photo`,`group_msg_count`,`private_msg_count`,`is_bot`,`created_at`)
VALUES
('341292662', 'derido', 'Derido', 'Novelium', NULL, '0', '0', '0', NOW())
ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`id`)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10236 page no 4 n bits 72 index tg_user_id of table `test`.`tg_users` trx id 293921 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 293922, ACTIVE 15 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1080, 2 row lock(s), undo log entries 1
MySQL thread id 6308, OS thread handle 2651384640, query id 41334 192.168.50.1 ammarfaizi2 update
INSERT INTO `tg_users`
(`tg_user_id`,`username`,`first_name`,`last_name`,`photo`,`group_msg_count`,`private_msg_count`,`is_bot`,`created_at`)
VALUES
('239302521', 'tomorimo', 'Tomorimo', 'Avede', NULL, '0', '0', '0', NOW())
ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`id`)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10236 page no 4 n bits 72 index tg_user_id of table `test`.`tg_users` trx id 293922 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10236 page no 4 n bits 72 index tg_user_id of table `test`.`tg_users` trx id 293922 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

PRIMARY KEY (`id`),
UNIQUE KEY `tg_user_id` (`tg_user_id`),

PRIMARY KEY (`tg_user_id`),