Mysql锁机制
作用
解决并发事务带来的问题,确保数据库中,多条工作线程并行执行时的数据安全性。
分类
- 锁粒度划分:
- 表锁:
- 全局锁:整个数据库只允许读,不允许写;
- 元数据锁/
MDL
锁:基于表的元数据加锁,加锁后不允许其他事物操作; - 意向锁:
InnoDB
中为了兼容行锁和表锁设计的; - 自增锁/
AUTO-INC
锁:为了提升自增ID并发插入性能设计的;
- 行锁:
- 记录锁/
Record
锁:就是行锁; - 间隙锁/
GAP
锁:InnoDB
中为了解决幻读问题的一种锁机制; - 临建锁/
Next-Key
锁:同时具备记录锁+间隙锁的功能;
- 记录锁/
- 页面锁:
Berkeley DB
存储引擎支持的一种锁粒度;;
- 表锁:
- 互斥性划分:
- 共享锁/S锁:不同事物间不会相互排斥;
- 排他锁/X锁:不同事物相互排斥,同时只能允许一个事物持有所;
- 共享排他锁/SX锁:
MySQL5.7
引入,主要解决SMO
(B+tree结构的变更操作)带来的问题;
- 操作类型划分:
- 读锁:查询时使用;
- 写锁:插入、删除、修改、
DDL
语句时使用;
- 加锁方式划分:
- 显式锁:编写SQL时,通过关键字,手动指定加锁粒度;
- 隐式锁:执行SQL时,数据库自动加锁;
- 锁思想划分:
- 乐观锁;
- 悲观锁;
各类锁分析
共享锁和排他锁
- 共享锁:不同事物间不会排斥(读操作),可以同时获取锁并执行;但当另一个事务尝试对具备共享锁的数据进行写操作时,会被共享锁排斥,直到持有共享锁的事物结束才能继续执行;
- 使用:通过
lock in share mode
显示加锁如:select * from test where id = 1 lock in share mode
;
- 使用:通过
- 排他锁:当一个事物获取到锁后,会排斥其他事物;
- 使用:通过
for update
显示加锁如:select * from test where id = 1 for update
; - 特殊情况:当一个事物T1在读操作时添加了锁
select * from test where id = 1 for update
,此时另一个事物T2来读取这条数据select * from test where id = 1
,可以读到数据(跟MVCC机制有关);
- 使用:通过
- 锁释放:由Mysql自己完成,在不同的隔离级别下释放时机不同;
- 读未提交:SQL语句执行完后释放锁;
- 读已提及:SQL语句执行完后释放锁;
- 可重复读:事物执行结束后释放锁;
共享排他锁:
MySQL5.7
版本新增,主要为了解决SMO
问题;
- 不会阻塞共享锁,但是会阻塞排他锁和共享排他锁;
SMO
问题:若SQL的更新操作触发了B+Tree
的叶子节点分裂或合并,那么mysql会对整个B+Tree
加排他锁,咋样会导致这张表上所有的读写操作被阻塞;- sql执行期间的三个概念:
- 读取操作:基于
B+Tree
去读取某些记录; - 乐观写入:不改变
B+Tree
的索引键; - 悲观写入:会改变
B+Tree
的索引键,并改变B+Tree
结构;
- 读取操作:基于
MySQL5.7
中读操作的执行流程:- 读取数据前,给
B+Tree
加一个共享锁; - 检索数据,并对所有走过的叶节点加一个共享锁;
- 找到目标数据叶子节点后,先加一个共享锁,然后释放b步骤上加的共享锁;
- 读取目标数据,读取完成后,释放目标叶子节点的锁;
- 读取数据前,给
MySQL5.7
中乐观写入的执行流程:- 写入前给
B+Tree
加一个共享锁; - 检索数据,并对所有走过的叶节点加一个共享锁;
- 找到目标数据叶子节点后,先加一个排他锁,然后释放b步骤上加的共享锁;
- 修改数据后,释放排他锁;
- 写入前给
MySQL5.7
中悲观写入的执行流程:- 写入前给
B+Tree
加一个共享排他锁,之后执行过程中,会阻塞其他尝试更改树结构的事物; - 检索数据,找到后对其分支加上排他锁,释放a中加的共享排他锁。
- 执行写入操作,之后释放分支上的排他锁;
- 写入前给
表锁
以表作为锁的基础,将锁加在表上;
-
InnoDB
的表锁:InnoDB
的锁机制是通过聚簇索引实现的,若能命中索引则加的是行锁,若不能则加的是表锁,如:select * from test for update
,无法命中索引,则会生成一个涵盖所有行数据的锁结构,这是一个伪表锁,但作用是相同的,锁了整张表; -
MyISAM
的表锁:需要通过语法显示加锁;-- MyISAM引擎中获取读锁(具备读-读可共享特性) LOCK TABLES `table_name` READ; -- MyISAM引擎中获取写锁(具备写-读、写-写排他特性) LOCK TABLES `table_name` WRITE; -- 查看目前库中创建过的表锁(in_use>0表示目前正在使用的表锁) SHOW OPEN TABLES WHERE in_use > 0; -- 释放已获取到的锁 UNLOCK TABLES;
-
元数据锁(
Meta Date Lock
):基于表的元数据加锁;主要保护表结构,分读锁和写锁,写锁在执行DDL语句时会获取,比如向一张表创建/删除索引、修改一个字段名称/数据类型、增加/删除字段等情况;
读锁在执行CRUD这类SQL语句时,会自动获取DML读锁(共享锁),DML读锁不互斥,可以获取多次。
-
意向锁(
Intention Lock
):意向锁是InnoDB
中为了支持多粒度的锁,为了兼容行锁、表锁而设计的;- 行锁和表锁存在的冲突:当有一个事物T1对
ID=1
的数据加了一个行锁,此时来了一个事物T2,想要获取这张表的写锁,这时因为其他事物以及获取了锁,T2无法满足独占性,不能获取锁;
所以,当T2获取表锁时,需要知道表中是否有其他事物存在操作,若采用遍历的方式,不仅效率低下而且可能存在其他事物对已经遍历过的数据加锁; - 解决:在事物T1对
ID=1
数据添加行锁前,会先加一个表级别的意向锁(读:共享意向锁、写:排他意向锁),当T2去尝试获取表锁时,会先去判断一下表上是否存在意向锁,是否与自身冲突;
- 行锁和表锁存在的冲突:当有一个事物T1对
-
自增锁(
AUTO-INC Lock
):自增锁主要负责维护并发事务下自增列(AUTO_INCREMENT
修饰)的顺序,为提升自增ID并发插入性能而设计的:-
三种插入类型:
- 普通插入:
INSERT INTO table_name(...) VALUES(...)
; - 批量插入:
INSERT ... SELECT ...
插入查询出的数据; - 混合插入:
INSERT INTO table_name(id,...) VALUES(1,...),(NULL,...),(3,...)
指定一部分ID,一部分不指定;
- 普通插入:
-
三种级别通过
innodb_autoinc_lock_mode
参数控制:-
innodb_autoinc_lock_mode = 0
:传统模式;互斥,当事物T1获取自增锁插入数据时,事物T2也要插入数据,T2需要阻塞等待;
-
innodb_autoinc_lock_mode = 1
:连续模式(mysql8.0
之前默认);普通插入时:知道需要插入多少条数据,会直接分配范围自增值,假设事物T1需要插入式条数据且当前表中最大
ID=88
,mysql会将89-98这是个自增值分给T1不要再去获取自增值锁,该用轻量级锁Mutex-Lock
防止自增值重复;批量插入和混合插入不能知道需要插入多少条需要自动分配自增ID的数据,不能使用这种预分配思想,需要获取自增锁;
-
innodb_autoinc_lock_mode = 2
:交错模式(mysql8.0
之后默认);自增列的值交错插入,比如:存在事物T1、T2,给事物T1分配「1、3、5…」给T2分配「2、4、6…」;可能会出现空隙,比如T1只插入了两条数据,5就不会被使用到;
-
-
-
全局锁 :基于整个数据库加锁,加上全局锁后整个数据库只允许读,不允许任何写操作,一般在对整库做数据备份时使用;
-- 获取全局锁的命令 FLUSH TABLES WITH READ LOCK; -- 释放全局锁的命令 UNLOCK TABLES;
行锁:
-
InnoDB
的行锁实现:InnoDB
会将表数据存储在聚簇索引中,每条行数据都会存储在树中的叶子节点上,因此行数据相互直接是“分开的”,所以可以对每一条数据上锁; -
记录锁(
Record Lock
):实际上就是行锁,锁定的是表中具体的一行数据;-
使用,前提:sql可以命中索引,否则加的是表锁;
-- 获取行级别的 共享锁 select * from test where id = 1 lock in share mode; -- 获取行级别的 排他锁 select * from test where id = 1 for update;
当使用
update
修改数据时,若where
后面的条件无法命中索引,则加的也是表锁;
-
-
间隙锁(
Gap Lock
):对行锁的一种补充,锁定的是间隙区间,可以被多个事物持有,用来解决幻读问题;- 间隙区间:假设现在有张表
test
只存在三条数据,ID分别为1、2、6
,ID从2跳到了6,那么2-6两者之间的范围被称为间隙,间隙锁则主要锁定这块区域; - 使用:当存在两个事物T1、T2,在T1事物中执行
select * from test where id = 3 loc in share mode;
,查询一条不存在的数据并加锁,这时候其实就是加了间隙锁,锁定了(2-6)
之间的区域,当T2事物想执行insert into test values(5)
时,回呗阻塞,只有当事物T1结束后,才能继续执行插入操作; - 结论:当对一个不存在的数据加锁后,默认就是锁定前后两条数据之间的区间,当其他事务再尝试向该区间插入数据时,就会陷入阻塞,只有当持有间隙锁的事务结束后,才能继续执行插入操作。
- 间隙区间:假设现在有张表
-
临建锁(
Next-Key Lock
):是一种由记录锁+间隙锁组成的锁,加锁后,既锁定左开右闭的区间,也会锁定当前行数据;一般情况当尝试对一条数据加锁时InnoDB
默认加的是临建锁; -
插入意向锁(
Insert Intention Lock
):当事务执行插入语句阻塞时,就会生成一个插入意向锁,表示当前事务想对一个区间插入数据(目前的事务处于等待插入意向锁的状态);它是一种隐式锁且不会排斥其他事物; -
行锁粒度粗化(锁膨胀):
-
内存中存储锁对象的空间满了后,为了节省内存空间,行锁会粗化为表锁;
-
当范围行写操作,由于要加的行锁较多,也可能会粗化成表锁;
-
页面锁、乐观锁和悲观锁
- 页面锁:锁住一页数据;
- 乐观锁:需自己实现,通过添加版本号
version
字段的方式实现,适用于读多写少的业务场景; - 悲观锁:手动添加
for update
等;
死锁
-
Mysql的死锁现象:
-- 主键索引为user_name SELECT * FROM `account`; +-----------+---------+ | user_name | balance | +-----------+---------+ | a | 6666666 | | b | 8888888 | +-----------+---------+ -- T1事务:a向b转账 UPDATE `account` SET balance = balance - 888 WHERE user_name = "b"; UPDATE `account` SET balance = balance + 888 WHERE user_name = "a"; -- T2事务:b向a转账 UPDATE `account` SET balance = balance - 666 WHERE user_name = "a"; UPDATE `account` SET balance = balance + 666 WHERE user_name = "b";
- T1事物执行第一条sql,扣件b的余额,此时会默认加上排他锁;
- T2事物执行第一条sql,扣件a的余额,此时页会默认加上排他锁;
- T1事物执行第二条sql,增加a的余额,但由于a的锁被T2持有,所以会陷入阻塞等待;
- T2事物执行第二条sql,增加b的余额,但由于b的锁被T1持有,所以也会陷入阻塞等待;
- 死锁出现,Mysql自动检测死锁并介入
ERROR 1213 (40001): Deadlock found when trying to get lock:try restarting transaction
,强制回滚结束一个“死锁参与者(事物)”,打破死锁僵局,让另一个死锁能继续执行;
-
如何解决:
- 两种解决死锁的方法:
- 锁超时机制:一个事物超过设定时间没有获取到锁,就会主动放弃等待;
- 外力打破僵局:外部介入,打破死锁闭环,让事物继续执行或中断;
- Mysql锁超时机制:
InnoDB
中提供了锁超时机制,可通过show variables like 'innodb_lock_wait_timeout';
查看锁超时时间,默认是50s;
- 两种解决死锁的方法:
-
Mysql死锁检测算法:
InnoDB
针对死锁问题研究了wait-for graph
算法;- 启用后,Mysql会收集两个信息:
- 锁的信息链表:目前持有每个锁的事物是哪个;
- 事物等待链表:阻塞的事物要等待的锁是哪个;
- 具体流程:
- 当一个事物需要阻塞等待锁时触发;
- 以该事物为起点,从锁的信息链表中,查找对应锁的持有者(事物)信息;
- 再根据锁的持有者信息,去事物等待链表中,查找持有者是否再等待获取其他锁;
- 若持有者在等待获取其他锁,则以持有者为起点继续查找,直到出现闭环或查找到未阻塞事物;
- 若出现闭环,则介入破坏闭环,选择一个事物进行回滚操作;回滚事物选择:
- 选择数据更改最小的事物,也就是
undo-log
日志中数据量最小的事物,若数据量相同,则会选择回滚触发死锁的事物;
- 选择数据更改最小的事物,也就是
- 开启关闭:可以通过
innodb_deadlock_detect=on|off
参数来控制是否开启死锁检测机制;
- 启用后,Mysql会收集两个信息:
-
如何避免死锁:
- 合理设置索引结构,使sql能精准定位数据,从而减小锁粒度;
- 根据业务,合理降低隔离级别,从而减小锁的限制;
- 拆分业务粒度,尽量将大事物拆分成多个小事物,缩短事物持有锁的时间;
- 将(对账、刷数据等)大事物放到系统使用量低的时候去执行;
- 尽量不手动在事物中获取排他锁;
- 当死锁发生并被mysql自行解决后,这个死锁绝对还会再次出现,需要去寻找产生死锁原因并解决;
锁状态
MySQL整体的锁状态可以通过show status like 'innodb_row_lock_%'
;命令查询;
Innodb_row_lock_current_waits
:当前正在阻塞等待锁的事务数量。
Innodb_row_lock_time
:MySQL启动到现在,所有事务总共阻塞等待的总时长。
Innodb_row_lock_time_avg
:平均每次事务阻塞等待锁时,其平均阻塞时长。
Innodb_row_lock_time_max
:MySQL启动至今,最长的一次阻塞时间。
Innodb_row_lock_waits
:MySQL启动到现在,所有事务总共阻塞等待的总次数。
内存结构:
- 锁的事物信息:记录生成锁结构的事物指针;
- 索引信息:行锁特有,记录加锁行数据属于哪个索引、哪个节点的指针;
- 锁粒度信息:表锁记录的时加锁表信息;行锁记录信息较多,有三个较为重要:
Space ID
:加锁行数据,所在表空间ID;page number
:加锁行数据,所在的页号;n_bits
:使用的比特位,对一页数据中,加了多少个锁;
- 锁类型:使用了一个
32bit
的type_mode
来表示,可拆分成lock_mode、lock_type、rec_lock_type
三部分,如:阻塞等待的行级排他临键锁结构00000000000000000000000(临键锁)1(阻塞)0010(行级锁)0011(排他锁)
:lock_mode
:使用1-4位,表示锁模式;0000/0
:表示锁结构是共享意向锁;0001/1
:表示锁结构是排他意向锁;0010/2
:表示锁结构是共享锁;0011/3
:表示锁结构是排他锁;0100/4
:表示锁结构是自增锁;
lock_type
:使用5-8位,表示锁的类型;0001
:表示是表级锁;0010
:表示是行级锁;
rec_lock_type
:使用10-32位,表示行锁具体类型;- 临键锁:高23位全位0;
- 间隙锁:第10位为1;
- 记录锁:第11位为1;
- 插入意向锁:第12位为1;
- …
is_waiting
:使用第9位,表示锁的状态是等待还是持有;- 0:表示持有状态,无需阻塞等待;
- 1:表示等待状态,需要阻塞等待;
- 其他信息:用于辅助锁机制的信息,如每一个事务和锁的持有、等待关系等,将所有的事务、锁连接起来,就可以形成死锁检测机制中的事物等待链表、锁的信息链表。
- 锁的比特位:当一张表里有3条数据时,会形成一个比特数组:
00000
,首位和末位即表示无穷小、无穷大两个间隙;当事物T1对1,3两条数据加锁了,此时比特数组会变为01010
,表示T1锁定了两条数据;
InnoDB
的锁实现:
-
通过聚簇索引实现;
-
若是基于非聚簇索引字段作为条件进行写操作时,会加两个锁,一个锁是聚簇索引中的主键,另一个是非聚簇索引中的辅助索引键,加的顺序是:先加主键锁,再加辅助锁;
但在语句执行时,判断的顺序为:先判断非聚簇索引键有没有锁,再判断聚簇索引键有没有锁,碰到锁则阻塞等待;当都无锁时,这时才会开始加锁;
-
mysql的锁合并:mysql对数据加锁,并为每一条数据生成一个锁结构,而是会将满足一定条件的行记录放入到同一个锁结构中,合并条件如下;
- 对不同记录加锁的事物是同一个;
- 需要加锁的记录在同一个页中;
- 加锁类型相同;
- 锁的等待状态相同;
-
Mysql获取锁的过程:
- 当一个事物要获取行锁时,检查内存中是否存在这条数据的锁结构;
- 存在:将对应
is_waiting
的比特位改为1,表示当前事物在阻塞等待该锁,待其他事物释放锁后,会唤醒当前阻塞事物,如何将is_waiting
改为0,继续执行sql; - 如何检查内存中是否存在这条数据的锁结构:根据锁粒度信息判断;
- 存在:将对应
- 当一个事物要获取行锁时,检查内存中是否存在这条数据的锁结构;
-
释放锁过程:一般由mysql自己完成,事物结束后会自动释放,释放时会检查内存中释放有事物正在等待获取当前释放的锁,如果有责唤醒对应事物;