现在有表:
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。
索引加锁,如果一条语句用到了非主键索引,会先给非主键索引加锁,在给主键索引加锁
关于给索引加锁的例子:
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的执行计划,
如果没有用到索引,就会采用表级锁,如果更新操作用到了主键索引,就是先给主键索引加锁,在给其他索引加锁,而非主键索引加锁顺序相反,更新操作,按照主键更新,可以减少死锁的发生