MySQL高阶之锁原理篇
update流程图
update t set c=‘曹操’ where id = 1;
主键等值更新速度最快 加行锁
主键范围更新需要加间隙锁 防止出现幻读
如果不应用索引需要加全表锁+间隙锁
Mysql锁的分类
锁的粒度
- 全局锁：锁住整个mysql实例 此时只能读
- 表级锁：锁定某一个表
- 行级锁：对某一行数据加锁。只有InnoDB引擎支持
锁的功能
- X锁(写锁)：排它锁
- S锁(读锁)：共享锁
锁的类型
全局锁
- 锁住：flush tables with read lock;
- 释放锁：unlock tables; 会话断开会自动解锁
表级锁
- 读锁：SessionA加锁后，表处于只读状态，其他的Session只能读，不能进行写，sessionA也只能读不能写，其他session也可以加读锁
- 加锁：lock table {表名} read
- 释放锁：unlock tables
写锁
- 加锁之后当前会话可读可写，其他会话不能读也不能写
- 加锁：lock table {表名} write
- 释放锁：nulock tables
演示
CREATE TABLE mylock (
id int(11) NOT NULL AUTO_INCREMENT,
NAME varchar(20) DEFAULT NULL,
PRIMARY KEY (id)
);
INSERT INTO mylock (id,NAME) VALUES (1, 'a');
INSERT INTO mylock (id,NAME) VALUES (2, 'b');
INSERT INTO mylock (id,NAME) VALUES (3, 'c');
INSERT INTO mylock (id,NAME) VALUES (4, 'd');
读锁演示
写锁演示
元数据锁
当开启事务之后执行对某一个表的查询操作，此时会对表加元数据锁，此时不允许其他事务对其表结构进行修改。
1、session1: begin;--开启事务
select * from mylock;--加MDL读锁
2、session2: alter table mylock add f int; -- 修改阻塞
3、session1：commit; --提交事务 或者 rollback 释放读锁
4、session2：Query OK, 0 rows affected (38.67 sec) --修改完成
Records: 0 Duplicates: 0 Warnings: 0
自增锁
mysql支持自增主键，生成的主键不能重复，所以需要有自增锁来保证生成的主键是唯一的。
行锁
只有InnoDB引擎支持 行级锁是由引擎层来实现的 InnoDB行锁是通过给索引上的索引项来加锁的 因此还有通过索引条件检索的数据才会使用到行锁,否则使用的是表锁.即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引
记录锁
只锁定某一个表的某一条记录。也有X锁和S锁
加锁的时机：根据主键等值查询并且命中一条记录
update t set c = 3 where id = 1 --x锁
delete from t where id = 1 --x锁
select * from t where id = 1 for update --x锁
select * from t where id = 1 lock in share mode -- s锁
间隙锁
记录和记录之间的缝隙值，当加间隙锁后不允许间隙值的插入
例如 三条记录：id：1 5 10
1和5之间的缝隙值就是2 3 4，此时不允许插入2 3 4 的记录
select * from t where id >=1 and id <10 for update --x锁
#加锁的时机：通过主键更新或查询询数据没有命中任何记录时加间隙锁
临建锁
- 记录锁+间隙锁：默认的加锁方式。根据主键范围更新的操作加临建锁.
- 如果主键查询命中了一条记录会退化为记录锁
- 如果主键查询没有命中任何记录会退化成间隙锁
意向锁(Innodb表级锁)
当对表加表级锁，需要判断该表是否有行锁的存在，如果存在是无法加锁的。此时需要快速知道表上是否有行锁的存在，引入了意向锁，会在加行锁后，在表上新增一个标记位，标志该表有行级锁。
插入意向锁
在执行insert操作后，事务还没有提交之前，需要锁定插入行的主键的值。防止其他事务方式该主键
实战面试题
面试官:以下这条语句执行过程当中加入了哪些锁?
delete from t where id =10
看似简单 其实全是坑!!!如果你连前提都没有提出疑问,那么不好意思,请回家等通知!!!
百分之80的人会说,加写锁
这个答案对吗？说不上来。即可能是正确的，也有可能是错误的，已知条件不足，这个问题没有答案。必须还要知道以下的一些前提，前提不同，能给出的答案也就不同。要回答这个问题，还缺少哪些前提??
条件？
- 前提一：id列是不是主键？
- 前提二：当前系统的隔离级别是什么？
- 前提三：id列如果不是主键，那么id列上有索引吗？
- 前提四：id列上如果有二级索引，那么这个索引是唯一索引吗？
- 前提五：两个SQL的执行计划是什么？索引扫描？全表扫描？
组合1：id主键 + RC隔离级别
因为id是主键，并且sql语句执行命中了一条记录，此时会加一把临建锁，但是由于只命中了一条记录，因此会退化成记录x锁。因此只会加一次x锁
组合2：id唯一索引(辅助索引) + RC隔离级别
- 第一次锁:由于id是唯一索引(非主键索引),此时根据索引找到一条记录,加x锁(行锁)
- 第二次锁:找到id-10的记录之后,需要通过id=10这条记录找到主键,假设该例子主键是name字段,那么需要通过name主键找到记录(回表),此时加x锁(行锁)
- 总结:加两次x锁
组合3:id非唯一索引+RC隔离级别
- 和组合2有点类似.
- 第一次锁:锁住id=10的所有记录 加行锁
- 第二次锁:通过找到的记录的主键回表查询具体对的某一条数据行,加行锁
组合4：id无索引+ RC隔离级别
分析:犹豫id字段没有索引,那么mysql只能选择走主键索引,进行全表扫描.如图 满足条件的有两条记录,但是所有的记录行都被锁住了.但是mysql server层对其进行了优化 当所有记录行加x锁之后会进行判断,此时id=10的只有两条记录符合,因此会释放其他不满足条件的记录行,这是一个加锁的过程,但是这个操作也是不能省略的.
组合5：id主键+RR
和组合1一致
组合6：id唯一索引+ RR隔离级别
和组合2一致
组合7：id非唯一索引 + RR
间隙锁+记录锁+回表x锁
- 分析:RC和RR的区别在于 RC允许幻读的存在 RR不允许幻读的存在 关键就在于这 间隙锁就是RR隔离级别防止幻读的关键所在
- 何为幻读？我一个事务同一条查询sql执行两次，得到的是不同的结果，为什么？因为我第二次读可能读到了别人修改过记录的值
- gap锁：间隙锁 如图 开启一个事务读取id=10的记录，理想应该是两条记录，但是中途有其他事务也插入了一条id=10的记录进来，我再次去读就读到三条记录，为了防止这种情况，gap锁应运而生。他的作用就是在临界旁边加锁，此时id=6至id=10之间是不允许插入数据的，从而杜绝了幻读的发生。
- 加锁过程：通过id非唯一索引定位到第一条满足查询条件的记录，加记录上的x锁，然后加gap锁(间隙锁)，然后通过辅助索引找到主键索引再次加x锁，然后返回；然后读取下一条，重复执行，直到进行到第一条不满足条件(id<>10)的记录，此时不需要再加锁，但是仍然要加gap锁。
组合 8：id无索引+RR
- 所有记录加行锁+全表间隙锁
- 无索引只能进行全表扫描，每条记录加x锁，全表加间隙锁，此处只有6条记录，一共需要6个记录锁，7个GAP锁。试想，如果表上有1000万条记录呢？
组合9：Serializable串行化隔离级别
串行执行 效率很低 不考虑
死锁
产生原因：两个事务互相持有对方请求的资源
死锁处理：MySql自动检测死锁，并且会自动回滚代价小的事务，从而解决死锁
避免：尽快释放资源，减少资源持有的时间。
如何避免死锁呢？
- MySQL默认会主动探知死锁，并回滚某一个影响最小的事务。等另一事务执行完成之后，再重新执行该
事务。
1、注意程序的逻辑
根本的原因是程序逻辑的顺序，最常见的是交差更新
Transaction 1: 更新表A -> 更新表B
Transaction 2: 更新表B -> 更新表A
Transaction获得两个资源
2、保持事务的轻量
越是轻量的事务，占有越少的锁资源，这样发生死锁的几率就越小
3、提高运行的速度
避免使用子查询，尽量使用主键等等
4、尽量快提交事务，减少持有锁的时间
越早提交事务，锁就越早释放
462
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
