This is the 11th day of my participation in the Gwen Challenge in November. Check out the details: The last Gwen Challenge in 2021

Because the table data needed to be modified concurrently in batches, a partial SQL execution failed in the log, and an error was reported: the Deadlock was found when trying to get lock. If the try restarting transaction is restarted, a deadlock occurs.

Cause of deadlock

  • Mysql InnoDB engine supports transactions and locks at row level. But this row-level lock is indexed.
  • Row-level locking does not directly lock records, but locks indexes. If a SQL statement uses a primary key index, mysql will lock the primary key index. If a statement operates on a non-primary key index, mysql will first lock the non-primary key index and then lock the primary key index. If the operation uses the primary key index, the primary key index will be locked first, and then the other indexes will be locked, otherwise the order is reversed.
  • Table-level locking is used for operations that do not use indexes

Table structure

CREATE TABLE `user_video` (
  `id` varchar(64) NOT NULL DEFAULT ' ' COMMENT 'primary key',
  `user_id` varchar(40) NOT NULL DEFAULT ' ' COMMENT 'user id',
  `video_id` varchar(255) NOT NULL DEFAULT ' ' COMMENT 'video id',
  `title` bit(1) NOT NULL DEFAULT b'0'
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `modify_time` datetime DEFAULT NULL COMMENT 'Modification time'.PRIMARY KEY (`id`),
  KEY `user_video_user_id_IDX` (`user_id`) USING BTREE,
  KEY `user_video_video_id_IDX` (`video_id`) USING BTREE,
  KEY `user_video_create_time_IDX` (`create_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

Analysis of the

SQL statement to create deadlock: update user_video set title=’ haha ‘where user_id=? and video_id=?

Because you are using a non-primary key index, you first need to obtain the row-level lock on user_video_video_id_IDX and then update it based on the primary key, so you need to obtain the row-level lock on the primary key; When the update is complete, commit and release all locks. User_id and video_id are indexed, mysql will lock the rows of user_id and video_id, if the same video_id comes in, different user_id will be locked.

simulating

When executing in code, an error message will appear: The Deadlock found when trying to get lock; If the SQL process is restarted, a lock wait occurs.

SQL simulation
# sql1
start TRANSACTION;
UPDATE  user_video   set  modify_time=NOW() 
where  video_id='1'
and  user_id='1005';
select sleep(10);
UPDATE  user_video   set modify_time=NOW() 
where  video_id='1'
and  user_id='1004';
COMMIT;

# sql2
start TRANSACTION;
UPDATE  user_video   set modify_time=NOW() 
where  video_id='1'
and  user_id='1004';
select sleep(5);
UPDATE  user_video  set modify_time=NOW() 
where  video_id='1'
and  user_id='1005';
COMMIT

# sql3
show status like '%lock%';
Copy the code
The phenomenon of

Open three Windows in Navicat and execute sqL1, SQL2, and SQL3 respectively. The number of Innodb_row_lock_current_waits increases by 1. The number of Innodb_row_lock_current_waits increases by 1.

conclusion

Because we’re using a non-primary key index, we need to get the row-level lock on video_id and then update it based on the primary key, so we need to get the row-level lock on the primary key; When the update is complete, commit and release all locks.

The solution

Based on service requirements, ensure that the primary key index is obtained based on other fields, and the primary key index is used to update data.