记一次线上数据库死锁

        最近，线上订单库老出现报错，通过elk看是是死锁导致数据库回滚了，哲哥那拿来的数据库死锁日志如下：

*** (1) TRANSACTION:
TRANSACTION 728240667, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 135270358, OS thread handle 140267696391936, query id 2140927486 192.168.1.250 clx updating
update order_contract set goods_required_amount= goods_required_amount-49 where contract_no = ''
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1584 page no 53 n bits 168 index idx_orderContract_contractNo of table `order_service`.`order_contract` trx id 728240667 lock_mode X waiting
Record lock, heap no 27 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 15; hex 585947533230323130313233303031; asc XYGS20210123001;;
 1: len 4; hex 800019a9; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 728240665, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
1314 lock struct(s), heap size 139472, 6469 row lock(s), undo log entries 3230
MySQL thread id 135268737, OS thread handle 140268200789760, query id 2140927480 192.168.1.225 clx updating
update order_info set goods_residue=goods_residue-49.0,modified_time=SYSDATE() where order_no<>'' and contract_no=''
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1584 page no 53 n bits 168 index idx_orderContract_contractNo of table `order_service`.`order_contract` trx id 728240665 lock_mode X
Record lock, heap no 27 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 15; hex 585947533230323130313233303031; asc XYGS20210123001;;
 1: len 4; hex 800019a9; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1583 page no 10958 n bits 96 index PRIMARY of table `order_service`.`order_info` trx id 728240665 lock_mode X locks rec but not gap waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 109; compact format; info bits 0
0: len 4; hex 800368f8; asc   h ;;
 1: len 6; hex 00002b68121b; asc   +h  ;;
 2: len 7; hex 4a000080361df1; asc J   6  ;;
 3: len 4; hex 8000137f; asc     ;;
 4: len 4; hex 80000659; asc    Y;;
 5: len 1; hex 82; asc  ;;
 6: len 1; hex 81; asc  ;;
 7: len 1; hex 82; asc  ;;
 8: len 6; hex e7a59ee8bebe; asc       ;;
 9: len 12; hex e7a5a5e5ae87e585ace58fb8; asc             ;;
(以下省略...)

*** WE ROLL BACK TRANSACTION (1)

        定位到相应的代码位置后，发生死锁时的代码如下：

if (flag.abs().compareTo(new BigDecimal(0)) == 1) {
        
            int order = orderInfoDao.updateXXX(XXX, XXX, 0, XXX);
            if (!StringUtils.isEmpty(CCC)) {
                OrderContractDTO orderContractDTO = AAA;
                if (orderContractDTO == null) {
                    throw new Exception();
                } else {
                    if (BBB == 1) {
                        orderInfoDao.updateYYY(YYY, YYY, YYY);
                        orderContractDao.updateYYY(YYY, flag);
                    }
                }
            }
    }
 /** 费用调整 end*/
 int updateId = orderChildDao.updateZZZ(ZZZ);

          这是一个装车接口，和同事一起分析了下这个代码，orderInfoDao和orderInfoDao顺序本来应该是这样的，但是在别的方法有一个顺序是反过来的，两个方法一起执行就出问题了

        后头的解决方法是修改代码，修改orderInfoDao和orderInfoDao的执行顺序应该就可以了。

        另外，下面id为1、2这两行是事务ID和回滚指针

 0: len 4; hex 800368f8; asc   h ;;
 1: len 6; hex 00002b68121b; asc   +h  ;;
 2: len 7; hex 4a000080361df1; asc J   6  ;;

对于Innodb表中的行每一行包括：

6字节的事务ID(DB_TRX_ID)字段: 用来标识最近一次对本行记录做修改(INSERT|UPDATE)的事务的标识符, 即最后一次修改(INSERT|UPDATE)本行记录的事务id。

7字节的回滚指针(DB_ROLL_PTR)字段: 指写入回滚段(ROLLBACK segment)的 UNDO LOG record (撤销日志记录记录)。

如果一行记录被更新, 则 UNDO LOG record 包含 '重建该行记录被更新之前内容' 所必须的信息。