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_520_ci NOT NULL,
`last_name` varchar(255) COLLATE utf8mb4_unicode_520_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_520_ci;
Logic:
- Select user information from database based on
tg_user_id
. - If the user has not been stored in database, then insert it.
- 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:
tg_user_id
is a UNIQUE key.- 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
- 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). - Why does the deadlock happen?
- 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)