mysql-死锁

背景：线上项目报数据库死锁

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模式。
这特么也能锁住。。。。。