Problem Description: a deadlock problem suddenly occurs during the peak period of online projects! When the user operation gets stuck, the deadlock is thrown directly in the background

Viewing the log, it is found that deadlock loserdataaccessexception (Deadlock found when trying to get lock; try restarting transaction...) occurs when updating the table.

Problem analysis:

Viewing the log is an exception thrown when updating data (therefore, the lock waiting timeout rollback caused by lock competition is considered), because there are many maintenance personnel in the old version. Looking at the sql statement, it is found that there is no index, and the transaction isolation level is rr. So far, the problem has been solved (in the rr isolation level, the locking mechanism is not indexed, and the netkey mechanism is used to lock the whole table (exclusive lock), and only one row will be locked in the rc isolation level)

Solution: add an index to the sql statement or set the transaction isolation level to rc (it is recommended to set it to rc. Although unreal reads and non repeatable reads may occur, we can avoid or manually lock in business)

Later: it has always been said that if the update statement does not go through the index, the whole table is locked. Why does the blogger say that it is a lock? In RC mode, the lock is a row of records. The blogger is also very strange. He occasionally read such a sentence (there will be no gap lock (to prevent phantom reading) without ensuring the consistency of data read many times at RC and RU levels, and row lock upgrades table lock)

Look at the measured handle

Create a student table

#Create student table
CREATE TABLE `student` (
  `s_id` varchar(20) NOT NULL DEFAULT '',
  `s_name` varchar(20) NOT NULL DEFAULT '',
  `s_birth` varchar(20) NOT NULL DEFAULT '',
  `s_sex` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#Add sql statement
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('01', 'Lei Zhao', '1990-01-01', 'male');
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('02', 'Qian Dian', '1990-12-21', 'male');
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('03', 'Sun Feng', '1990-05-20', 'male');
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('04', 'Li Yun', '1990-08-06', 'male');
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('05', 'Zhou Mei', '1991-12-01', 'female');
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('06', 'Wu Lan', '1992-03-01', 'female');
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('07', 'Zheng Zhu', '1989-07-01', 'female');
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('08', 'Ju Wang', '1990-01-20', 'female');

Set database isolation level

set session transaction isolation level repeatable  read;

Execute open transaction to update table records and view locked table records

set session transaction isolation level read committed;
update student set s_name = 'Li shisan'  where s_name ="Ju Wang"

select * from information_schema.innodb_trx

innodb_trx view the status of the transaction lock, the isolation level of the transaction, the weight and the table record where the transaction resides. As shown in the figure below, only one row of records is locked

View rr update statements with no index set under isolation level

#Set isolation level
set session transaction isolation level repeatable  read;
Open transaction
start TRANSACTION
select * from student 

update student set s_name = 'Li shisan'  where s_name ="Ju Wang"
#View transaction lock status
 select * from information_schema.innodb_trx
ROLLBACK
COMMIT

As shown in the figure below, the whole watch is locked