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

在开发过程中，操作数据库总会涉及到批量操作。有些批量操作可以利用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 readmysql tables in use 1, locked 1LOCK WAIT 4 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 85MySQL 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 waitingRecord 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 readmysql tables in use 1, locked 17 lock struct(s), heap size 1136, 93 row lock(s), undo log entries 149MySQL 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 gapRecord 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 waitingRecord 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接口