3

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)
9
  • 2
    please add the last deadlock details from output of SHOW ENGINE INNODB STATUS command Aug 3, 2020 at 15:21
  • @Nikita Ok, please have a check. Aug 3, 2020 at 15:37
  • There are some tricky things, first the update and also last_insert_id, mysql has to lock, the table because it simply doesn't know, which row it has to update, then myslq has also to check if one of the constraints are touched
    – nbk
    Aug 3, 2020 at 15:51
  • 1
    what is the min tg_user_id in the table where tg_user_id >= 239302521? Aug 3, 2020 at 15:53
  • 1
    @Nikita I can reproduce the above deadlock with an empty table. Aug 3, 2020 at 16:06

3 Answers 3

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

4
  • I am still investigating it, will take a look about supremum pseudo-record problem. Aug 3, 2020 at 16:43
  • Is there a method to check what lock type is issued a query, and which rows are being locked by the such lock? It may be helpful for me to debug it deeper. Aug 3, 2020 at 16:49
  • 1
    @AmmarFaizi what version of MySQL Server do you use? Try select * from performance_schema.data_locks in MySQL 8 Aug 3, 2020 at 16:57
  • Yes, it is a bug. This bug has not been fixed for 13 years. Just found it here bugs.mysql.com/bug.php?id=25847 Aug 5, 2020 at 9:17
0

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.

2
  • Isn't that locks my rows (record)? (I am well aware of that will lock the row) Could you highlight the part that implies lock table? Aug 4, 2020 at 4:16
  • 1
    @AmmarFaizi it doesn't lock the table, it locks your row but there is no such record and MySQL locks a gap. In your case it locks one supremum pseudo-record but the problem is that it locks it twice and uses X lock. It's difenitely a bug, Aug 4, 2020 at 7:46
0

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.

1
  • I will have to recode almost the entire application if I do that. I am considering this and try to do experiments about removing that field first while looking for more acceptable solution. I will be back here to inform my decision later. Aug 4, 2020 at 4:21

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.