Mysql各种锁机制（全面）

一、mysql 锁介绍

锁来源

在存在并发操作的时候，必然需要一种机制来保证数据的完整性与一致性。锁就是这一技术的实现。

锁种类

根据概念分：悲观锁和乐观锁

根据粒度分：表锁、页锁、行锁，最常见的就是表锁和行锁。其中，MyISAM引擎只有表锁，而InooDB既有表锁也有行锁。

根据功能分：共享锁、排它锁（独占锁）、意向锁等。其中，共享锁被称为 S 锁。排它锁称为 X 锁。

二、MyISAM 表锁

先看一下读锁和写锁的兼容性：

当前锁模式 / 是否兼容 / 请求锁模式 读锁 写锁
读锁 是 否
写锁 是 否
博主使用的 mysql 详细信息：
版本引擎等信息 | 详细描述
——– | —– | —–
mysql 测试版本 | 5.6.49-log
表引擎 | 其中，classromm 表为 MyISAM，student 表为 InnoDB。

1、读锁
语法：lock table tablename read

会话 1：给 classroom 表加锁，就不能对 student 等其它表进行操作了，只能对加锁的表进行读操作。

mysql> lock table classroom read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from classroom;
+—-+———+
| id | cname |
+—-+———+
| 1 | php班 |
| 2 | Java班 |
+—-+———+
2 rows in set (0.00 sec)

mysql> select * from student;
ERROR 1100 (HY000): Table ‘student’ was not locked with LOCK TABLES
mysql> delete from student where id = 6;
ERROR 1100 (HY000): Table ‘student’ was not locked with LOCK TABLES
会话 2：会话 1 中给 classroom 表加锁，不影响会话 2 查询加锁的表和其它表。

mysql> select * from classroom;
+—-+———+
| id | cname |
+—-+———+
| 1 | php班 |
| 2 | Java班 |
+—-+———+
2 rows in set (0.00 sec)
会话 2：此时会话 1 虽然对 classroom 加表锁了，但是会话 2 也依然可以对 classroom 表加表锁。但是加上表锁后，会话 2 也和会话 1 一样只能对进行查询的操作了。

mysql> lock table classroom read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
ERROR 1100 (HY000): Table ‘student’ was not locked with LOCK TABLES
mysql> update classroom set cid = 6 where id = 1;
ERROR 1099 (HY000): Table ‘classroom’ was locked with a READ lock and can’t be updated
结论 1：在执行表读锁后，当前会话只能访问加锁的这个表，不能访问未加锁的表，但是非当前会话不受影响。
会话 1：对 classroom 表进行加表锁，并对 id 为 3 的数据进行删除。

mysql> lock table classroom read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from classroom;
+—-+———+
| id | cname |
+—-+———+
| 1 | php班 |
| 2 | Java班 |
| 3 | 前端 |
+—-+———+
3 rows in set (0.00 sec)

mysql> delete from classroom where id = 3;
ERROR 1099 (HY000): Table ‘classroom’ was locked with a READ lock and can’t be updated
会话 2：对 classroom 表中某行数据进行删除，但会造成阻塞。

mysql> delete from classroom where id = 3;
……等待
结论 2：在执行表读锁后，当前会话只能进行查询操作，不能进行其它操作（update、delete 等）。非当前会话可以执行其它操作，但会造成阻塞。
2、写锁：
语法：lock table tablename write

会话 1：会话 1 持有 classroom 表读锁，其它会话能持有该表的读锁，但不能持有该表的写锁。

mysql> lock table classroom read;
Query OK, 0 rows affected (0.00 sec)
会话 2：会话 2 可在持有 classroom 的表读锁，但不能持有表写锁，会造成阻塞。

mysql> lock table classroom read;
Query OK, 0 rows affected (0.00 sec)
mysql> lock table classroom write;
……等待
结论 1：当一个会话持有表读锁，其它会话可以持有表读锁，但不能持有表写锁。
会话 1：事务 1 给 classroom 加表锁，那只有事务 1 才能进行增删改查操作。

mysql> lock table classroom write;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from classroom;
+—-+——–+——+
| id | cname | cid |
+—-+——–+——+
| 1 | php班 | 7 |
| 2 | java班 | 1 |
+—-+——–+——+
2 rows in set (0.00 sec)
会话 2：会话 1 加了写锁后，会话 2 不能在对此表进行操作，但是可以对其它表进行操作。

mysql> select * from classroom;
Ctrl-C — sending “KILL QUERY 214” to server …
Ctrl-C — query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from student;
+—-+——–+——+
| id | name | age |
+—-+——–+——+
| 21 | 张无忌 | 32 |
| 22 | 周芷若 | 19 |
+—-+——–+——+
2 rows in set (0.04 sec)

mysql> update student set age = 33 where id = 21;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from student;
+—-+——–+——+
| id | name | age |
+—-+——–+——+
| 21 | 张无忌 | 33 |
| 22 | 周芷若 | 19 |
+—-+——–+——+
2 rows in set (0.00 sec)
结论 2：当一个会话持有表写锁，那么该会话只能对该表进行增删改查操作。其它会话则不能对该表进行一切操作。但是不影响其它会话对别的表进行操作。
3、总结
\ 表读锁 表写锁
当一个事务已持有表读 / 写锁，其它事务是否可对该表进行 curd 可查不可增删改 可增删该查
当一个事务已持有表读锁，其它事务能否在继续持有表读 / 写锁 能在持有表读锁 不能持有表写锁
当一个事务已持有表写锁，其它事务能否在继续持有表读 / 写锁 不能持有表读锁 不能持有表写锁
当一个事务已持有表读 / 写锁，那这个事务能否在对别的表进行操作 不能 不能

三、InnoDB 表锁（意向锁）

意向锁含义（百度百科）：

意向锁的含义是如果对一个结点加意向锁，则说明该结点的下层结点正在被加锁；对任一结点加锁时，必须先对它的上层结点加意向锁。

意向锁是有数据引擎自己维护的，用户无法手动干预，在加行级排它锁或共享锁之前，InooDB 先会判断所在数据行的数据表中是否有对应的意向锁。

InooDB 是持有行锁的，MyISAM 是没有行锁的，既然有行锁，必然就要了解一下 InooDB 下行锁和表锁之间的那兼容性。

下面做个实验：

会话 1：给 student 表中某一行数据加上共享锁，并未提交

mysql> select * from student;
+—-+————–+—–+
| id | name | age |
+—-+————–+—–+
| 1 | 光头强 | 21 |
| 5 | 吉吉国王 | 40 |
| 10 | 白雪公主 | 26 |
+—-+————–+—–+
3 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 1 lock in share mode;
+—-+———–+—–+
| id | name | age |
+—-+———–+—–+
| 1 | 光头强 | 21 |
+—-+———–+—–+
1 row in set (0.00 sec)
会话 2：给 student 表加上表级共享锁，看是否会阻塞呢？发现是能加上的。

mysql> lock table student read;
Query OK, 0 rows affected (0.00 sec)
会话 2：既然表级共享锁加不上，表级排它锁是否能加上呢？ 阻塞了……

mysql> lock table student write;
……
结论 1：当一个会话持有某个表的行级共享锁，其它会话可以获取该表的表级共享锁，但不能获取该表的表级排它锁。
继续做第二个实验：

会话 1：给 student 表某一行数据加上排它锁，并未提交

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 1 for update;
+—-+———–+—–+
| id | name | age |
+—-+———–+—–+
| 1 | 光头强 | 23 |
+—-+———–+—–+
1 row in set (0.00 sec)
会话 2：此时窗口 2 去申请 student 表的表级共享锁。出现了阻塞。

mysql> lock table student read;
……
会话 2：那去申请排它锁是否会被阻塞呢？发现也被阻塞了。

mysql> lock table student write;
……
结论 2：当一个会话持有某个表的行级排它锁，其它会话不可以获取到表级的排它锁和共享锁。
以上例子，都是讲先加行锁后，在去加表锁的情况。下面翻过来试一下，在看下先加表锁在加行锁的情况。

看下第三个实验：

会话 1：给 student 表加上表读锁。

mysql> select * from student;
+—-+————–+—–+
| id | name | age |
+—-+————–+—–+
| 1 | 光头强 | 24 |
| 5 | 吉吉国王 | 40 |
| 10 | 白雪公主 | 26 |
+—-+————–+—–+
3 rows in set (0.00 sec)

mysql> lock table student read;
Query OK, 0 rows affected (0.00 sec)
会话 2：在去看看是否能在申请行写锁，发现已经被阻塞了。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 1 for update;
……
会话 2：那再看看是否能够申请行读锁呢？发现是可以申请的

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student where id = 1 lock in share mode;
+—-+———–+—–+
| id | name | age |
+—-+———–+—–+
| 1 | 光头强 | 24 |
+—-+———–+—–+
1 row in set (0.00 sec)
结论 3：当一个会话持有某个表的表读锁，其它会事务可以在获取到行级读锁，但获取行级写锁会阻塞。
再看下第四个试验：

会话 1：给 student 表加表读锁

mysql> select * from student;
+—-+————–+—–+
| id | name | age |
+—-+————–+—–+
| 1 | 光头强 | 24 |
| 5 | 吉吉国王 | 40 |
| 10 | 白雪公主 | 26 |
+—-+————–+—–+
3 rows in set (0.00 sec)

mysql> lock table student write;
Query OK, 0 rows affected (0.00 sec)
会话 2：给 student 表加某一行数据申请行写锁，发现会被阻塞

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 1 for update;
会话 2：给 student 表某一行数据申请行读锁，依然被阻塞。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student where id = 1 lock in share mode;
……
结论 4：当一个会话持有某个表的表写锁锁，其它会事务都不可以在获取该表的行级读写锁。
总结
那以上栗子就可以说明：

\ 意向共享锁（IS） 意向排他锁（IX）
表级共享锁（S） 兼容 互斥
表级排他锁（X） 互斥 互斥
明白了以上表级与行级的兼容后再去了解意向锁会更好理解。

意向锁是表锁！当我们需要给一个表 加表锁的时候，我们需要根据意向锁去判断表中有没有数据行被锁定，以确定是否能加成功。如果意向锁是行锁，那么我们就得遍历表中所有数据行来判断。如果意向锁是表锁，则我们直接判断一次就知道表中是否有数据行被锁定了。

注意注意注意！！！这儿有个坑，我上边的测试都是mysql5.7版本，但是在mysql5.7版本以下，加了行写锁后还能再加表读锁。版本不一样导致结果不一样，这坑研究了好几天没从网上找到答案，一度让我怀疑人生，以此谨记吧

四、InnoDB 行锁

注意，InnoDB 中的行锁需要在事务中运行才生效。

1、共享锁（S 锁）

语法：lock in share mode

概念：又名读锁，对某一资源加共享锁，自身可以修改或读取该资源，其它人也能继续持有该资源的共享锁，无法持有该资源的排它锁。并只能读取，不能进行其它操作。

会话 1：给 student 表 id 为 6 的数据加上共享锁。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+—-+———–+——-+——+
| id | name | price | cid |
+—-+———–+——-+——+
| 6 | 李北京 | 50 | 6,2 |
| 7 | 王上海 | 200 | 2 |
+—-+———–+——-+——+
2 rows in set (0.00 sec)

mysql> select * from student where id = 6 lock in share mode;
+—-+———–+——-+——+
| id | name | price | cid |
+—-+———–+——-+——+
| 6 | 李北京 | 50 | 6,2 |
+—-+———–+——-+——+
1 row in set (0.00 sec)
会话 2：在去修改这条数据会一直造成阻塞，知道超时或者锁释放。

mysql> update student set name = ‘周北京’ where id = 6;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
结论 1：一个会话给一个表中的某一行加共享锁，其它会话可读不可进行其它操作，直到锁释放。
会话 1：给 student 表 id 为 6 的数据加上共享锁，看看当前会话是否能在操作别的表中的数据呢？

mysql> select * from student;
+—-+———–+——-+——+
| id | name | price | cid |
+—-+———–+——-+——+
| 6 | 赵北京 | 50 | 6,2 |
| 7 | 王上海 | 200 | 2 |
+—-+———–+——-+——+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 6 lock in share mode;
+—-+———–+——-+——+
| id | name | price | cid |
+—-+———–+——-+——+
| 6 | 赵北京 | 50 | 6,2 |
+—-+———–+——-+——+
1 row in set (0.00 sec)

mysql> select * from classroom;
+—-+———+
| id | cname |
+—-+———+
| 1 | php班 |
| 2 | Java班 |
+—-+———+
2 rows in set (0.00 sec)

mysql> update classroom set cname = ‘php一班’ where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from classroom;
+—-+———–+
| id | cname |
+—-+———–+
| 1 | php一班 |
| 2 | Java班 |
+—-+———–+
2 rows in set (0.00 sec)
结论 2：一个会话给一个表中的某一行加共享锁，不影响该会话操作其它表，以及自身的表，这与表锁不同（表锁是当前会话给该表加表锁后，那当前会话只能操作该表中的数据，不能在进行操作其它表中的数据了）
会话 1：开启事务，给 student 表中 id 为 6 的加上共享锁。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+—-+———–+——-+——+
| id | name | price | cid |
+—-+———–+——-+——+
| 6 | 孙北京 | 50 | 6,2 |
| 7 | 王上海 | 200 | 2 |
+—-+———–+——-+——+
2 rows in set (0.00 sec)
mysql> select * from student where id = 6 lock in share mode;
+—-+———–+——-+——+
| id | name | price | cid |
+—-+———–+——-+——+
| 6 | 孙北京 | 50 | 6,2 |
+—-+———–+——-+——+
1 row in set (0.00 sec)
会话 2：也给 student 表中 id 为 6 的加上共享锁。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+—-+———–+——-+——+
| id | name | price | cid |
+—-+———–+——-+——+
| 6 | 孙北京 | 50 | 6,2 |
| 7 | 王上海 | 200 | 2 |
+—-+———–+——-+——+
2 rows in set (0.00 sec)

mysql> select * from student where id = 6 lock in share mode;
+—-+———–+——-+——+
| id | name | price | cid |
+—-+———–+——-+——+
| 6 | 孙北京 | 50 | 6,2 |
+—-+———–+——-+——+
1 row in set (0.00 sec)
会话 1：去修改这条数据，会造成阻塞。

mysql> update student set name = ‘李北京’ where id = 6;
……等待
会话 2：在去修改这条数据，出现死锁。

mysql> update student set name = ‘李北京’ where id = 6;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
结论 3：当一个会话持有某行的共享锁，其它会话也可在持有某行的共享锁，但是两者同时修改这条数据的话会造成死锁。

2、排它锁（X 锁）

语法：for update

概念：又名写锁，对某一资源加排它锁，自身可以修改或读取该资源，其它会话不能继续持有该资源的共享锁和排它锁。并只能对加锁数据进行读取，不能进行其它操作。

排他锁的申请前提：没有线程对该结果集中的任何行数据使用排他锁或共享锁，否则申请会阻塞
for update 及 lock in share mode 仅适用于 InnoDB，且必须在事务块 (BEGIN/COMMIT) 中才能生效，在进行事务操作时，通过 for update 语句，MySQL 会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞，排他锁包含 行锁、表锁

行排它锁可不是加上以后其它事务就不能查询该行数据，只是其它事务则不能再去给该行加其它的锁。mysql InnoDB 引擎默认的修改数据语句，update,delete,insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型，不管是行共享锁还是行排它锁都能够进行查询的，因为普通查询没有任何锁机制。

会话 1：给 student 表 id 为 6 的数据加上排它锁。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+—-+———–+——-+——+
| id | name | price | cid |
+—-+———–+——-+——+
| 6 | 李北京 | 50 | 6,2 |
| 7 | 王上海 | 200 | 2 |
+—-+———–+——-+——+
2 rows in set (0.00 sec)

mysql> select * from student where id = 6 for update;
+—-+———–+——-+——+
| id | name | price | cid |
+—-+———–+——-+——+
| 6 | 李北京 | 50 | 6,2 |
+—-+———–+——-+——+
1 row in set (0.00 sec)
会话 2：会话 1 加上排它锁后，会话 2 是否能够读取加锁数据呢？答案是可以的！

mysql> select * from student where id = 6;
+—-+———–+——-+——+
| id | name | price | cid |
+—-+———–+——-+——+
| 6 | 李北京 | 50 | 6,2 |
+—-+———–+——-+——+
1 row in set (0.00 sec)
会话 2：会话 1 加上排它锁后，会话 2 是否还能在继续持有该行的排它锁呢？答案是被阻塞了。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 6 for update;
……等待
会话 2：会话 1 加上排它锁后，会话 2 是否还能在继续持有该行的共享锁呢？也会被阻塞。

mysql> select * from student where id = 6 lock in share mode;
……等待
会话 2：会话 1 加上排它锁后，会话 2 是否还能够修改这条数据呢？答案是肯定不行的！会造成锁等待，直到资源释放。

mysql> update student set name = ‘吴北京’ where id = 6;
……等待
结论 1：当一个会话持有某行的排它锁，其它会话则不能在修改数据以及持有改行的共享锁及排它锁。会造成阻塞。
3、总结
\ 行读锁 行写锁
加上行读 / 写锁后，其它事务能删改这条数据吗 会阻塞 会阻塞
加上行读 / 写锁后，其它事务能读取这条数据吗 能 能
加上行读锁后，其它事务能在去持有行 / 读锁吗 能 不能
加上行写锁后，其它事务能在去持有行 / 读锁吗 不能 不能
四、锁的算法（行锁）
1、记录锁：
窗口 1：直接锁住 id 为 1 的记录

mysql> select * from student;
+—-+——–+
| id | name |
+—-+——–+
| 1 | 小王 |
| 2 | 李 |
| 3 | 张 |
+—-+——–+
3 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 1 for update;
+—-+——–+
| id | name |
+—-+——–+
| 1 | 小王 |
+—-+——–+
1 row in set (0.00 sec)
窗口 2：

则不能对 id 为 1 的记录进行修加锁以及增删改操作，但是可以查出

mysql> select * from student where id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

3、间隙锁：

间隙锁（Gap Lock）是 Innodb 在可重复读提交下为了解决幻读问题时引入的锁机制。

4、临键锁

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间，解决幻读问题。