mysql 一个死锁的分析

mwjhw216

　　死锁信息如下：
　　*** (1) TRANSACTION:
　　TRANSACTION 4363766192, ACTIVE 0 sec
　　mysql tables in use 2, locked 2

　　LOCK WAIT 9 lock struct(s), heap>
　　MySQL thread>　　update keap_cash_coup_type a,(select sum(freezed_amount) freezedAmount,cash_coupon_type_id from keap_cash_transcation where transcation_id = 10000001415322882 group by cash_coupon_type_id)b set a.amount = a.amount-b.freezedAmount,a.locked_amount=a.locked_amount+b.freezedAmount where a.cash_coupon_type_id=b.cash_coupon_type_id
　　*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

　　RECORD LOCKS space>　　*** (2) TRANSACTION:
　　TRANSACTION 4363766191, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 4999
　　mysql tables in use 2, locked 2

　　9 lock struct(s), heap>
　　MySQL thread>　　update keap_cash_coup_type a,(select sum(freezed_amount) freezedAmount,cash_coupon_type_id from keap_cash_transcation where transcation_id = 10000001415322879 group by cash_coupon_type_id)b set a.amount = a.amount-b.freezedAmount,a.locked_amount=a.locked_amount+b.freezedAmount where a.cash_coupon_type_id=b.cash_coupon_type_id
　　*** (2) HOLDS THE LOCK(S):

　　RECORD LOCKS space>　　*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

　　RECORD LOCKS space>　　Record lock, heap no 103 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
　　信息显示两个多表链接update的事务，事务一在等待表keap_cash_transcation表主键索引的S锁，位置在第3页的176字节处，事务二拿到了对应位置的锁，而又在等待该位置S锁，这种锁等待看起来有点奇怪，明明已经拿到该位置的X锁为什么还要去获取S锁，都知道mysql在对唯一索引做update和insert时是会先获取S锁再获取X锁，这感觉有点像，一步一步排查分析吧
　　首先查询隔离级别好判断加锁粒度：
　　mysql> show global variables like "%iso%";
　　+---------------+----------------+
　　| Variable_name | Value          |
　　+---------------+----------------+
　　| tx_isolation  | READ-COMMITTED |
　　+---------------+----------------+
　　1 row in set (0.00 sec)
　　是RC提交读隔离级别，知道了无gap锁，只有针对行加锁的情况，再仔细看看两个事务的sql发现等待锁的表keap_cash_transcation只是作为关联条件并未更新字段，查看表结构都只有主键，transcation_id无索引，建两个只有主键的零时表进行测试：
　　结构：
　　CREATE TABLE `t1`/`t2` (
　　`id` int(11) DEFAULT NULL,
　　`name` varchar(10) DEFAULT NULL,
　　`id_1` int(11) NOT NULL AUTO_INCREMENT,
　　PRIMARY KEY (`id_1`)
　　) ENGINE=InnoDB AUTO_INCREMENT=7
　　测试：
session 1:session 2:select * from t1 for update;　　update t2 join t1 on t1.id=t2.id set t2.age=10;
　　发生等待
　　mysql> select a.lock_trx_id,b.trx_mysql_thread_id,b.trx_query,a.lock_mode,a.lock_type from INNODB_LOCKs a join INNODB_trx b on a.lock_trx_id=b.trx_id\G;
　　*************************** 1. row ***************************
　　lock_trx_id: 18944085
　　trx_mysql_thread_id: 33762
　　trx_query: update t2 join t1 on t1.id=t2.id set t2.age=10
　　lock_mode: S
　　lock_type: RECORD
　　lock_page: 3
　　lock_table: `test`.`t1`
　　*************************** 2. row ***************************
　　lock_trx_id: 18944084
　　trx_mysql_thread_id: 33761
　　trx_query: NULL
　　lock_mode: X
　　lock_type: RECORD
　　lock_page: 3
　　lock_table: `test`.`t1`
　　2 rows in set (0.00 sec)
　　看出事务二的update在获取t1表的S锁，但是这条语句只对t1表做查询匹配操作，两个事务执行的语句调个顺序看看结果
session 1session 2update t2 join t1 on t1.id=t2.id set t2.age=10;　　select * from t1 for update;
　　发生等待
　　*************************** 1. row ***************************
　　lock_trx_id: 18944086
　　trx_mysql_thread_id: 33761
　　trx_query: select * from t1 for update
　　lock_mode: X
　　lock_type: RECORD
　　lock_page: 3
　　lock_table: `test`.`t1`
　　*************************** 2. row ***************************
　　lock_trx_id: 18944085
　　trx_mysql_thread_id: 33762
　　trx_query: NULL
　　lock_mode: S
　　lock_type: RECORD
　　lock_page: 3
　　lock_table: `test`.`t1`
　　2 rows in set (0.01 sec)
　　事务二这时是在获取X锁，注意死锁显示都在获取同一个位置的锁，并且update拿到有X锁，事务一的语句显然是首先从t1表获取S锁，再获取X锁，最终获取S锁
　　加上索引再进行测试：
session 1session 2　　update t2 join t1  on (t1.id=t2.id and t1.id=4) set t2.age=10;

　　select * from t1 where>　　正常无阻塞！
　　总结：
　　通过上面的测试基本已经清楚mysql在关联update时，只是作为关联查询的表如果没有对应索引会对满足条件的行数据会进行加锁操作，在t1表进行数据查询时满足id=4条件的所有数据都会加S锁，和t2表关联对数据进行判断并做更新时对应的行会请求X锁，当对数据更新完成后会释放X锁并请求S锁，整个流程为 S-> X->S,如果未给t1表指明条件又以它作为驱动表的话就会造成t1表的记录都会加锁，在对条件字段id加了索引过后t1表不会产生阻塞，在生产环境中有这种关联更新的语句需要注意索引的问题。