背景:线上项目报数据库死锁
ERROR 2020-03-20 12:05:00,151
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may involve
### The error occurred while setting parameters
### SQL: update action_schedule set Status=?, Rerun=?, Begin_Time=?, End_Time=now(), Execute_Times=?, Priority=?, First_Begin_Time=?,First_Success_Time=?,Elapsed_Time=?, External_ID=? where Action_SID=?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
代码是:任务成功后,更新数据库中的任务状态以及一些运行时间。
排查:查看mysql的错误日志,
*** (1) TRANSACTION:
TRANSACTION 99992427, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 16546820, OS thread handle 0x7f862de67700, query id 14073772905 10.0.40.21 bigdata updating
update action_schedule set Status=4, Rerun=0,
Begin_Time='2020-03-20 12:04:02', End_Time=now(), Execute_Times=1, Priority=7,
First_Begin_Time='2020-03-20 12:04:02',First_Success_Time='2020-03-20 12:05:00.027',Elapsed_Time=0, External_ID='9252c815-5373-4642-afcb-06bd7188584f'
where Action_SID=9496600
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1020 page no 4434 n bits 272 index `PRIMARY` of table `taoist`.`action_schedule` trx id 99992427 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 24; compact format; info bits 0
0: len 8; hex 800000000090e818; asc ;;
1: len 6; hex 000005f5c070; asc p;;
2: len 7; hex 0b0001000904a2; asc ;;
3: len 4; hex 80a821c8; asc ! ;;
4: len 4; hex 5e743fc0; asc ^t? ;;
5: len 2; hex 8003; asc ;;
6: len 1; hex 80; asc ;;
7: len 4; hex 5e743fc1; asc ^t? ;;
8: len 4; hex 5e7440b2; asc ^t@ ;;
9: SQL NULL;
10: len 4; hex 5e7440b2; asc ^t@ ;;
11: SQL NULL;
12: len 4; hex 00000000; asc ;;
13: len 4; hex 80000001; asc ;;
14: len 1; hex 07; asc ;;
15: len 5; hex 99a5e8b000; asc ;;
16: len 30; hex 39323532633831352d353337332d343634322d616663622d303662643731; asc 9252c815-5373-4642-afcb-06bd71; (total 36 bytes);
17: len 0; hex ; asc ;;
18: len 4; hex 80000000; asc ;;
19: len 1; hex 80; asc ;;
20: len 4; hex 5e743fc0; asc ^t? ;;
21: len 4; hex 5e7440b1; asc ^t@ ;;
22: len 1; hex 81; asc ;;
23: len 1; hex 80; asc ;;
*** (2) TRANSACTION:
TRANSACTION 99992423, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
88 lock struct(s), heap size 13864, 12005 row lock(s), undo log entries 1
MySQL thread id 16544678, OS thread handle 0x7f862be7c700, query id 14073772855 10.0.40.21 bigdata Sending data
update action set last_runing_status=
(SELECT status
FROM action_schedule
WHERE Action_ID = 11018738
ORDER BY Nominal_Time DESC
LIMIT 1)
WHERE action_id = 11018738
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1020 page no 4434 n bits 272 index `PRIMARY` of table `taoist`.`action_schedule` trx id 99992423 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 24; compact format; info bits 0
0: len 8; hex 800000000090e815; asc ;;
1: len 6; hex 000005f5baf0; asc ;;
2: len 7; hex 780000c0ab1b7d; asc x };;
可以看到于sql:
update action set last_runing_status=
(SELECT status
FROM action_schedule
WHERE Action_ID = 11018738
ORDER BY Nominal_Time DESC
LIMIT 1)
WHERE action_id = 11018738
相互锁住,但是action_id和上述的action_sid不在同一行。
死锁原因:事务隔离级别设置不对。
需要改成 read-commit模式。
这特么也能锁住。。。。。