(Deadlock) Select Query Causes Insert Query to be Blocking in Transaction

Question

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:

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)

Answer 1

When you run the first two selects MySQL creates two X locks on supremum pseudo-record in tg_user_id because both ids (341292662 and 239302521) outside of the existing range. It looks like a bug. When you run inserts both of them try to get insert intention lock on the same record but they are blocked by previous locks.

Edit

Confirmed as bug: https://bugs.mysql.com/bug.php?id=25847

However, we have ugly workarounds to make this transaction scenario works: https://stackoverflow.com/questions/17068686/how-do-i-lock-on-an-innodb-row-that-doesnt-exist-yet

Answer 2

A SELECT ... FOR UPDATE;

Locks your table

For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... FOR SHARE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.)

see https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

Ttry it with out that, because it is only used for selected rows, that will be updated in the next sstep.

Answer 3

Simplify the DDL:

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

-->

PRIMARY KEY (`tg_user_id`),

(and get rid of id). That way there will (perhaps) be fewer locks taken and less overall complexity. (Caution: If you JOIN on id, such joins will need changing.)

That also gets rid of LAST_INSERT_ID(id), which seems to be part of the deadlock.