MySQL锁原理思维导图模板_ProcessOn思维导图、流程图

锁分类

操作的粒度

表级锁

每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低【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 确保不会有其他线程对数据库做更新，然后对整个库做备份.在备份过程中整个库完全 处于只读状态。

表级锁 (偏读)

表级锁定分为: 表共享读锁(共享锁)与表独占写锁(排他锁)。

表级锁特点: 开销小,加锁快.出现死锁的概率比较低;锁定的粒度比较大,发生锁冲突的概率比较高, 并 发度比较低.MyISAM引擎默认使用表级锁

行级锁 (偏写)

介绍

行锁的是mysql锁中粒度最小的一种锁，因为锁的粒度很小，所以发生资源争抢的概率也最小，并发性 能最大

前提

使用 innoDB 引擎

开启事务 (隔离级别为 Repeatable Read )

InnoDB行锁的类型

共享锁(S)

排他锁(X)

加锁的方式

InnoDB引擎默认更新语句，update,delete,insert 都会自动给涉及到的数据加上排他锁 elect 语句默认不会加任何锁类型，如果要加可以使用下面的方式:

加共享锁(S):select * from table_name where ... lock in share mode;

加排他锁(x):select * from table_name where ... for update;

锁兼容

共享锁只能兼容共享锁, 不兼容排它锁

排它锁互斥共享锁和其它排它锁

InnoDB行级锁升级为表级锁

未使用到索引

索引失效

索引字段重复率过高

加锁模式

记录锁(Record Locks)

特点

列必须为唯一索引列或主键列，否则加的锁就会变成临键锁  查询语句必须为精准匹配 = ，不能为 >、<、like等，否则也会退化成临键锁。

含义

记录锁就是为某行记录加锁(也就是行锁)

例子

-- 在id=1的数据上添加记录锁,阻止其他事务对其进行更新操作  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 锁。（可解决幻读问题）

每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时， 会锁住一 段左开右闭区间的数据。

加锁原则

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+树中的索引内容】

for update 加的是写锁(排它锁) , for update 的加锁内容是非主键索引树上符合条件的索引项,以及这些索引项对应的主键索引树上相应的索引项。在两个索引上都加了锁.

锁实战

间隙锁实战

产生条件

RR事务隔离级别)

使用普通索引锁定

使用多列唯一索引

使用唯一索引锁定多行记录

查看间隙锁设置

结论

唯一索引的间隙锁【不会产生间隙锁】

只使用一个索引查询，并且只锁定一条记录

唯一索引的间隙锁【产生间隙锁】

where id between 5 and 7 在范围查询时间隙锁的添加方式是: 过滤条件的最左侧值,作为左区 间也就是记录值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 这些需要加锁的操作

MySQL如何解决幻读

产生幻读的原因:

行锁只能锁住行, 但是新插入的记录,操作的是行之间的 "间隙"

InnoDB 行锁优化建议

尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为 表级锁定

合理设计索引

尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记 录

尽量控制事务的大小，减少锁定的资源量和锁定时间长度;

在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别 所带来的附加成本。

乐观锁

含义

不是数据库提供的功能，需要开发者自己去实现

实现方式

冲突检测和数据更新【CAS】

ABA问题

在极端情况下可能会出现ABA问题

ABA问题解决

设计一个单独的可以顺序递增的version字段 每操作一次，将那条记录的版 本号加 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};

行锁原理

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 ?;

当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对 读取记录加锁。其中，除了 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的快照读, 所以是不加锁的.

delete from v1 where id = 10; 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中的查询条件

不满足,跳过当前记录，继续读取索引的下一条记录

满足，则返回记录，此记录满足了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的顺序处理，

必须同时锁定两个资源时，要保证在任何时刻都应该按 照相同的顺序来锁定资源。

行级锁死锁

场景1:

产生原因