MySQL锁

一条update语句

# 创建表结构
CREATE TABLE t ( 
    id INT PRIMARY KEY,
    name VARCHAR(100) 
) Engine=InnoDB CHARSET=utf8;

# 插入一条记录
INSERT INTO `test`.`t`(`id`, `name`) VALUES (1, 'GC');

# 对这条记录进行修改
UPDATE `test`.`t` SET `name` = 'XA' WHERE `id` = 1;

执行流程：

MySQL中的锁介绍

在MySQL中锁总共分为三大类：

全局锁：锁住整个数据库（database）
表级锁：锁住整张表（table）
行级锁：锁住表中具体的行（row）

按照锁的功能来说分为：共享锁和排他锁。

共享锁（S）：加了共享锁后，允许其它事物继续在当前行上继续加S锁，不允许加X锁。加锁方式：select…lock in share mode
排它锁（X）：加了排它锁后，不允许其它锁对该行数据加任何其它所，其它事物若想对当前行加锁，只能等待当前事物释放锁。加锁方式：select…for update

锁类型	共享锁S	排它锁X
共享锁 S	兼容	冲突
排他锁 X	冲突	冲突

全局锁

全局锁就对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的MDL的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

# 全局锁加锁
flush tables with read lock;

#释放全局锁
unlock tables；

或者断开加锁session的连接，自动释放全局锁。

另外，全局锁用于备份这个事情，还是很危险的。因为如果在主库上加全局锁，则整个数据库将不能写入，备份期间影响业务运行，如果在从库上加全局锁，则会导致不能执行主库同步过来的操作，造成主从延迟。

表级锁

MySQL的表级锁有四种：

表读、写锁
元数据锁（meta data lock，MDL)
意向锁 Intention Locks（InnoDB）
自增锁(AUTO-INC Locks)

查看表锁相关命令：

# MySQL 实现的表级锁定的争用状态变量
show status like 'table%';


- table_locks_immediate：产生表级锁定的次数
- table_locks_waited   ：出现表级锁定争用而发生等待的次数

表锁测试

环境准备

CREATE TABLE `mylock`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `mylock` VALUES (1, 'Lock1');
INSERT INTO `mylock` VALUES (2, 'Lock2');
INSERT INTO `mylock` VALUES (3, 'Lock3');
INSERT INTO `mylock` VALUES (4, 'Lock4');
INSERT INTO `mylock` VALUES (5, 'Lock5');

读锁测试：

写锁测试：

元数据锁

MDL是在MySQL5.5版本引入的，MDL元数据锁不需要我们用户干预，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。其实用于在对表结构进行修改的时候对表进行锁定，防止在另外一个事物里重复读的时候出现数据结构不一致的情况。

在对表进行增删改查的时候会加MDL读锁，在对表结构进行修改的时候会加MDL写锁。

元数据锁演示

意向锁

InnoDB也实现了表级锁，也就是意向锁，它是页不需要用户干预的，意向锁和行锁可以共存。

意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更新数据时，需要先检索该表是否某些记录上面有行锁。

表明“某个事务正在某些行持有了锁、或该事务准备去持有锁”
意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。
**例子：**事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表会上一把意向排它锁（IX），这时事物B如果想给user表加表锁就会被阻塞，意向锁通过这种方式实现了表锁与行锁直接共存而且满足事物的隔离级别。
共享意向锁（IS）：事物在加S锁前，要先获得IS锁。
排他意向锁（IX）：事物在加X锁前，要先获得IX锁。

意向锁的作用

在对表要进行加排它锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁）。如果表被判定有意向锁，则就知道该表当前存在行锁。

否则要遍历表中的行才知道该表有没有行锁。

意向锁和共享锁、排他锁的兼容关系

锁类型	共享锁 S	排他锁 X	意向共享锁 IS	意向排他锁 IX
共享锁 S	兼容	冲突	兼容	冲突
排他锁 X	冲突	冲突	冲突	冲突
意向共享锁 IS	兼容	冲突	兼容	兼容
兼容意向排他锁 IX	冲突	冲突	兼容	兼容

InnoDB 表存在两种表级锁，一种是LOCK TABLES语句手动指定的锁，另一种是由 InnoDB 自动添加的意向锁。

简单来说，意向锁和表锁之间只有共享锁兼容，意向锁和意向锁之间都可以兼容。意向锁的主要作用是表明某个事务正在或者即将锁定表中的数据行。

行级锁

MySQL的行级锁，是由存储引擎来实现的，这里我们说的是InnoDB的行级锁。

InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索的数据，InnoDB才使用行级锁，否则InnoDB将使用表锁。总的来说在InnoDB中行锁分为四种：

**记录锁（Record Locks）:**锁定索引中一条记录。
**间隙锁（Gap Locks）:**要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。
**临键锁（Next-Key Locks）:**是索引记录上的记录锁和在索引记录之前的间隙锁的组合（间隙锁+记录锁）。
**插入意向锁(Insert Intention Locks)：**做insert操作时添加的对记录id的锁。

InnoDB的行级锁，按照功能来说，分为两种：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

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

# 手动添加共享锁（S）：
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

# 手动添加排他锁（x）：
SELECT * FROM table_name WHERE ... FOR UPDATE

记录锁(Record Locks)

记录锁测试：

当查询的列上没有索引, 对查询语句加排他锁测试，看会不会造成锁表的情况：

可以看到session1中的测试结果，当前事物加排它锁，不影响当前事物操作增删改查。

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

# 可以查询
mysql> select * from mylock where NAME = 'Lock1' for update;
+----+-------+
| id | NAME  |
+----+-------+
|  1 | Lock1 |
+----+-------+
1 row in set (0.02 sec)

# 可以新增
mysql> INSERT INTO `test`.`mylock`(`id`, `NAME`) VALUES (100, 'Lock100');
Query OK, 1 row affected (0.00 sec)

# 可以修改
mysql> UPDATE `mylock` SET `NAME` = 'Lock1' WHERE `id` = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

# 可以删除
mysql> delete from mylock where id = 1;
Query OK, 1 row affected (0.00 sec)

# 查看当前是否有被锁住的表
# 可以看到当前mylock已经是锁表的状态
mysql> show OPEN TABLES where In_use > 0;
+----------+--------+--------+-------------+
| Database | Table  | In_use | Name_locked |
+----------+--------+--------+-------------+
| test     | mylock |      1 |           0 |
+----------+--------+--------+-------------+
1 row in set (0.05 sec)


# 查看正在锁的事物
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+--------------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+------------------------+
| lock_id            | lock_trx_id | lock_mode | lock_type | lock_table      | lock_index | lock_space | lock_page | lock_rec | lock_data              |
+--------------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+------------------------+
| 193375534:8104:3:1 | 193375534   | X         | RECORD    | `test`.`mylock` | PRIMARY    |       8104 |         3 |        1 | supremum pseudo-record |
2 rows in set (0.07 sec)

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

而在session2中，除了可以查询，其它的操作都不能做，这无疑是因为session1中的家了排他锁，而该列上又没有索引而导致了锁表的情况

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

# 可以查询
mysql> select * from mylock where NAME = 'LOCK1';
+----+-------+
| id | NAME  |
+----+-------+
|  1 | Lock1 |
+----+-------+
1 row in set (0.01 sec)

# 新增阻塞
mysql> INSERT INTO `test`.`mylock`(`id`, `NAME`) VALUES (100, 'Lock100');

# 修改阻塞
mysql> UPDATE `mylock` SET `NAME` = 'Lock1' WHERE `id` = 1;

# 删除阻塞
mysql> delete from mylock where id = 1;

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

接下来，我们给 NAME字段加上唯一索引再来进行测试

# 增加唯一索引
alter table `mylock` add unique  (`NAME` )

进行测试

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

# 给 NAME = 'LOCK1'd的数据加排它锁
mysql> select * from mylock where NAME = 'LOCK1' for update;
+----+-------+
| id | NAME  |
+----+-------+
|  1 | Lock1 |
+----+-------+
1 row in set (0.02 sec)


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

# session2

# 开启事务
mysql> being;
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# 能查询session1加排它锁的数据
mysql> select * from mylock where NAME = 'LOCK1';
+----+-------+
| id | NAME  |
+----+-------+
|  1 | Lock1 |
+----+-------+
1 row in set (0.05 sec)

# 能查询表中其它数据
mysql> select * from mylock where NAME = 'LOCK2';
+----+-------+
| id | NAME  |
+----+-------+
|  2 | Lock2 |
+----+-------+
1 row in set (0.04 sec)

# 能新增数据到mylock表中
mysql> INSERT INTO `test`.`mylock`(`id`, `NAME`) VALUES (101, 'Lock101');
Query OK, 1 row affected (0.00 sec)

# 能删除mylock表中的数据
mysql> DELETE FROM  mylock WHERE ID = 10;
Query OK, 1 row affected (0.00 sec)

# 能修改mylock中的数据
mysql> UPDATE `mylock` SET `NAME` = 'Lock3UP' WHERE `id` = 3;
Query OK, 1 row affected (0.00 sec)

# 修改session1中加锁的记录行,则会阻塞
mysql> UPDATE `mylock` SET `NAME` = 'Lock3UP' WHERE `id` = 3;

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

由此证实，在没有索引的列上进行查询时，如果加了for update排它锁，则会进行锁表操作，如果有索引，则只会对当前一行记录加锁。所以在开发中要谨慎使用锁相关的操作。

间隙锁(Gap Locks)

间隙锁 锁定的是索引记录之间的间隙、第一个索引之前的间隙或者最后一个索引之后的间隙。例如: <font color='red'>SELECT * FROM mylock WHERE id BETWEEN 1 and 10 FOR UPDATE;</font>会阻止其他事务将 1 到 10 之间的任何值插入到 c1 字段中，即使该列不存在这样的数据；因为这些值都会被锁定。

# 在做测试之前,因为我们上面刚才给 ‘NAME’ 字段加了唯一索引。先删除掉
# 不然如果使用select * 的话就会产生锁表的情况,即使我们的where 是id,但是 'NAME' 仍然带着唯一索引存在 select * 中。
drop index name on mylock

通过主键操作范围值

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

# 锁住 id 1 - 10之间的记录
mysql> select * from mylock where id between 1 and 10 for update;
+----+--------+
| id | NAME   |
+----+--------+
|  1 | Lock1  |
|  6 | Lock6  |
|  7 | Lock7  |
| 10 | Lock10 |
+----+--------+
4 rows in set (0.02 sec)


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

# session2

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

# 查询
mysql> select * from mylock;
+----+--------+
| id | NAME   |
+----+--------+
|  1 | Lock1  |
|  6 | Lock6  |
|  7 | Lock7  |
| 10 | Lock10 |
+----+--------+
4 rows in set (0.02 sec)

# 新增阻塞
# 虽然mylock表里只有4条记录,而id=2的数据当前不存在mylock表中,间隙锁锁的是一个区间,所以当在执行id=2的新增的操作时,也是会被锁住的
mysql> INSERT INTO `test`.`mylock`(`id`, `NAME`) VALUES (2, 'Lock2');
1205 - Lock wait timeout exceeded; try restarting transaction


# 但是,我们可以插入id > 10的数据,是没有问题的。因为这类数据不再session1间隙锁的范围中
mysql> INSERT INTO `test`.`mylock`(`id`, `NAME`) VALUES (17, 'Lock17');
Query OK, 1 row affected (0.00 sec)


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

接来来我们来试一下，如果锁的是记录不存在的等值查询，会发生什么

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

# 看一下表中的数据
mysql> select * from mylock;
+----+--------+
| id | NAME   |
+----+--------+
|  1 | Lock1  |
|  6 | Lock6  |
|  7 | Lock7  |
| 10 | Lock10 |
+----+--------+
4 rows in set (0.04 sec)

# 等值=9的排它锁
mysql> select * from mylock where id = 9 for update;
Empty set

# 通过执行锁信息语句可以看到,当前被加了排它锁+间隙锁,所以导致session2插入id=8的数据会被阻塞
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+--------------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| lock_id            | lock_trx_id | lock_mode | lock_type | lock_table      | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| 194106112:8104:3:9 | 194106112   | X,GAP     | RECORD    | `test`.`mylock` | PRIMARY    |       8104 |         3 |        9 | NULL      |
| 194105929:8104:3:9 | 194105929   | X,GAP     | RECORD    | `test`.`mylock` | PRIMARY    |       8104 |         3 |        9 | NULL      |
+--------------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
2 rows in set (0.04 sec)

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

# session2

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

# 阻塞
mysql> INSERT INTO `test`.`mylock`(`id`, `NAME`) VALUES (8, 'Lock8');

# 成功,因为id=12的记录不存在于表记录中,间隙锁的最大值是10,所以插入id=12的数据不会被阻塞
mysql> INSERT INTO `test`.`mylock`(`id`, `NAME`) VALUES (12, 'Lock12');
Query OK, 1 row affected (0.00 sec)

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

接下来我们继续测试一个主键大于当前表中所有的数据

# session1


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

mysql> select * from mylock;
+----+--------+
| id | NAME   |
+----+--------+
|  1 | Lock1  |
|  6 | Lock6  |
|  7 | Lock7  |
| 10 | Lock10 |
+----+--------+
4 rows in set (0.03 sec)

# 锁住一个不存在的主键
mysql> select * from mylock where id = 30 for update;
Empty set

# 查看锁信息,可以看到当前mylock表锁的类型是X, 锁的数据是supremum pseudo-record
# 当N超过表里的最大值时，两种方式都会对primary索引的 supremum pseudo-record加一个 X锁，导致其他的insert会被阻塞
# supremum pseudo-record :相当于比索引中所有值都大，但却不存在索引中，相当于最后一行之后的间隙锁
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+--------------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+------------------------+
| lock_id            | lock_trx_id | lock_mode | lock_type | lock_table      | lock_index | lock_space | lock_page | lock_rec | lock_data              |
+--------------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+------------------------+
| 194108389:8104:3:1 | 194108389   | X         | RECORD    | `test`.`mylock` | PRIMARY    |       8104 |         3 |        1 | supremum pseudo-record |
| 194107672:8104:3:1 | 194107672   | X         | RECORD    | `test`.`mylock` | PRIMARY    |       8104 |         3 |        1 | supremum pseudo-record |
+--------------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+------------------------+
2 rows in set (0.04 sec)

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

# session2

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

# 插入id=14的数据, 当前数据 < 30 
# 阻塞
mysql> INSERT INTO `test`.`mylock`(`id`, `NAME`) VALUES (14, 'Lock14');

# 插入id=40的数据, 当前数据 > 30 
# 阻塞
mysql> INSERT INTO `test`.`mylock`(`id`, `NAME`) VALUES (40, 'Lock40');

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

临键锁(Next-Key Locks)

record lock + gap lock, 左开右闭区间，例如（5,8]。
默认情况下，innodb使用next-key locks来锁定记录。select … for update
但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即
仅锁住索引本身，不是范围。

Next-Key Lock在不同的场景中会退化:

场景	退化成的锁类型
使用unique index精确匹配（=），且记录存在	Record Lock
使用unique index精确匹配（=），且记录不存在	Gap Lock
使用unique index范围匹配（ <和> ）	Record Lock + Gap Lock

# 测试环境准备

CREATE TABLE `t1_simple`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pubtime` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `pubtime_index`(`pubtime`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 121 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t1_simple
-- ----------------------------
INSERT INTO `t1_simple` VALUES (10, 1);
INSERT INTO `t1_simple` VALUES (13, 1);
INSERT INTO `t1_simple` VALUES (4, 3);
INSERT INTO `t1_simple` VALUES (11, 4);
INSERT INTO `t1_simple` VALUES (8, 5);
INSERT INTO `t1_simple` VALUES (12, 9);
INSERT INTO `t1_simple` VALUES (1, 10);
INSERT INTO `t1_simple` VALUES (100, 20);
INSERT INTO `t1_simple` VALUES (6, 100);

# session1

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

# 对pubtime=20的记录加X锁
mysql> select * from t1_simple where pubtime = 20 for update;
+-----+---------+
| id  | pubtime |
+-----+---------+
| 100 |      20 |
+-----+---------+
1 row in set (0.04 sec)

# 查看当前锁信息
# 可以明显的看到当前加的X,GAP锁,而GAP间隙锁锁的间隙可以在lock_data列中看到,锁的是100 - 6主键中对应 pubtime 的数据
# 而id = 100 的 pubtime = 20, id = 6 的 pubtime = 100, 
# 也就是说我们可以插入的 pubtime 数据要么小于20, 要么大于100, 我们去session2中试一下
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+---------------------+-------------+-----------+-----------+--------------------+---------------+------------+-----------+----------+-----------+
| lock_id             | lock_trx_id | lock_mode | lock_type | lock_table         | lock_index    | lock_space | lock_page | lock_rec | lock_data |
+---------------------+-------------+-----------+-----------+--------------------+---------------+------------+-----------+----------+-----------+
| 194477958:8190:4:10 | 194477958   | X,GAP     | RECORD    | `test`.`t1_simple` | pubtime_index |       8190 |         4 |       10 | 100, 6    |
| 194477529:8190:4:10 | 194477529   | X,GAP     | RECORD    | `test`.`t1_simple` | pubtime_index |       8190 |         4 |       10 | 100, 6    |
+---------------------+-------------+-----------+-----------+--------------------+---------------+------------+-----------+----------+-----------+
2 rows in set (0.05 sec)

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

# session2

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

# 看一下当前表中的数据
mysql> select * from t1_simple;
+-----+---------+
| id  | pubtime |
+-----+---------+
|  10 |       1 |
|  13 |       1 |
|   4 |       3 |
|  11 |       4 |
|   8 |       5 |
|  12 |       9 |
|   1 |      10 |
| 100 |      20 |
|   6 |     100 |
+-----+---------+
9 rows in set (0.03 sec)

# 阻塞。此时我们回到session1执行一下锁信息,看一下锁的情况
mysql> insert into t1_simple values (101, 21);

# 小于20,成功
mysql> insert into t1_simple values (9, 2);
Query OK, 1 row affected (0.00 sec)

# 大于100,成功
mysql> insert into t1_simple values (101, 101);
Query OK, 1 row affected (0.01 sec)

# 插入 20 - 100直接的数据25, 则阻塞
mysql> insert into t1_simple values (20, 25);

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

插入意向锁(Insert Intention Locks)

插入意向锁是一种Gap锁，不是意向锁，在insert操作时产生。
在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。
假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。
插入意向锁不会阻止任何锁，对于插入的记录会持有一个记录锁。

MSQL锁