Mysql DeadLock 死锁

表结构

CREATE TABLE `t_custom_sku_price` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `customSkuId` BIGINT(20) DEFAULT NULL COMMENT '自定义skuid',
  `priceType` INT(11) DEFAULT NULL COMMENT '价格类型',
  `price` DECIMAL(14,4) DEFAULT '0.0000' COMMENT '价格',
  PRIMARY KEY (`id`),
  KEY `customSkuId` (`customSkuId`)
)

执行步骤

 

sessionA	sessionB
begin	begin
delete from t_custom_sku_price where customSkuId in (6243929);
	delete from t_custom_sku_price where customSkuId in (6243923);
insert into t_custom_sku_price (customSkuId,priceType,price)values(6243929,9,0.0);
	insert into t_custom_sku_price (customSkuId,priceType,price)values(6243932,9,0.0);

执行结果

数据不存在的情况

sessionA

sessionB

sessionA rollback前，各事务锁占用情况：

SELECT * FROM information_schema.innodb_locks; 

 

代码中delete操作的是非唯一索引列在innoDB引擎下会触发 next-key lock（间隙锁）。

 

 

 

https://my.oschina.net/u/2342969/blog/1813772?p=1

 

begin	begin
delete from t_custom_sku_price where customSkuId in (6243929);
	insert into t_custom_sku_price (customSkuId,priceType,price)values(6243932,9,0.0)

 

 

http://blog.itpub.net/22664653/viewspace-2145736/

 

调 delete 不存在的记录是要加上GAP锁，事务日志中显示Lock_mode X wait .

 

数据存在的情况，没有死锁

 

如何解决呢？
   a 先select 检查一下看看是否存在，然后在删除。这里也存在两个或者多个会话并发执行同一个select where条件的，这里需要开发同学做处理。
   b insert into on deuplicate key .