记一次线上数据库死锁定位

在开发过程中，操作数据库总会涉及到批量操作。有些批量操作可以利用multiquery更新数据库，但有些不可，例如对于同一张表不同字段的多行更新。我们经常会把这种操作放到一个事务里面，由于都是按照主键更新，所以性能上不会有大问题，例如这样：

@Transactional
    public void update(List<DAO> daoList)
    {
        for (DAO dao : daoList) {
            daoMapper.updateByPrimaryKeySelective(dao);
        }

    }

但是如果不注意的话，会出现数据库死锁，利用MySql自带的innodb引擎监控日志（show engine innodb status）,看到最近的死锁：

2017-09-28 00:19:24 0x7f70134b1700
*** (1) TRANSACTION:
TRANSACTION 205776344, ACTIVE 71 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 85
MySQL thread id 2552, OS thread handle 140118527325952, query id 17080031 xxx.xx.xx.xxx sql
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15 page no 135560 n bits 200 index PRIMARY of table `db`.`table` trx id 205776344 lock_mode X locks rec but not gap waiting
Record lock, heap no 67 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 23; hex 3137303932383030313032315354463132333833373937; asc 170928001021STF12383797;;
 1: len 6; hex 00000c43e3fa; asc    C  ;;
 2: len 7; hex 30000008370881; asc 0   7  ;;


*** (2) TRANSACTION:
TRANSACTION 205775866, ACTIVE 129 sec starting index read
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1136, 93 row lock(s), undo log entries 149
MySQL thread id 2544, OS thread handle 140119336752896, query id 17080690 xxx.xx.xx.xxx sql
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 15 page no 135560 n bits 200 index PRIMARY of table `afbet`.`t_facts_market_stateful_messages` trx id 205775866 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 23; hex 3137303932383030303930395354463132333833303738; asc 170928000909STF12383078;;
 1: len 6; hex 00000c43e3fa; asc    C  ;;


Record lock, heap no 9 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 23; hex 3137303932383030303931305354463132333833303837; asc 170928000910STF12383087;;
 1: len 6; hex 00000c43e3fa; asc    C  ;;


Record lock, heap no 12 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 23; hex 3137303932383030303931345354463132333833313037; asc 170928000914STF12383107;;
 1: len 6; hex 00000c43e3fa; asc    C  ;;
 2: len 7; hex 30000008371103; asc 0   7  ;;


Record lock, heap no 14 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 23; hex 3137303932383030303931345354463132333833313336; asc 170928000914STF12383136;;
 1: len 6; hex 00000c43e3fa; asc    C  ;;
 2: len 7; hex 30000008370ad3; asc 0   7  ;;


Record lock, heap no 15 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 23; hex 3137303932383030303931365354463132333833313332; asc 170928000916STF12383132;;
 1: len 6; hex 00000c43e3fa; asc    C  ;;
 2: len 7; hex 3000000837103d; asc 0   7 =;;


Record lock, heap no 16 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 23; hex 3137303932383030303931385354463132333833313934; asc 170928000918STF12383194;;
 1: len 6; hex 00000c43e3fa; asc    C  ;;
 2: len 7; hex 300000083708e4; asc 0   7  ;;

Record lock, heap no 17 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 23; hex 3137303932383030303932305354463132333833323135; asc 170928000920STF12383215;;
 1: len 6; hex 00000c43e3fa; asc    C  ;;
 2: len 7; hex 300000083714e1; asc 0   7  ;;


Record lock, heap no 18 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 23; hex 3137303932383030303932305354463132333833323137; asc 170928000920STF12383217;;
 1: len 6; hex 00000c43e3fa; asc    C  ;;
 2: len 7; hex 3000000837141b; asc 0   7  ;;


Record lock, heap no 19 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 23; hex 3137303932383030303932305354463132333833323138; asc 170928000920STF12383218;;
 1: len 6; hex 00000c43e3fa; asc    C  ;;
 2: len 7; hex 30000008370deb; asc 0   7  ;;


Record lock, heap no 21 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 23; hex 3137303932383030303932345354463132333833323733; asc 170928000924STF12383273;;
 1: len 6; hex 00000c43e3fa; asc    C  ;;
 2: len 7; hex 30000008370a0d; asc 0   7  ;;




Record lock, heap no 67 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 23; hex 3137303932383030313032315354463132333833373937; asc 170928001021STF12383797;;
 1: len 6; hex 00000c43e3fa; asc    C  ;;
 2: len 7; hex 30000008370881; asc 0   7  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15 page no 135559 n bits 112 index PRIMARY of table `db`.`table` trx id 205775866 lock_mode X locks rec but not gap waiting
Record lock, heap no 39 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 23; hex 3137303932383030303834395354463132333833303332; asc 170928000849STF12383032;;
 1: len 6; hex 00000c43e5d8; asc    C  ;;
 2: len 7; hex 2a0000080726b6; asc *    & ;;


*** WE ROLL BACK TRANSACTION (1)

发现有两个Transaction发生死锁

原因：

批量更新，并且每个dao可以多线程更新（在不同事务内同时请求更新），而且没有按照一定顺序。例如事务1更新id为1，2，3的数据，事务2更新id为3，2，1的数据，这样就会发生死锁

修改方案：

按顺序更新，例如把入参改为TreeSet，并且dao对象实现Comparable接口