记一次死锁解决

nathan0529

已于 2023-03-16 15:38:58 修改

阅读量634

点赞数

文章标签：数据库 mysql sql

于 2023-03-14 10:25:27 首次发布

本文链接：https://blog.csdn.net/wnsh1990/article/details/129517315

版权

场景

测试反馈环境卡死，查看日志

### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in com/xxxMapper.java (best guess)
### The error may involve com.xxxnMapper.delete-Inline
### The error occurred while setting parameters
### SQL: UPDATE t_user_xxx_2 SET is_deleted=1   WHERE  is_deleted=0  AND (biz_id = ?)
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:271)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)

问题分析

show engine innodb status; 来查看死锁的情况：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-01-31 16:33:49 140205454694144
*** (1) TRANSACTION:
TRANSACTION 496155, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 3
MySQL thread id 144572, OS thread handle 140204594546432, query id 5420551 Server-f6b12fd5-986d-42f0-9e7b-1a3c1649c4bf.novalocal 172.29.67.200 root updating
UPDATE t_user_A SET is_deleted=1 
 WHERE  is_deleted=0

AND (biz_id = 1089)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 2570 page no 41 n bits 88 index idx_person_task_id of table `db`.`t_task_B` trx id 496155 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;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2590 page no 5 n bits 384 index PRIMARY of table `db`.`t_user_A` trx id 496155 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000032df6; asc     - ;;
 2: len 7; hex 81000000e50110; asc        ;;
 3: len 4; hex 656c7375; asc elsu;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: len 4; hex 80000001; asc     ;;
 6: len 1; hex 81; asc  ;;


*** (2) TRANSACTION:
TRANSACTION 496144, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 20 lock struct(s), heap size 3488, 3919 row lock(s), undo log entries 5
MySQL thread id 144569, OS thread handle 140201527457536, query id 5420691 Server-f6b12fd5-986d-42f0-9e7b-1a3c1649c4bf.novalocal 172.29.67.200 root update
INSERT INTO t_A  ( 
is_deleted,
create_by,
update_by,
create_time,
update_time )  VALUES  (
0,
'll',
null,
'2023-01-31 16:33:49.959',
'2023-01-31 16:33:49.959' )

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2590 page no 5 n bits 384 index PRIMARY of table `db`.`t_user_A` trx id 496144 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;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000032df6; asc     - ;;
 2: len 7; hex 81000000e50110; asc        ;;
 3: len 4; hex 656c7375; asc elsu;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: len 4; hex 80000001; asc     ;;
 6: len 1; hex 81; asc  ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;
 1: len 6; hex 000000032df6; asc     - ;;
 2: len 7; hex 81000000e50121; asc       !;;
 3: len 4; hex 656c7375; asc elsu;;
 4: len 8; hex 8000000000000002; asc         ;;
 5: len 4; hex 80000000; asc     ;;
 6: len 1; hex 80; asc  ;;



*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2570 page no 41 n bits 88 index idx_person_task_id of table `db`.`t_B` trx id 496144 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 (1)
------------
TRANSACTIONS
------------
Trx id counter 572685
Purge done for trx's n:o < 572685 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421680852460328, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421680852459520, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421680852468408, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421680852467600, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421680852466792, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421680852464368, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421680852462752, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421680852475680, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421680852457904, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421680852457096, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 18446744073709551614
6559 OS file reads, 2893393 O

通过mysql日志时间查阅java代码日志找出死锁相关操作

模拟死锁

建表

CREATE TABLE `t_B` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `task_examine_id` bigint DEFAULT NULL COMMENT '任务id',
  `account` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '参与人account',
  `join_type` bigint DEFAULT NULL COMMENT '参会人类型;1.主评人,2.评委,3.报告人,4.组织者,5.BGBU组织者,6.集团组织者,7.发起人(即提交人),8.特邀评委,9.参评人员',
  `is_attend` tinyint DEFAULT '1' COMMENT '是否参会 1参加  0不参加',
  `suggestion` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `is_handle` tinyint DEFAULT '0' COMMENT '记录问题环节操作动作，0-未操作，1-通过，2-不通过，3-系统自动提交',
  `create_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
  `update_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint DEFAULT '0' COMMENT '1:删除 0:不删除',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_person_task_id` (`task_examine_id`) USING BTREE,
  KEY `idx_person_account` (`account`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4896 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='参与人表';



CREATE TABLE `t_A` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `account` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户表account',
  `biz_id` bigint NOT NULL COMMENT '业务id',
  `active_status` int DEFAULT '0' COMMENT '0.未激活;1.激活流程',
  `is_deleted` tinyint DEFAULT '0' COMMENT '1:删除 0:不删除',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_biz_account` (`biz_id`,`account`) USING BTREE COMMENT '业务数据id',
  KEY `idx_account` (`account`) USING BTREE COMMENT 'account数据索引'
) ENGINE=InnoDB AUTO_INCREMENT=3325 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户数据权限表';

事物1

select @@autocommit;
set autocommit=0;
select @@autocommit;
 ROLLBACK;
 
 --  tx1   01
delete FROM t_B where task_examine_id = 1087 and join_type not in ( 8 );
select * from t_B where task_examine_id = 1087 and join_type in ( 8 );

UPDATE t_A SET is_deleted=1 WHERE is_deleted=0 AND (biz_id = 1087);
SELECT COUNT( * ) FROM t_A WHERE is_deleted=0 AND (biz_id = 1087 AND account = 'qcliu' AND is_deleted = 'N');

INSERT INTO t_A ( account, biz_id ) VALUES ( 'qcliu', 1087 );

SELECT COUNT( * ) FROM t_A WHERE is_deleted=0 AND (biz_id = 1087 AND account = 'qcliu' AND is_deleted = 'N');

INSERT INTO t_B ( task_examine_id, account, join_type, is_attend, is_deleted, create_by, update_by, create_time, 
              update_time ) VALUES ( 1087, 'qcliu', 1, 1, 0, 'elsu', null, '2023-01-31 16:33:49.724', '2023-01-31 16:33:49.724' );
SELECT COUNT( * ) FROM t_A WHERE is_deleted=0 AND (biz_id = 1087 AND account = 'feihe' AND is_deleted = 'N');			

INSERT INTO t_A ( account, biz_id ) VALUES ( 'feihe', 1087 );

INSERT INTO t_B ( task_examine_id, account, join_type, is_attend, is_deleted, create_by, update_by, 
create_time, update_time ) VALUES ( 1087, 'feihe', 2, 1, 0, 'elsu', null, '2023-01-31 16:33:49.73', '2023-01-31 16:33:49.73' );

SELECT COUNT( * ) FROM t_A WHERE is_deleted=0 AND (biz_id = 1087 AND account = 'xuanzhang' AND is_deleted = 'N');

INSERT INTO t_A ( account, biz_id ) VALUES ( 'xuanzhang', 1087 );

INSERT INTO t_B ( task_examine_id, account, join_type, is_attend, is_deleted, create_by, update_by, create_time, 
update_time ) VALUES ( 1087, 'xuanzhang', 3, 1, 0, 'elsu', null, '2023-01-31 16:33:49.736', '2023-01-31 16:33:49.736' );

SELECT COUNT( * ) FROM t_A WHERE is_deleted=0 AND (biz_id = 1087 AND account = 'elsu' AND is_deleted = 'N');

INSERT INTO t_A ( account, biz_id ) VALUES ( 'elsu', 1087 );

SELECT COUNT( * ) FROM t_A WHERE is_deleted=0 AND (biz_id = 1087 AND account = 'elsu' AND is_deleted = 'N');

INSERT INTO t_B ( task_examine_id, account, join_type, is_attend, is_deleted, create_by, update_by,
 create_time, update_time ) VALUES ( 1087, 'elsu', 4, 1, 0, 'elsu', null, '2023-01-31 16:33:49.745', '2023-01-31 16:33:49.745' );
 
 INSERT INTO t_B ( task_examine_id, account, join_type, is_attend, is_deleted, create_by, update_by, 
create_time, update_time ) VALUES ( 1087, 'elsu', 7, 1, 0, 'elsu', null, '2023-01-31 16:33:49.75', '2023-01-31 16:33:49.75' );


-- tx1 04
update t_A set active_status = 1 where biz_id=1087 and account in ( 'elsu' );

事物2

select @@autocommit;
set autocommit=0;
select @@autocommit;

 ROLLBACK;
 -- tx2 02
delete FROM t_B where task_examine_id = 1088 and join_type not in ( 8 );

select * from t_B where task_examine_id = 1088 and join_type in ( 8 );
				
-- tx2 05
UPDATE t_A SET is_deleted=1 WHERE is_deleted=0 AND (biz_id = 1088);
SELECT COUNT( * ) FROM t_A WHERE is_deleted=0 AND (biz_id = 1088 AND account = 'qcliu' AND is_deleted = 'N');	
-- 这里  Lock wait timeout exceeded; try restarting transaction
INSERT INTO t_A ( account, biz_id ) VALUES ( 'qcliu', 1088 );

SELECT COUNT( * ) FROM t_A WHERE is_deleted=0 AND (biz_id = 1088 AND account = 'qcliu' AND is_deleted = 'N');

INSERT INTO t_B ( task_examine_id, account, join_type, is_attend, is_deleted, create_by, update_by, 
create_time, update_time ) VALUES ( 1088, 'qcliu', 1, 1, 0, 'elsu', null, '2023-01-31 16:33:49.959', '2023-01-31 16:33:49.959' );


 SELECT * FROM t_B WHERE task_examine_id = 3;
SELECT * FROM t_A WHERE biz_id=3;
 ROLLBACK;

分析

tx1执行UPDATE t_A SET is_deleted=1 WHERE is_deleted=0 AND (biz_id = 1087);时 t_A 表中无记录此时是间隙锁

tx2执行UPDATE t_A SET is_deleted=1 WHERE is_deleted=0 AND (biz_id = 1088);间隙锁

间隙锁与间隙锁非互斥

tx2执行INSERT INTO t_A ( account, biz_id ) VALUES ( 'qcliu', 1088 );插入意象锁此时与tx1点间隙锁互斥

问题解决

java代码中对此代码在先查询t_A是否有记录，有记录执行更新操作，更新操作根据查询出的记录id进行更新

查阅资料

彻底搞懂MySQL死锁_mysql deadlock_AlbenXie的博客-CSDN博客

解决死锁之路（终结篇）- 再见死锁 - 腾讯云开发者社区-腾讯云

MySQL——锁（全面总结） - 腾讯云开发者社区-腾讯云

nathan0529

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
记一次死锁解决

tx1执行UPDATE t_A SET is_deleted=1 WHERE is_deleted=0 AND (biz_id = 1087);时 t_A 表中无记录此时是间隙锁。tx2执行UPDATE t_A SET is_deleted=1 WHERE is_deleted=0 AND (biz_id = 1088);java代码中对此代码在先查询t_A是否有记录，有记录执行更新操作，更新操作根据查询出的记录id进行更新。通过mysql日志时间查阅java代码日志找出死锁相关操作。
复制链接

扫一扫