mysql 锁

ddatsh

dev #mysql 2024/09/06

全局

flush tables with read lock
unlock tables

mysqldump –single-transaction for 支持可重复读隔离级别的事务的innodb

MyISAM 全局锁

表级锁

表锁
元数据锁（MDL）
意向锁
AUTO-INC 锁

表锁

// 表级共享锁，也就是读锁；
lock tables t_student read;

// 表级独占锁，也就是写锁；
lock tables t_stuent write;

// 释放当前会话的所有表锁
// 会话退出后，也会释放所有表锁
unlock tables

表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作

元数据锁

不需要显式使用 MDL，对数据库表进行操作时，会自动给这个表加上 MDL

对表 CRUD 操作时，加的是 MDL 读锁
对表做结构变更操作时，加的是 MDL 写锁

MDL 保证对表 CRUD 操作时，防止其他线程对这个表结构做了变更

MDL 在事务提交后才释放，意味着事务执行期间，MDL 是一直持有的

如果数据库有一个长事务（开启了事务，但是一直还没提交），对表结构做变更操作的时候，可能会发生意想不到的事情：

线程 A 先启用了事务（但一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁
然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，「读读」不冲突
接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞

在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了

为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？

申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作

所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更

意向锁

InnoDB 对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」
InnoDB 对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」

当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁

普通 select 不加行级锁，利用 MVCC 实现一致性读，无锁

select 也可以对记录加共享锁和独占锁

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（*lock tables … read*）和独占表锁（*lock tables … write*）发生冲突

表锁和行锁是满足读读共享、读写互斥、写写互斥的

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢

那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录

所以，意向锁的目的是为了快速判断表里是否有记录被加锁

AUTO-INC 锁

特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放

在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉

一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的

对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞

MySQL 5.1.22 InnoDB 提供 轻量级的锁来实现自增

一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁

innodb_autoinc_lock_mode 控制选择用 AUTO-INC 锁，还是轻量级的锁

0，就采用 AUTO-INC 锁，语句执行结束后才释放锁
2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放
1：
- 普通 insert 语句，自增锁在申请之后就马上释放
- 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放
innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题

行级锁

普通 select 不对记录加锁，属于快照读。如果要在查询时对记录加行锁，用锁定读 语句

//对读取的记录加共享锁
select ... lock in share mode;

//对读取的记录加独占锁
select ... for update;

这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0

共享锁（S 锁）满足读读共享，读写互斥。独占锁（X 锁）满足写写互斥、读写互斥

行级锁类型主要有三类：

Record Lock，记录锁，仅仅把一条记录锁上
Gap Lock，间隙锁，锁定一个范围，但不包含记录本身；
Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身

Record Lock

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：

当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）
当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）

当一个事务执行了下面这条语句：

begin;
select * from t_test where id = 1 for update;

就是对 t_test 表中主键 id 为 1 的这条记录加上 X 型的记录锁，这样其他事务就无法对这条记录进行修改了

当事务执行 commit 后，事务过程中生成的锁都会被释放

Gap Lock

只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的

Next-Key Lock

临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录

next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的

比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞

虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）

如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态

举个例子，假设事务 A 已经对表加了一个范围 id 为（3，5）间隙锁

当事务 A 还没提交的时候，事务 B 向该表插入一条 id = 4 的新记录，这时会判断到插入的位置已经被事务 A 加了间隙锁，于是事物 B 会生成一个插入意向锁，然后将锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），此时事务 B 就会发生阻塞，直到事务 A 提交了事务。

插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。

如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）

锁作用

保证共享资源一致性
实现事务隔离性

锁粒度(等级)

表级锁：开销小，加锁快；不会死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
行级锁：开销大，加锁慢；会出现死锁（锁是逐步获得的）；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

存储引擎各自实现锁机制，server层不感知存储引擎的锁实现

InnoDB锁模式

行锁

共享锁（S）
排他锁（X）

表锁

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁

允许行锁和表锁共存，实现多粒度锁机制

详解 MySql InnoDB 中意向锁的作用

隐式& 显式锁定

隐式锁定

事务执行过程中，使用两阶段锁协议：

随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁
锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在 ``同一时刻` 被释放

显式锁定：

select ... lock in share mode
select ... for update

乐观&悲观锁

乐观锁

select id,value,version from TABLE where id = #{id}

更新时，为防止发生冲突，需要这样操作

update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version}

失败后重试或回滚

悲观锁

直接调用数据库的相关语句

悲观锁涉及的另外两个锁概念：共享锁与排它锁

共享锁（S）：

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据（获取数据上的排他锁）

自己不一定能够修改数据，因为有可能其他的事务也对这些数据使用了 in share mode 的方式上了 S 锁

当前事务对读锁进行修改操作，很可能会造成死锁

create table innodb_lock(
id bigint primary key auto_increment,
v int
);

insert into innodb_lock (id,v) values(1,'dd');

Transaction A	Transaction B
set autocommit=0;select * from innodb_lock where id=1 lock in share mode;	set autocommit=0;select * from innodb_lock where id=1 lock in share mode;
update innodb_lock set v=2 where id=1; 等待
等待	update innodb_lock set v=2 where id=1; Deadlock found when trying to get lock; try restarting transaction
Affected rows: 1

排他锁（X)：

SELECT * FROM table_name WHERE ... FOR UPDATE

自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改

行锁

锁直接加在索引记录上面，锁住的是 key

多个事务操作同一行数据时，后来的事务：

处于阻塞等待状态，避免脏读等数据一致性问题
可以操作其他行数据，解决表锁高并发性能低的问题

# Transaction-A
set autocommit = 0;
update innodb_lock set v=1001 where id=1;
commit;

# Transaction-B
update innodb_lock set v=2001 where id=2;
update innodb_lock set v=1002 where id=1;

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁

索引失效：

explain看 key = null
like 等不确定扫描范围

劣势：开销大；加锁慢；会出现死锁
优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强
加锁方式：自动加锁
1. UPDATE、DELETE和INSERT，InnoDB自动给涉及数据集加排他锁
2. 普通SELECT，InnoDB不会加任何锁；也可以显示的加锁

间隙锁

当隔离级别降至 RC 或innodb_locks_unsafe_for_binlog=ON 关闭间隙锁

用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁

对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

例，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101

Select * from  emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁

InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，也会使用间隙锁

间隙锁目的：

防止幻读，满足隔离级别的要求

要是不用间隙锁，其他事务插入empid>100的记录，本事务如果再次执行上述select，就会发生幻读
满足恢复和复制的需要

通过 BINLOG 实现恢复和主从复制

Binlog 按事务提交先后顺序记录，恢复也是按这个顺序进行

由此可见，MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读

更新丢失

A在卡有100元钱，某一刻用户B向A转账50元（称为B操作），同时有用户C向A转账50元（称为C操作)

B操作从数据库中读取他此时的余额100，计算新的余额为100+50=150

C操作也从数据库中读取他此时的余额100，计算新的余额为100+50=150

B操作将balance=150写入数据库,之后C操作也将balance=150写入数据库

最终A的余额变为150!=正确值200

原因是B和C向A发起转账请求时，同时打开了两个数据库会话，进行了两个事务，后一个事务拿到了前一个事务的中间状态数据，导致更新丢失

常用解决思路：

加锁同步执行
update前检查数据一致性
原子更新 update account set balance=balance+50 where xx

update，delete操作会自动加排他锁，但即使加了排他锁也无法阻止select操作

而select XX for update 可以对select操作加上排他锁

所以为了防止更新丢失可以在select时加上for update加锁，这样就可以阻止其余事务的select for update (但注意无法阻止select)

在一个事务系统中，死锁是确切存在并且是不能完全避免的

InnoDB会自动检测事务死锁，立即回滚其中某个事务，并且返回一个错误。根据某种机制来选择那个最简单（代价最小）的事务来进行回滚

Next-Key Lock

行锁+范围锁(Record Lock + Gap Lock)，锁定范围，和记录本身

即 UPDATE、DELETE 不仅锁定 WHERE 条件扫描过的所有索引记录，而且会锁定相邻的键值,其他事务就不能在这个间隙修改或者插入记录,同样防幻读

RR 隔离级别下，Next-key Lock 是默认行记录锁定算法

事务

开启事务获取隐式锁，提交/回滚时释放锁，根据隔离级别自动处理锁

ACID

隔离性通过 InnoDB 锁就可以实现

原子性、一致性、持久性通过 redo 和 undo 完成

MVCC(多版本的并发控制协议)

与 MVCC 相对的，是基于锁的并发控制

MVCC 数据分多版本，各自关联一个时间戳

读只能取小于其时间戳的最大时间戳的数据；写用两阶段封锁协议

最大好处是：读不加锁，读写不冲突。极大增加并发性能

锁和多版本数据（MVCC）是 InnoDB 实现一致性读和 ISO/ANSI SQL92 隔离级别的手段

在不同的隔离级别下，InnoDB 处理 SQL 时采用的一致性读策略和需要的锁是不同的

读操作

快照读 (snapshot read)

读取的是记录的可见版本 (有可能是历史版本)，不用加锁

当前读 (current read)

读取记录最新版本，且返回记录加锁，保证其他事务不会再并发修改这条记录

每行数据后添加两个额外隐藏值(何时被创建，何时过期（或被删除）)来实现 MVCC

记录何时创建/过期实现方式，基于时间戳（Oracle），基于事务 ID（Innodb、Postgres）

事务 ID 实现中，每开启一个新事务，事务的版本号就会递增

select、insert、delete update 操作数据的规则如下（Repeatable reads 事务隔离级别下）

SELECT 时，读取创建版本号 <= 当前事务版本号并且删除版本号为空或> 当前事务版本号
INSERT 时，保存当前事务版本号为行的创建版本号
DELETE 时，保存当前事务版本号为行的删除版本号
UPDATE 时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

MVCC空间换时间，减少锁使用，大多数读不用加锁，且也能保证只会读取到符合标准的行，也只锁住必要行

MVCC 只工作在 RC 和 RR 隔离级别

快照读（snapshot read）

只读早于或等于自己事物版本号的行记录,提高读并发

普通 select 属于快照读，不加锁

select * from table where ?

不同隔离级别不同

RC 读锁定行最新快照数据

RR、Serializable 读事务开始时版本

当前读（current read）

特殊的读操作，插入 / 更新 / 删除操作，属于当前读，需要加锁

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 ?

幻读（Phantom Read）

同事务中，同样操作读两次，得到的记录数不相同

当事务不是独立执行时发生的一种现象

第一个事务对一个表中的数据进行了修改，涉及全部数据行

同时第二个事务向表中插入一行新数据

第一个事务发现表中还有没有修改的数据行，就好象发生了幻觉一样

解决办法：当前事务完成数据处理之前，其他事务不可以添加新数据

脏读（Dirty Read）

读到了另一个事务没有提交的数据

不可重复读（Non-repeatable Read）

同事务中，两次读取同一数据，得到内容不同

事务没结束时，另一事务修改同一数据。第一个事务中的两次读数据之间，由于第二个事务的修改，两次读到的的数据可能是不一样的

解决办法：如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题

简单区别不可重复读和幻读：

不可重复读：

偏向的是 update、delete 带来的问题
幻读：

insert 带来的问题

隔离级别（Isolation Level）

InnoDB 支持的 4 种隔离级别：

级别	解释
Read Uncommited	【未提交读】也就是可能读取到其他会话中未提交事务修改的数据，一般不会用。（允许脏读）
Read Committed (RC)	【已提交读】只能读取到已经提交的数据，数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的。Oracle 默认级别。（防止脏读）
Repeatable Read (RR)	【可重复读】在同一个事务内的查询都是事务开始时刻一致的。InnoDB 默认级别在 SQL 标准中，该隔离级别消除了不可重复读，但是还存在幻象读。（可防止脏读、不可重复读）
Serializable	【可串行化】完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞，会降低数据库的效率。（可防止脏读、不可重复读、幻读）

4 种隔离级别区别：

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
未提交读（Read uncommitted）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable）	不可能	不可能	不可能

二阶段锁协议（2PL）

Two-phase Locking（两阶段锁）

锁操作分加锁/解锁两阶段，且保证加锁解锁阶段不相交

加锁阶段：只加锁，不放锁

解锁阶段：只放锁，不加锁

加锁阶段：读前申请并获得 S 锁，写前申请并获得 X 锁。加锁不成功，事务等待，直到加锁成功才继续执行

事务                  加锁/解锁处理
begin；  
insert into test .....  加 insert 对应的锁
update test set...  加 update 对应的锁
delete from test ....   加 delete 对应的锁
commit;                 事务提交时，同时释放 insert、update、delete 对应的锁

InnoDB 行锁

通过给索引上的索引项加锁（理解这点很重要）来实现，Oracle通过在数据块中对相应数据行加锁来实现

InnoDB 行锁实现特点：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！

除单个 SQL 组成的事务外，锁是逐步获得的，InnoDB 中发生死锁是可能的

主动探知死锁两个方法：

两个事务相互等待超过阀值对其中一个事务进行回滚,太被动
wait-for graph 算法主动检测，加锁请求无法立即满足需要并进入等待时算法都触发

当探知到死锁时，Innodb 回滚正在等待的事物，结束死锁

高并发的 MySQL 服务器上，事务会递归检测死锁，当超过一定的深度时，性能的下降会变的不可接受。facebook 早就提出了禁止死锁检测

总结

Innodb 默认隔离级别是 RR，防止脏读、不可重复读，Next-key lock解决幻读

MVCC 好处：

普通读不加锁，提高并发性能
能保证只会读取到符合标准的行，也只锁住必要行

https://xiaolincoding.com/mysql/lock/how_to_lock.html

mysql 锁

ddatsh

全局

表级锁

表锁

元数据锁

意向锁

AUTO-INC 锁

行级锁

Record Lock

Gap Lock

Next-Key Lock

插入意向锁

锁作用

锁粒度(等级)

InnoDB锁模式

行锁

表锁

隐式& 显式 锁定

隐式锁定

显式锁定 ：

乐观&悲观锁

乐观锁

悲观锁

行锁

间隙锁

更新丢失

Next-Key Lock

事务

ACID

MVCC(多版本的并发控制协议)

读操作

快照读（snapshot read）

当前读（current read）

幻读（Phantom Read）

脏读（Dirty Read）

不可重复读（Non-repeatable Read）

隔离级别（Isolation Level）

二阶段锁协议（2PL）

InnoDB 行锁

总结

隐式& 显式锁定

显式锁定：