一次死锁的追溯

        大早上的来到公司，连接上xshell，观测下stage环境上的日志，竟然发现了一条死锁。果断找了下dba，打印出来了死锁的日志。

LATEST DETECTED DEADLOCK
------------------------
2019-01-21 07:00:05 7f82879b7700
*** (1) TRANSACTION:
TRANSACTION 35802302422, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 10431071, OS thread handle 0x7f832d7d1700, query id 591985031 10.2.3.97 meicai_rw updating
update t_mall_stock set
u_t =
Case id 
When 49380 Then 1548025205292
End 
, in_stock =
Case id 
When 49380 Then 0
End 
, expect_arrival_time =
Case id 
When 49380 Then 0
End 
where
( 
(sku_id = 1239 AND warehouse_id = 1 AND owner_id = 1132)
)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 22420 page no 148 n bits 176 index `PRIMARY` of table `mall_stock_center_1`.`t_mall_stock` trx table locks 1 total table locks 2 trx id 35802302422 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0 
*** (2) TRANSACTION:
TRANSACTION 35802302421, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 10430493, OS thread handle 0x7f82879b7700, query id 591985032 10.2.3.97 meicai_rw updating
update t_mall_stock set stock_num = 2.00,instock_num=2,in_stock = 1,u_t=1548025205335,u_by=1001
where is_deleted = 0 and sku_id = 1239 AND warehouse_id = 1 AND owner_id = 1132
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 22420 page no 148 n bits 176 index `PRIMARY` of table `mall_stock_center_1`.`t_mall_stock` trx table locks 1 total table locks 2 trx id 35802302421 lock_mode X locks rec but not gap lock hold time 0 wait time before grant 0 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 22420 page no 522 n bits 704 index `idx_sku_w_o_id` of table `mall_stock_center_1`.`t_mall_stock` trx table locks 1 total table locks 2 trx id 35802302421 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0 
*** WE ROLL BACK TRANSACTION (2)

经过分析，上面的sql是不会产生死锁的，DBA问了问，还有没有其他的SQL，当时没有想起来，后来仔细查阅了代码，才发现有一条select for update的过。

下面来设计下项目死锁的场景：

事务A： start transaction; 然后 select sku_id from t_mall_stock where id = 195924 for update。

事务B：start transaction; 然后 update t_mall_stock set stock_num = 100 where sku_id = 4567 and warehouse_id = 2 and owner_id = 1;

当执行到这一步时，我们可以观察mysql的锁记录

根据INNODB_LOCKS表和INNODB_TRX表，我们发现有两个事务一个处于等待，一个正在运行，并且都是X锁，对主键进行的锁。

事务A：update t_mall_stock set stock_num = 200 where sku_id = 4567 and warehouse_id = 2 and owner_id = 1;

结果事务A在执行最后一个sql时，发生了死锁。

 

如果把事务A的第一条SQL改为  select sku_id from t_mall_stock where sku_id = 4567 and warehouse_id =2 and owner_id = 1 for update

那么再遵从上述的步骤，则只会产生锁等待，并且处于锁等待的是唯一索引的等待

总结：对唯一索引的加锁，mysql的innodb会先试图在唯一索引上加锁，然后在试图加上主键锁。所以当处于并发环境的对数据库更新，一定要采用相同的更新条件。 另一方面也是尽量减少并发的场景。