mysql更新死锁

转载自:http://blog.csdn.net/aesop_wubo/article/details/8286215

现在有表：

CREATE TABLE `user_item` (
`id` BIGINT(20) NOT NULL,
`user_id` BIGINT(20) NOT NULL,
`item_id` BIGINT(20) NOT NULL,
`status` TINYINT(4) NOT NULL, 

'name' varchar(20) not null,
PRIMARY KEY (`id`),
KEY `idx_1` (`user_id`,`item_id`,`status`)
) ENGINE=INNODB DEFAULT CHARSET=utf-8

首先mysql引擎为innodb，才会支持事务，在这个数据库引擎中使用行级锁保证事务的特性，和防止一些并发出现的问题。

行级锁：分为 共享锁 和排他锁。

共享锁兼容请求的共享锁，不兼容排他锁。

排他锁不兼容共享锁和排他锁。 

select 语句不会去获取锁，如果想要给查询的行加锁，使用for update来加排他锁， lock in share mode来加共享锁。

注意:1 .insert、delete、update 都会获取排他锁。

         2. 如果语句中没有用到索引，而试图加上行级锁，就会获取表锁

select * from user_item where name  = 'z' for update。

          2. 行级锁是给索引加锁，如果一条语句用到了非主键索引，会先给非主键索引加锁，在给主键索引加锁

关于给索引加锁的例子：

session1  事务1	session2 事务2
select  * from user_item where id = 3 for update;
	select * from user_item where id =4 for update ; 虽然是不同记录，但是行级锁是给索引加锁，由于事务1已经获取到排他锁，并给主键索引加锁，当前事务需要等待

关于语句中用到非主键索引，会先给非主键索引加锁，在给主键索引加锁，这个语句很可能出现死锁，例如：

update user_item set status=1 where user_id=? and item_id=?;

这条语句用到了索引idx_1，会获取行级锁，行级锁是锁索引的，如果一条语句用到了主键索引，就会锁主键索引，如果用到是非主键索引，就会先锁非主键索引，在锁主键索引。

上面的语句的执行过程可以分为:

1.用到了非主键索引，会给非主键索引加锁。

2. 紧接着根据主键更新，用到了主键索引，等待主键索引的锁

3.提交，释放锁。

如果在这个执行过程同时有一条语句持有主键索引的锁，等待非主键索引的锁，这样就会发生死锁。

例如语句：update user_item set status = 3 where id = ? and item_id = ?;
解决方案:

每次更新或者删除操作先查询出主键，再按照主键进行更新、删除

上面的语句改为:

select id from user_item where user_id=? and item_id=? 获取id，再按照ID进行更新。

结论: 在引擎为innodb的mysql中，更新操作会自动获取行级锁，行级锁是给索引加锁，如果发生死锁，可以检查mysql的执行计划，

如果没有用到索引，就会采用表级锁，如果更新操作用到了主键索引，就是先给主键索引加锁，在给其他索引加锁，而非主键索引加锁顺序相反，更新操作，按照主键更新，可以减少死锁的发生