MySQL锁

MySQL锁介绍

按照锁的粒度来说，MySQL主要包含三种类型（级别）的锁定机制：

全局锁：锁的是整个database。由MySQL的SQL layer层实现的
表级锁：锁的是某个table。由MySQL的SQL layer层实现的
行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。

按照锁的功能来说分为：共享读锁和排他写锁。
按照锁的实现方式分为：悲观锁和乐观锁（使用某一版本列或者唯一列进行逻辑控制）
表级锁和行级锁的区别：
a. 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
b. 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

MySQL锁

MySQL表级锁

表级锁介绍

由MySQL SQL Layer层实现

MySQL的表级锁有两种方式：

一种是表锁
一种是元数据锁（meta data lock，MDL）

MySQL实现的表级别锁定的争用状态变量

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 411   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 165   |
| Table_open_cache_misses    | 2     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
-- Table_locks_immediate：产生表级锁定的次数；
-- Table_locks_waited：出现表级锁定争用而发生等待的次数；

表锁介绍

表锁有两种实现方式：

表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）

手动增加表锁

-- lock table 表名称 read(write), 表名称2 read(write);
mysql> lock table t_user read;

查看表锁情况

mysql> show open tables;

image.png

删除表锁

mysql> unlock 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');

表读、写锁演示

表读锁

表读锁

session 1: DataGrid
session 2: MySQL命令行客户端

-- 1. session 1
lock table mylock read; -- 给mylock表加读锁，别的session不能修改数据（阻塞），但可以读数据
-- 2. session 1
select * from mylock; -- 可以查询
-- 3. session 1
select * from t_dep; -- 不能访问非锁定表
-- 提示：[HY000][1100] Table 't_dep' was not locked with LOCK TABLES
-- 4. session 2
select * from mylock; -- 可以查询，没有锁
-- 5. session 2
update mylock set name='x' where id=2; -- 修改阻塞，自动加行写锁
-- 6. session 1
unlock tables; -- 释放表锁
-- 7. session 2
Query OK, 1 row affected (23.17 sec)
Rows matched: 1  Changed: 1  Warnings: 0 -- 修改执行完成
-- 8. session 1
select * from t_dep; -- 可以访问

表写锁

表写锁

session 1: DataGrid
session 2: MySQL命令行客户端

-- 1. session 1
lock table mylock write; -- 给mylock表加写锁，别的session不能读数据（阻塞），但可以写数据
-- 2. session 1
select * from mylock; -- 可以查询
-- 3. session 1
select * from t_dep; -- 不能访问非锁定表
-- 提示：[HY000][1100] Table 't_dep' was not locked with LOCK TABLES
-- 4. session 1
update mylock set name='y' where id=2; -- 可以执行
-- 5. session 2
select * from mylock; -- 查询阻塞
-- 6. session 1
unlock tables; -- 释放表锁
-- 7. session 2
4 rows in set (7.57 sec) -- 查询执行完成
-- 8. session 1
select * from t_dep; -- 可以访问

MySQL元数据锁

MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。
因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

元数据锁演示

session 1: DataGrid
session 2: MySQL命令行客户端
session 3: MySQL命令行客户端

-- 1. session 1
begin; -- 开启事务
select * from mylock; -- 查询数据，加DML读锁
-- 2. session 2
alter table mylock add f int; -- 修改表结构加DML写锁，与前面的DML读锁互斥，修改阻塞
-- 3. session 3
select * from mylock; -- 查询阻塞，与session 2的写锁互斥
-- 4. session 1
commit; -- 提交事务 或者 rollback回滚，释放读锁
-- 5. session 2
Query OK, 0 rows affected (14.67 sec) -- 修改完成
Records: 0  Duplicates: 0  Warnings: 0
-- 6. session 3
4 rows in set (3 min 21.78 sec) -- 查询完成

我们可以看到 session 1 先启动，这时候会对表 mylock 加一个 MDL 读锁。由于 session 2 需要的也是 MDL 读锁，读锁之间不互斥，因此可以正常执行。
之后 session 2 会被 blocked，是因为 session 1 的 MDL 读锁还没有释放，而 session 2 需要 MDL 写锁，因此只能被阻塞。
如果只有 session 2 自己被阻塞还没什么关系，但是之后所有要在表 mylock 上新申请 MDL 读锁的请求也会被 session 2 阻塞。前面我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。
你现在应该知道了，事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

MySQL行级锁

行级锁介绍

MySQL的行级锁，是由存储引擎来实现的，利用存储引擎锁住索引项来实现的。这里我们主要讲解InnoDB的行级锁。

InnoDB的行级锁，按照锁定范围来说，分为三种：
- 记录锁（Record Locks）：锁定索引中一条记录。
- 间隙锁（Gap Locks）：要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。
- Next-Key Locks：是索引记录上的记录锁和在索引记录之前的间隙锁的组合。
InnoDB的行级锁，按照功能来说，分为两种：
- 共享(读)锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
- 排他(写)锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享写锁（不是读）和排他写锁。

对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

DELETE：删除一条数据时，先对记录加X锁，在执行删除操作。
INSERT：插入一条记录时，会先加隐式锁来保护这条新插入的纪录在本事务提交前不被别的事务访问到。

隐式锁：一个事务插入一条记录后，还未提交，这条记录会被保存到本次事务id，而其他事务如果要对这条记录加锁时，会发现事务id不对应，这时会产生X锁，所以相当于在插入一条记录时，隐式的给这条肌理加了一把瘾是X锁。
UPDATE：
- 如果被更新的列，修改前后没有导致存储空间变化，那么给记录加X锁，再直接对记录进行修改。
- 如果被更新的列，修改前后没有导致存储空间了变化，那么给记录加X锁，然后将记录删除，再insert一条新记录。

手动添加共享锁（S）：

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

手动添加排他锁（X）：

SELECT * FROM table_name WHERE ... FOR UPDATE;

InnoDB也实现了表级锁，也就是意向锁，意向锁是MySQL内部使用的，不需要用户干预。
- 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
- 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
意向锁和行锁可以共存，意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更新数据时，需要先检索该表是否某些记录上面有行锁。

意向锁、行锁兼容冲突关系

InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
Innodb所使用的行级锁定争用状态查看：

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
-- Innodb_row_lock_current_waits：当前正在等待锁定的数量；
-- Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
-- Innodb_row_lock_time_avg：每次等待所花平均时间；
-- Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间； 
-- Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

两阶段锁

传统RDBMS加锁的一个原则，就是2PL (Two-Phase Locking，二阶段锁)。相对而言，2PL比较容易理解，说的是锁操作分为两个阶段：不管同一个事务内需要在多少个数据项上加锁，那么所有的加锁操作都只能在同一个阶段完成，在这个阶段内，不允许对对已经加锁的数据项进行解锁操作，即加锁和解锁操作不能交叉执行（同一个事务内）。这一条是说在同一个事务内部的事情。

InnoDB行锁演示

行读锁

session 1: DataGrid
session 2: MySQL命令行客户端

-- 查看行锁状态
show STATUS like 'innodb_row_lock%';
-- 1. session 1
begin; -- 开启事务未提交
select * from mylock where ID=1 lock in share mode; -- 手动加id=1的行读锁，使用索引
-- 2. session 2
update mylock set name='y' where id=2; -- 未锁定该行可以修改
-- 3. session 2
update mylock set name='y' where id=1; -- 锁定该行修改阻塞
-- 4. session 1
commit; --提交事务 或者 rollback 释放读锁
-- 5. session 2
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- 锁定超时
-- 再执行update成功
update mylock set name='y' where id=1; -- 修改成功

-- 注：使用索引加行锁，未锁定的行可以访问

行读锁升级为表锁

session 1: DataGrid
session 2: MySQL命令行客户端

-- 1. session 1：
begin; -- 开启事务未提交
-- 手动加name='c'的行读锁，未使用索引
select * from mylock where name='c' lock in share mode; 
-- 2. session 2：
update mylock set name='y' where id=2; -- 修改阻塞 未用索引行锁升级为表锁 
-- 3. session 1：
commit; -- 提交事务 或者 rollback 释放读锁
-- 4. session 2：
update mylock set name='y' where id=2; -- 修改成功 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

行写锁

session 1: DataGrid
session 2: MySQL命令行客户端

-- 1. session 1：
begin; -- 开启事务未提交
-- 手动加id=1的行写锁，
select * from mylock where id=1 for update; 
-- 2. session 2：
select * from mylock where id=2; -- 可以访问
-- 3. session 2：
select * from mylock where id=1; -- 可以读 不加锁
-- 4. session 2：
select * from mylock where id=1 lock in share mode; -- 加读锁被阻塞
-- 5. session 1：
commit; -- 提交事务 或者 rollback 释放写锁 
-- 6. session 2：执行成功
-- 主键索引产生记录锁

间隙锁（Gap Lock）

session 1: DataGrid
session 2: MySQL命令行客户端
原始表数据：

原始表数据

create table news (id int, number int, primary key (id));
insert into news values(1, 2);
insert into news values(3, 4);
insert into news values(6, 5);
insert into news values(8, 5);
insert into news values(10, 5);
insert into news values(13, 11);
-- 加非唯一索引
alter table news add index idx_num(number);
-- 1. session 1
start transaction;
select * from news where number=4 for update;
-- 2. session 2
start transaction;
insert into news value(2,4); --（阻塞）
insert into news value(2,2); --（阻塞）
insert into news value(4,4); --（阻塞）
insert into news value(4,5); --（阻塞）
insert into news value(7,5); --（执行成功）
insert into news value(9,5); --（执行成功）
insert into news value(11,5); --（执行成功）
-- 注：id和number都在间隙内则阻塞。

-- 1. session 1
start transaction;
select * from news where number=13 for update; 
(select * from mylock where id>1 and id < 4 for update)
-- 2. session 2
start transaction;
insert into news value(11,5); -- (执行成功)
insert into news value(12,11); -- (执行成功)
insert into news value(14,11); -- (阻塞)
insert into news value(15,12); -- (阻塞)

-- 检索条件number=13，向左取得最靠近的值11作为左区间，向右由于没有记录因此取得无穷大作为右区间，
-- 因此，session 1的间隙锁的范围（11，无穷大）
-- 注：非主键索引产生间隙锁，主键范围

死锁

两个 session 互相等等待对方的资源释放之后，才能释放自己的资源，造成了死锁
session 1: DataGrid
session 2: MySQL命令行客户端

-- 1. session 1
begin; -- 开启事务未提交
update mylock set name='m' where id=1; -- 手动加行写锁 id=1，使用索引
-- 2. session 2
begin; -- 开启事务未提交
update mylock set name='m' where id=2; -- 手动加行写锁 id=2，使用索引
-- 3. session 1
update mylock set name='nn' where id=2; -- 加写锁被阻塞
-- 4. session 2
update mylock set name='nn' where id=1; -- 加写锁会死锁，不允许操作
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

最后编辑于：2020.12.06 09:46:18

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 218,284评论 6赞 506
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 93,115评论 3赞 395
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 164,614评论 0赞 354
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,671评论 1赞 293
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,699评论 6赞 392
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,562评论 1赞 305
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,309评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 39,223评论 0赞 276
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,668评论 1赞 314
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,859评论 3赞 336
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,981评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,705评论 5赞 347
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,310评论 3赞 330
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,904评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 33,023评论 1赞 270
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 48,146评论 3赞 370
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,933评论 2赞 355

MySQL锁

MySQL锁介绍

MySQL表级锁

表级锁介绍

表锁介绍

表锁演示

环境准备

表读、写锁演示

MySQL元数据锁

元数据锁演示

MySQL行级锁

行级锁介绍

两阶段锁

InnoDB行锁演示

行读锁

行读锁升级为表锁

行写锁

间隙锁（Gap Lock）

死锁

推荐阅读更多精彩内容