之前遇到的死锁分析，记录下
死锁信息如下：
*** (1) TRANSACTION:
TRANSACTION 4363766192, ACTIVE 0 sec
mysql tables in use 2, locked 2
LOCK WAIT 9 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 6
MySQL thread id 8822753, OS thread handle 0x7fca3025b700, query id 2302320886 *.*.*.* cashcoupon_oper Sending data
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 id 2280 page no 3 n bits 176 index `PRIMARY` of table `keap_ticket_cash`.`keap_cash_transcation` trx id 4363766192 lock mode S locks rec but not gap waiting
*** (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 size 1248, 2 row lock(s), undo log entries 6
MySQL thread id 8822751, OS thread handle 0x7fc8718a1700, query id 2302320895 *.*.*.* cashcoupon_oper Sending data
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 id 2280 page no 3 n bits 176 index `PRIMARY` of table `keap_ticket_cash`.`keap_cash_transcation` trx id 4363766191 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2280 page no 3 n bits 176 index `PRIMARY` of table `keap_ticket_cash`.`keap_cash_transcation` trx id 4363766191 lock mode S locks rec but not gap waiting
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
测试：
看出事务二的update在获取t1表的S锁，但是这条语句只对t1表做查询匹配操作，两个事务执行的语句调个顺序看看结果
事务二这时是在获取X锁，注意死锁显示都在获取同一个位置的锁，并且update拿到有X锁，事务一的语句显然是首先从t1表获取S锁，再获取X锁，最终获取S锁
加上索引再进行测试：
总结：
通过上面的测试基本已经清楚原因，mysql在关联update时只是作为关联查询的表，如果没有对应索引会对满足条件的行进行加锁操作，在t1表进行数据查询时满足id=4条件的所有数据都会加S锁，和t2表关联对数据进行判断并做更新时对应的行会请求X锁，当对数据更新完成后会释放X锁并请求S锁，整个流程为 S-> X->S,如果未给t1表指明条件又以它作为驱动表的话就会造成t1表的记录都会加锁，在对条件字段id加了索引过后t1表不会产生阻塞，在生产环境中有这种关联更新的语句需要注意索引的问题。
