Deadlock in UPDATE statement without transaction in MySQL

We are investigating the cause of the deadlock at the MySQL UPDATE site.
Exclusive processing is carried out with optimistic lock, no transaction is used, COMMIT is performed immediately after UPDATE is executed, and we are at a loss as to why the lock was deadlocked.
Thank you for your help.

MYSQL version: 5.7.12
Separation level:REPEATABLE READ
Auto-Commit:OFF

addition:

Occurs within a daily batch that is rotated in parallel for each user_id, and the frequency is about once a week.

Table Layout

CREATE TABLE `testtable`(
  `id`bigint(20)NOT NULL AUTO_INCREMENT,
  `user_id`bigint(20) unsigned NOT NULL,
  `image_id`bigint(20) unsigned NOT NULL,
  `status`tinyint(4)DEFAULT '0',
 
  `created_at`datetime NOT NULL,
  `updated_at`datetime NOT NULL,
  `deleted_at`datetime NOT NULL DEFAULT '1970-01-0100:00:00',
 
  PRIMARY KEY(`id`),
  KEY`index_image_id`(`image_id`)
ENGINE=InnoDB AUTO_INCREMENT=217159 DEFAULT CHARSET=utf8mb4;

LATEST DETECTED DEADLOCK excerpt from SHOW ENGINE INNODB STATUS output

***(1) TRANSACTION:
TRANSACTION 349587391, ACTIVE 29sec starting index read
mysql tables in use1, locked1
LOCK WAIT 9 lock structure(s), heap size 1136, 10 row lock(s), undolog entries 2

MySQL thread id 4004274, OS thread handle 47407848781568, query id 5622588628**.**.** prod_buildee updating
UPDATE`testtable`SET`status`='1', WHERE`user_id`='24914'AND`deleted_at`='1970-01-0100:00'AND
 `image_id` in ('23345', '23567', '23987', '23456', '23123', '23654', '23783')

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27692 page no 638 n bits 0 index PRIMARY of table `testdb`.`testtable`trx 
id349587391 lock_mode X locks rec but not gap waiting
Record lock, heap no 30 PHYSICAL RECORD: n_fields 20; compact format; infobits 0

***(2) TRANSACTION:

TRANSACTION 349587390, ACTIVE 0 sec fetch rows
mysql tables in use1, locked1
LOCK WAIT 751 lock structure(s), heap size 1136,83407 row lock(s)
MySQL thread id 4004316, OS thread handle 47407861106432, query id 5622588590**.**.**.** prod_buildee updating

UPDATE`testtable`SET`status`='2', `updated_at`='2021/1403:03:32', `integration_service_id`='2'
 WHERE`user_id`='23500'AND`status`='1'AND`deleted_at`='1970-01-0100:00'LIMIT50

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 27692 page no 638 n bits 0 index PRIMARY of table `testdb`.`testtable`trx id 349587390 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 20; compact format; infobits 0

mysql

2022-09-30 14:53

3 Answers

A deadlock can occur if UPDATE updates multiple records at the same time.

For example, if Client A updates 1, 2, 3, 4... and Client B updates 4, 3, 2, 1..., it will be deadlocked.

https://zenn.dev/tmtms/articles/202103-mysql-deadlock

2022-09-30 14:53

It should be resolved by indexing user_id and adding composite indexes to user_id and image_id.There is a high probability that transaction1 and 2 have the same image_id records.

2022-09-30 14:53

I think the second transaction is bad.

WHERE`user_id`='23500'AND`status`='1'AND`deleted_at`='1970-01-0100:00'

is narrowing down what to update, but the only condition for this is testtable index (image_id).does not hit ) either.As a result, MySQL must read all records to find tables to be updated by this query, and to prevent data inconsistencies, those records will be locked until transaction2 is complete.

If you index user_id, you will be able to lock both update queries based on that index, so I think this problem will probably be resolved.

2022-09-30 14:53

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656