MySQL锁原理
2024-12-30 18:11:11 0 举报
AI智能生成
深入浅出 锁原理
作者其他创作
大纲/内容
锁分类
操作的粒度
表级锁
每次操作锁住整张表。锁定粒度大,发生锁冲突的概率最高,并发度最低【MyISAM、InnoDB、BDB】
行级锁
每次操作锁住一行数据。锁定粒度最小,发生锁冲突的概率最低,并发度最高。【InnoDB】
页级锁
每次锁定相邻的一组记录,锁定粒度界于表锁和行锁之间,开销和加锁时间界于表锁和行 锁之间,并发度一般【BDB 存储引擎】
操作的类型
读锁(S锁)
共享锁,针对同一份数据,多个读操作可以同时进行而不会互相影响。
写锁(X锁)
排他锁,当前写操作没有完成前,它会阻断其他写锁和读锁
关系
S锁
事务A对记录添加了S锁,可以对记录进行读操作,不能做修改,其他事务可以对该记 录追加S锁,但是不能追加X锁,要追加X锁,需要等记录的S锁全部释放。
X锁
事务A对记录添加了X锁,可以对记录进行读和修改操作,其他事务不能对记录做读和 修改操作。
IS
意向共享锁,表级锁,已加S锁的表,肯定会有IS锁,反过来,有IS锁的表,不一定会有S 锁
IX
意向排它锁,表级锁,已加X锁的表,肯定会有IX锁,反过来,有IX锁的表,不一定会有X 锁.
操作的性能
乐观锁
一般的实现方式是对记录数据版本进行比对,在数据更新提交的时候才会进行冲突检测, 如果发现冲突了,则提示错误信息。
悲观锁
在对一条数据修改的时候,为了避免同时被其他人修改,在修改数据之前先锁定,再修改 的控制方式。共享锁和排他锁是悲观锁的不同实现,但都属于悲观锁范畴。
加锁的范围
全局锁
含义
全局锁是对整个数据库实例加锁,添加全局锁后,以下语句会被阻塞:
数据更新语句(增删改)、 数据定义语句(建表、修改表结构等)和更新类事务的提交语句。
数据更新语句(增删改)、 数据定义语句(建表、修改表结构等)和更新类事务的提交语句。
全局锁使用场景
做全库逻辑备份(mysqldump). 重新做主从时候也就是把整库每个 表都 select 出来存成文本.
通过 FTWRL 确保不会有其他线程对数据库做更新,然后对整个库做备份.在备份过程中整个库完全 处于只读状态。
通过 FTWRL 确保不会有其他线程对数据库做更新,然后对整个库做备份.在备份过程中整个库完全 处于只读状态。
表级锁 (偏读)
表级锁定分为: 表共享读锁(共享锁)与表独占写锁(排他锁)。
表级锁特点: 开销小,加锁快.出现死锁的概率比较低;锁定的粒度比较大,发生锁冲突的概率比较高,
并 发度比较低.MyISAM引擎默认使用表级锁
并 发度比较低.MyISAM引擎默认使用表级锁
行级锁 (偏写)
介绍
行锁的是mysql锁中粒度最小的一种锁,因为锁的粒度很小,所以发生资源争抢的概率也最小,并发性 能最大
前提
使用 innoDB 引擎
开启事务 (隔离级别为 Repeatable Read )
InnoDB行锁的类型
共享锁(S)
排他锁(X)
加锁的方式
InnoDB引擎默认更新语句,update,delete,insert 都会自动给涉及到的数据加上排他锁
elect 语句默认不会加任何锁类型,如果要加可以使用下面的方式:
elect 语句默认不会加任何锁类型,如果要加可以使用下面的方式:
加共享锁(S):select * from table_name where ... lock in share mode;
加排他锁(x):select * from table_name where ... for update;
锁兼容
共享锁只能兼容共享锁, 不兼容排它锁
排它锁互斥共享锁和其它排它锁
InnoDB行级锁升级为表级锁
未使用到索引
索引失效
索引字段重复率过高
加锁模式
记录锁(Record Locks)
特点
列必须为唯一索引列或主键列,否则加的锁就会变成临键锁
查询语句必须为精准匹配 = ,不能为 >、<、like等,否则也会退化成临键锁。
查询语句必须为精准匹配 = ,不能为 >、<、like等,否则也会退化成临键锁。
含义
记录锁就是为某行记录加锁(也就是行锁)
例子
-- 在id=1的数据上添加记录锁,阻止其他事务对其进行更新操作
select * from test where id = 1 FOR UPDATE
-- 通过主键索引和唯一索引对数据进行update操作时,也会对该行数据加记录锁
update set age = 50 where id = 1; -- id是主键
select * from test where id = 1 FOR UPDATE
-- 通过主键索引和唯一索引对数据进行update操作时,也会对该行数据加记录锁
update set age = 50 where id = 1; -- id是主键
间隙锁(Gap Lock)
含义
间隙锁是封锁索引记录中的间隔,可以是两个索引记录之间,也可能是第一个索引 记录之前或最后一个索引之后的空间
例子
SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;【所有在 (1,10) 区间内的记录行都会被锁住】
临键锁 ( Next-Key Locks )
含义
next-key锁其实包含了记录锁和间隙锁,即锁定一个范围,并且锁定记 录本身,
InnoDB默认加锁方式是next-key 锁。(可解决幻读问题)
InnoDB默认加锁方式是next-key 锁。(可解决幻读问题)
每个数据行上的非唯一索引列上都会存在一把临键锁,当某个事务持有该数据行的临键锁时,
会锁住一 段左开右闭区间的数据。
会锁住一 段左开右闭区间的数据。
加锁原则
1. 加锁的基本单位是next-key lock。next-key lock是左开右闭区间。
2. 查找过程中访问到的对象才会加锁。
3. 索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁。
4. 索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙
锁。
5. 唯一索引上的范围查询会访问到不满足条件的第一个值为止。
lock in share mode 与 for update 的区别
lock in share mode 加的是读锁(共享锁) , lock in share mode 只锁覆盖索引
【lock in share mode 只锁非主键索引对应的B+树中的索引内容】
【lock in share mode 只锁非主键索引对应的B+树中的索引内容】
for update 加的是写锁(排它锁) , for update 的加锁内容是非主键索引树上符合条件的索
引项,以及这些索引项对应的主键索引树上相应的索引项。在两个索引上都加了锁.
锁实战
间隙锁实战
产生条件
RR事务隔离级别)
使用普通索引锁定
使用多列唯一索引
使用唯一索引锁定多行记录
查看间隙锁设置
结论
唯一索引的间隙锁【不会产生间隙锁】
只使用一个索引查询,并且只锁定一条记录
唯一索引的间隙锁【产生间隙锁】
where id between 5 and 7 在范围查询时间隙锁的添加方式是:
过滤条件的最左侧值,作为左区 间也就是记录值id=5
向右寻找最靠近检索条件的记录值 id = 11,那么锁定的区间就是 (5,11) .
过滤条件的最左侧值,作为左区 间也就是记录值id=5
向右寻找最靠近检索条件的记录值 id = 11,那么锁定的区间就是 (5,11) .
唯一索引实战
数据准备
数据
测试1: 只使用记录锁,不会产生间隙锁
数据
测试2: 产生间隙锁的情况
数据
测试3: 针对不存在的数据,进行加锁
数据
普通索引的间隙锁
数据准备
数据
测试1:在普通索引列上,不管是何种查询,只要加锁,都会产生间隙锁
图1
图2
图3
分析
Next-Key Lock案例
数据准备
数据
测试1 :等值查询普通索引
测试
测试2: 范围查询 唯一索引
图2
测试3:范围查询 普通索引
图3
幻读问题演示与解决方案
含义
幻读指的是同一事务下,不同的时间点,同样的查询,得到不同的行记录的集合。
产生幻读分析
对于RC级别来说,因为每次查询都重新生成一个read view,
也就是查询的都是最新的快照数 据,所以会可能每次查询到不一样的数据,
造成不可重复读
也就是查询的都是最新的快照数 据,所以会可能每次查询到不一样的数据,
造成不可重复读
对于RR级别来说只有第一次的时候生成read view,
查询的是事务开始的时候的快照数据,所以就 不存在不可重复读的问题,
当然就更不可能有幻读的问题了。
查询的是事务开始的时候的快照数据,所以就 不存在不可重复读的问题,
当然就更不可能有幻读的问题了。
幻读问题在 “当前读” 下才会出现。
当前读指的是 lock in share mode 、 for update 、 insert 、 update 、
delete 这些需要加锁的操作
当前读指的是 lock in share mode 、 for update 、 insert 、 update 、
delete 这些需要加锁的操作
MySQL如何解决幻读
产生幻读的原因:
行锁只能锁住行, 但是新插入的记录,操作的是行之间的 "间隙"
InnoDB 行锁优化建议
尽可能让所有的数据检索都通过索引来完成,从而避免InnoDB因为无法通过索引键加锁而升级为 表级锁定
合理设计索引
尽可能减少基于范围的数据检索过滤条件,避免因为间隙锁带来的负面影响而锁定了不该锁定的记 录
尽量控制事务的大小,减少锁定的资源量和锁定时间长度;
在业务环境允许的情况下,尽量使用较低级别的事务隔离,以减少MySQL因为实现事务隔离级别
所带来的附加成本。
所带来的附加成本。
乐观锁
含义
不是数据库提供的功能,需要开发者自己去实现
实现方式
冲突检测和数据更新【CAS】
ABA问题
在极端情况下可能会出现ABA问题
ABA问题解决
设计一个单独的可以顺序递增的version字段
每操作一次,将那条记录的版 本号加 1
每操作一次,将那条记录的版 本号加 1
-- 1.查询出商品信息 version=1
select (quantity,version) from items where id = 1;
-- 2.根据商品生成订单
insert into orders ...
insert into items ...
-- 3.修改商品库存,同时递增版本字段值
update products set quantity=quantity-1,version=version+1 where id=1 and version=#{version};
select (quantity,version) from items where id = 1;
-- 2.根据商品生成订单
insert into orders ...
insert into items ...
-- 3.修改商品库存,同时递增版本字段值
update products set quantity=quantity-1,version=version+1 where id=1 and version=#{version};
行锁原理
SQL语句背后的锁实现
3种行锁算法
RecordLock锁:锁定单个行记录的锁。(记录锁,RC、RR隔离级别都支持)
GapLock锁:间隙锁,锁定索引记录间隙(不包括记录本身),确保索引记录的间隙不变。(范围 锁,RR隔离级别支持)
Next-key Lock 锁:记录锁和间隙锁组合,同时锁住数据,并且锁住数据前后范围。(记录锁+范 围锁,RR隔离级别支持)
图
MVCC中的快照读和当前读
快照读: 简单的select操作,属于快照读,不加锁.
select * from table where ?;
当前读: 特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁.
select * from table where ? lock in share mode; select * from table where ? for update;
insert into table values (...);
update table set ? where ?;
delete from table where ?;
insert into table values (...);
update table set ? where ?;
delete from table where ?;
当前读,读取记录的最新版本。并且,读取之后,还需要保证其他并发事务不能修改当前记录,对 读取记录加锁。其中,除了 lock in share mode 对读取记录加S锁 (共享锁)外,其他的操作, 都加的是X锁 (排它锁).
为什么将 插入/更新/删除 操作,都归为当前读
MySQL两阶段加锁协议 (Two-phase locking - 2PL)
俩阶段锁
InnoDB 引擎加锁原理分析
demo
查询语句加锁了吗? 加的是什么锁 ?
查询语句加锁了吗? 加的是什么锁 ?
select * from v1 where t1.id = 1;
分析加锁需要考虑当前的隔离级别,该语句在串行化下MVCC会降级成Lock-Based CC,是加锁的,加的是读 锁.
在其他三种隔离级别下,由于MVCC的快照读, 所以是不加锁的.
分析加锁需要考虑当前的隔离级别,该语句在串行化下MVCC会降级成Lock-Based CC,是加锁的,加的是读 锁.
在其他三种隔离级别下,由于MVCC的快照读, 所以是不加锁的.
delete from v1 where id = 10;
1. id列是不是主键?
2. 当前系统的隔离级别是什么?
3. id列如果不是主键,那么id列上有索引吗?
4. id列上如果有二级索引,那么这个索引是唯一索引吗? 5. 两个SQL的执行计划是什么?索引扫描?全表扫描?
1. id列是不是主键?
2. 当前系统的隔离级别是什么?
3. id列如果不是主键,那么id列上有索引吗?
4. id列上如果有二级索引,那么这个索引是唯一索引吗? 5. 两个SQL的执行计划是什么?索引扫描?全表扫描?
RC级别加锁机制
组合1-2
组合3-4
RR级别加锁机制
组合5,6,7
组合8.9
复杂SQL的加锁分析
demo
SQL中的where条件提取
Index Key
用于确定SQL查询在索引中的连续范围(起始范围+结束范围)的查询条件,被称之为Index Key. 由于 一个范围,至少包含一个起始与一个终止,
因此Index Key也被拆分为Index First Key和Index Last Key,分别用于定位索引查找的起始,以及 索引查询的终止条件.
Index Filter
index Filter,用于过滤索引查询范围中不满足查询条件的记录,因此对于索引范围中的每一条记 录,均需要与Index Filter进行对比,若不满足Index Filter则直接丢弃,继续读取索引下一条记录.
Table Filter
所有不属于索引列的查询条件,都在Table Filter中
通过前面的过滤,记录已经满足了Index key 和 Index Filter中的条件,回表读取了完整的记录,接下来
就该判断完整记录是否满足Table Filter中的查询条件
就该判断完整记录是否满足Table Filter中的查询条件
不满足,跳过当前记录,继续读取索引的下一条记录
满足,则返回记录,此记录满足了where的所有条件,可以返回给前端用户。
demo
例子
结论:在Repeatable Read隔离级别下,针对一个复杂的SQL,首先需要提取其where条件。Index Key确定的范围,需要加上GAP锁;Index Filter过滤条件,视MySQL版本是否支持ICP,若支持ICP, 则不满足Index Filter的记录,不加X锁,否则需要X锁;Table Filter过滤条件,无论是否满足,都需 要加X锁。
死锁与解决方案
表的死锁
产生原因
用户A--》A表(表锁)--》B表(表锁)
用户B--》B表(表锁)--》A表(表锁)
解决方案
这种死锁比较常见,是由于程序的BUG产生的,除了调整的程序的逻辑没有其它的办法。
对于数据库的多表操作时,尽量按照相同的顺序进行处理,尽量避免同时锁定两个资源,
如操 作A和B两张表时,总是按先A后B的顺序处理,
如操 作A和B两张表时,总是按先A后B的顺序处理,
必须同时锁定两个资源时,要保证在任何时刻都应该按 照相同的顺序来锁定资源。
行级锁死锁
场景1:
产生原因
如果在事务中执行了一条没有索引条件的查询,引发全表扫描,把行级锁上升为全表记录锁定
多个这样的事务执行后,就很容易产生死锁和阻塞,最终应用系统会越来越慢,发生阻塞或 死锁。
解决方案
SQL语句中不要使用太复杂的关联多表的查询;使用explain“执行计划"对SQL语句进行分析,对于有全
表扫描和全表锁定的SQL语句,建立相应的索引进行优化。
场景2&3
产生原因1
两个事务分别想拿到对方持有的锁,互相等待,于是产生死锁。
产生原因2
每个事务只有一个SQL,但是有些情况还是会发生死锁.
解决方案
如上面的原因2和原因3, 对索引加锁顺序的不一致很可能会导致死锁,
所以如果可以,尽量 以相同的顺序来访问索引记录和表。
在程序以批量方式处理数据的时候,如果事先对数据排序,保证每 个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能;
死锁总结
对索引加锁顺序的不一致很可能会导致死锁, 所以如果可以, 尽量以相同的顺序来访问索引记录和 表. 在程序以批量方式处理数据的时候, 如果事先对数据排序, 保证每个线程按固定的顺序来处理记 录, 也可以大大降低出现死锁的可能.
间隙锁往往是程序中导致死锁的真凶, 由于默认情况下 MySQL 的隔离级别是 RR,所以如果能确定 幻读和不可重复读对应用的影响不大, 可以考虑将隔离级别改成 RC, 可以避免 Gap 锁导致的死锁.
为表添加合理的索引, 如果不走索引将会为表的每一行记录加锁, 死锁的概率就会大大增大
避免大事务, 尽量将大事务拆成多个小事务来处理. 因为大事务占用资源多, 耗时长, 与其他事务冲
突的概率也会变高.
避免在同一时间点运行多个对同一表进行读写的脚本, 特别注意加锁且操作数据量比较大的语句.
设置锁等待超时参数:innodb_lock_wait_timeout,在并发访问比较高的情况下,如果大量事务
因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖跨数据库。 我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。
0 条评论
下一页