1,应用服务死锁日志如下：
2023-12-06 02:30:00.845 [http-nio-18114-exec-94] [8qhhiv1vihh4e26] [ERROR] PayoutService [918] -
updatePayoutFlow exception id=20231206022954BEDF4646612338,
msg=org.springframework.dao.DeadlockLoserDataAccessException: jOOQ;
SQL [update `kirinpay`.`payout_flow` set `kirinpay`.`payout_flow`.`status` = ?, `kirinpay`.`payout_flow`.`comment` = ?, `kirinpay`.`payout_flow`.`callback_time` = ?
where `kirinpay`.`payout_flow`.`rid` = ?];
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
......
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
2. 数据库日志：mysql> SHOW ENGINE INNODB STATUS;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-12-06 02:30:00 22918058956544
*** (1) TRANSACTION:
TRANSACTION 304393045699752, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 28 lock struct(s), heap size 3520, 3074 row lock(s)
MySQL thread id 1826287, OS thread handle 22917195900672, query id 9370235050 event_scheduler executing
INSERT INTO payout_minute_stat (mytime, account_id, total_order_count, success_order_count)
SELECT
NAME_CONST('my_time',_latin1'2023-12-06 02:30:00' COLLATE 'latin1_swedish_ci') AS mytime,
accountid,
COUNT(1) AS total_order_count,
COUNT(CASE WHEN status = 'SUCCESS' THEN 1 END) AS success_order_count
FROM
payout_flow
WHERE
create_time > DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY
accountid
ON DUPLICATE KEY UPDATE
total_order_count = VALUES(total_order_count),
success_order_count = VALUES(success_order_count)
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 102 page no 517879 n bits 168 index ix_time_query of table `kirinpay`.`payout_flow` trx id 304393045699752 lock mode S waiting
Record lock, heap no 96 PHYSICAL RECORD: n_fields 11; compact format; info bits 32
0: len 5; hex 99b1cc2776; asc 'v;;
1: len 24; hex 363438376436626635613861633033313439373932653635; asc 6487d6bf5a8ac03149792e65;;
2: len 0; hex ; asc ;;
3: len 9; hex 626574636174706179; asc betcatpay;;
4: len 25; hex 7061796f75745f6265746361747061795f3637393830313133; asc payout_betcatpay_67980113;;
5: len 3; hex 435046; asc CPF;;
6: SQL NULL;
7: len 1; hex 02; asc ;;
8: len 25; hex 39353032303836343036323136323140676d61696c2e636f6d; asc 950208640621621@gmail.com;;
9: len 11; hex 3437313731343330303238; asc 47171430028;;
10: len 4; hex 80f334ed; asc 4 ;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 102 page no 517879 n bits 168 index ix_time_query of table `kirinpay`.`payout_flow` trx id 304393045699752 lock mode S waiting
Record lock, heap no 96 PHYSICAL RECORD: n_fields 11; compact format; info bits 32
0: len 5; hex 99b1cc2776; asc 'v;;
1: len 24; hex 363438376436626635613861633033313439373932653635; asc 6487d6bf5a8ac03149792e65;;
2: len 0; hex ; asc ;;
3: len 9; hex 626574636174706179; asc betcatpay;;
4: len 25; hex 7061796f75745f6265746361747061795f3637393830313133; asc payout_betcatpay_67980113;;
5: len 3; hex 435046; asc CPF;;
6: SQL NULL;
7: len 1; hex 02; asc ;;
8: len 25; hex 39353032303836343036323136323140676d61696c2e636f6d; asc 950208640621621@gmail.com;;
9: len 11; hex 3437313731343330303238; asc 47171430028;;
10: len 4; hex 80f334ed; asc 4 ;;
*** (2) TRANSACTION:
TRANSACTION 1800312561, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 1826171, OS thread handle 22917199513344, query id 9370235062 ip-172-31-35-61.sa-east-1.compute.internal 172.31.35.61 kirinpay updating
update `kirinpay`.`payout_flow` set `kirinpay`.`payout_flow`.`status` = 'SUCCESS', `kirinpay`.`payout_flow`.`comment` = '', `kirinpay`.`payout_flow`.`callback_time` = '2023-12-06 02:30:00.779' where `kirinpay`.`payout_flow`.`rid` = 15938797
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 102 page no 517879 n bits 168 index ix_time_query of table `kirinpay`.`payout_flow` trx id 1800312561 lock_mode X locks rec but not gap
Record lock, heap no 96 PHYSICAL RECORD: n_fields 11; compact format; info bits 32
0: len 5; hex 99b1cc2776; asc 'v;;
1: len 24; hex 363438376436626635613861633033313439373932653635; asc 6487d6bf5a8ac03149792e65;;
2: len 0; hex ; asc ;;
3: len 9; hex 626574636174706179; asc betcatpay;;
4: len 25; hex 7061796f75745f6265746361747061795f3637393830313133; asc payout_betcatpay_67980113;;
5: len 3; hex 435046; asc CPF;;
6: SQL NULL;
7: len 1; hex 02; asc ;;
8: len 25; hex 39353032303836343036323136323140676d61696c2e636f6d; asc 950208640621621@gmail.com;;
9: len 11; hex 3437313731343330303238; asc 47171430028;;
10: len 4; hex 80f334ed; asc 4 ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 102 page no 517879 n bits 168 index ix_time_query of table `kirinpay`.`payout_flow` trx id 1800312561 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 96 PHYSICAL RECORD: n_fields 11; compact format; info bits 32
0: len 5; hex 99b1cc2776; asc 'v;;
1: len 24; hex 363438376436626635613861633033313439373932653635; asc 6487d6bf5a8ac03149792e65;;
2: len 0; hex ; asc ;;
3: len 9; hex 626574636174706179; asc betcatpay;;
4: len 25; hex 7061796f75745f6265746361747061795f3637393830313133; asc payout_betcatpay_67980113;;
5: len 3; hex 435046; asc CPF;;
6: SQL NULL;
7: len 1; hex 02; asc ;;
8: len 25; hex 39353032303836343036323136323140676d61696c2e636f6d; asc 950208640621621@gmail.com;;
9: len 11; hex 3437313731343330303238; asc 47171430028;;
10: len 4; hex 80f334ed; asc 4 ;;
*** WE ROLL BACK TRANSACTION (2)上面的日志根据chatgpt解释：
根据提供的信息，发生了一个死锁情况。在这个例子中，有两个事务（事务ID为304393045699752和1800312561）相互等待对方持有的锁。
事务1正在执行一个INSERT语句，涉及到表payout_minute_stat和表payout_flow。 它持有表payout_flow中索引为ix_time_query的记录的共享锁（S锁）等待事务2释放持有的排他锁（X锁）。
事务2正在执行一个UPDATE语句，更新表payout_flow中rid为15938797的记录。 它持有表payout_flow中索引为ix_time_query的记录的排他锁（X锁），同时等待事务1释放持有的共享锁（S锁）。
这两个事务之间的循环等待导致了死锁。作为解决死锁的机制，MySQL会选择其中一个事务进行回滚，以允许另一个事务继续执行。 在这种情况下，事务2被选择进行回滚，以解除死锁。
为了解决这个问题，你可以考虑调整事务逻辑，以减少资源竞争。一个可能的解决方法是重新设计事务，使得不同的事务在访问相同的记录时具有一致的顺序。另一种方法是调整事务的隔离级别，以减少死锁的可能性。
3.解决方案
将插入payout_minute_stat的存储过程，修改为查询备库，插入主库表payout_minute_stat, 减少资源竞争冲突
