mysql系列-锁
一 概念区分
1.1 悲观锁
1.1.1 场景模拟
-- 0.开始事务[begin work;start transaction; (三者选一就可以)]
begin;
-- 1.查询出商品信息
select status from t_goods where id=1 for update;
-- 2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
-- 3.修改商品status为2
update t_goods set status=2;
-- 4.提交事务[commit work;]
commit;上面的查询语句中，我们使用了select…for update的方式，这样就通过开启排他锁的方式实现了悲观锁。
此时在t_goods表中，id为1的 那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。
MySQL InnoDB默认行级锁，行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住。
1.1.2 死锁场景模拟
线程1
-- 设置事务自动提交关闭
SELECT @@autocommit;
SET autocommit = 0;
-- 1、更新id=1的考生
SELECT * FROM student WHERE id = 1 FOR UPDATE;
UPDATE student SET `name` = '张三1' WHERE id = 1;
-- 2、更新id=2的考生【死锁】
SELECT * FROM student WHERE id = 1 FOR UPDATE;线程2
-- 设置事务自动提交关闭
SELECT @@autocommit;
SET autocommit = 0;
-- 1、更新id=2的考生
SELECT * FROM student WHERE id = 2 FOR UPDATE;
UPDATE student SET `name` = '李四2' WHERE id = 2;
-- 2、更新id=1的考生【死锁】
SELECT * FROM student WHERE id = 1 FOR UPDATE;不足
悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。
但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；
另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载，还会降低了并行性。
1.2 乐观锁
1.2.1 实现原理
使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+1操作。并判断当前版本号是不是该数据的最新的版本号。
1.2.2 场景模拟
-- 1.查询出商品信息
select status,status,version from t_goods where id=#{id}
-- 2.根据商品信息生成订单
-- 3.修改商品status为2
update
t_goods
set
status=2,
version=version+1
where
id=#{id}
and
version=#{version}1.2.3 不足
乐观并发控制相信事务之间的数据竞争的概率是比较小的，因此尽可能直接执行。当版本被及时更新时，会可能产生提交失败的情况，需重新获取最新版本号数据。
1.2.4 适用场景
1、整体并发量较低的场景【并发较高的话，可能失败率高，且效率低下】 2、核心业务数据
二 mysql锁
2.1 记录锁
1、通过主键更新
对某条记录加锁：
update user set name='hippo' where age=18;2.2 间隙锁
1、定义
即对某个范围加锁，但是不包含范围的临界数据
2、模拟场景
-- 对id大于1并且小于等于10的用户加锁
update user set age=age+1 where id>1 and id<=10;3、加锁分析
假如表中只有这样两条数据的话：
id | name | age |
|---|---|---|
1 | 张三 | 1 |
10 | 李四 | 10 |
针对age索引。产生三个索引范围：
(-∞,1]，(1,10]，(10,+∞)
更新语句为：
update user set name='hippo' where age=5;由于表中不存在age=5的记录，并且age=5刚好落在 (1,10] 的区间范围内，所以会对 (1,10] 的范围加锁。
三 验证
3.1 更新不存在数据验证
3.1.1 创建表
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(255) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
PRIMARY KEY (`id`),
KEY `idx_age` (`age`)
) COMMENT='用户表';3.1.2 初始化数据
INSERT INTO `user` VALUES ('1', '张三', '1');
INSERT INTO `user` VALUES ('2', '河马', '10');3.1.3 更新数据
[1]事务A：
begin;
update user set name='hippo' where age=5;
commit;事务A执行完update以后，在commit之前，事务B介入。
[2]事务B：
insert into user(name, age)values("王五", 2);事务B阻塞。
[3]事务C:
insert into user(name, age)values("河马", 11);事务C执行成功。
[4]事务A提交后： 当事务A执行commit后，事务B执行成功。
3.2 验证已存在的数据
3.2.1 更新存在数据，间隙锁范围
表中数据情况为：
id | name | age |
|---|---|---|
1 | 李四 | 1 |
5 | 张三 | 5 |
10 | 河马 | 10 |
3.2.2 更新语句
update user set name='hippo' where age=5;age=5的数据落在 (1,5] 的区间范围内，所以会对 (1,5] 的范围加锁。
此外，MySQL锁为了保证数据的安全性，还会向右遍历到不满足条件为止，还会再加一个间隙锁，也就是 (5,10] 的范围。
所以，这条SQL的加锁返回是 (1,5] 和 (5,10]
3.3 主键更新
3.3.1 id对应数据存在
update user set name='hippo' where id=5;加锁情况
如果存在id=5的数据，MySQL的 Next-Key Locks 会退化成 Record Locks ，也就是只在id=5的这一行记录上加锁。
3.3.2 id对应数据不存在
跟条件左右范围数据有关，(1,10] 。左开右闭
四 总结
4.1 加锁与索引关系
4.1.1 结论
MySQL锁是加在索引记录上面的。
4.2 非唯一性索引
如果是非唯一性索引，不论表中是否存在该记录，除了会对该记录所在范围加锁，还会向右遍历到不满足条件的范围进行加锁。
4.3 唯一性索引
4.3.1 唯一索引对应数据存在
如果是唯一索引，如果表中存在该记录，只对该行记录加锁。
4.3.2 唯一索引对应数据不存在
如果表中不存在该记录，除了会对该记录所在范围加锁，还会向右遍历到不满足条件的范围进行加锁。
腾讯云开发者
