MySQL：锁

后端 MySQL

发布时间 : 2024-09-25 00:00

字数:6.2k 阅读 :

行级锁

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：行锁（Record Lock）、间隙锁（Gap Lock）和临键锁（Next-Key Lock）

行锁（Record Lock）

介绍

行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在 RC、RR隔离级别下都支持。比如我们对id为34的一行数据进行加锁，那么其他事物就不可以对34进行update和delete。

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

InnoDB实现了以下两种类型的行锁：

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

两种行锁的兼容情况如下：

常见的SQL语句，在执行时，所加的行锁如下：

SQL	行锁类型	说明
INSERT …	排它锁	自动加锁
UPDATE …	排它锁	自动加锁
DELETE …	排它锁	自动加锁
SELECT（正常）	不加任何锁
SELECT … LOCK IN SHARE MODE	共享锁	需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT … FOR UPDATE	排它锁	需要手动在SELECT之后加FOR UPDATE

查看及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

演示

普通的select语句，执行时，不会加锁：

-- 窗口A
mysql> select * from stu where id=1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | tom  |   1 |
+----+------+-----+
1 row in set (0.00 sec)

-- 窗口B
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.02 sec)

select...lock in share mode，加共享锁，共享锁与共享锁之间兼容。

-- 窗口A
mysql> begin; # 发起事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu where id=1 lock in share mode; # 加共享锁
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | tom  |   1 |
+----+------+-----+
1 row in set (0.00 sec)
-- ######################################################################
-- 窗口C ： 
-- lock_type=TABLE 表示 表级锁
-- lock_type=RECORD 表示 行级锁
--    lock_mode=S 表示 共享锁
--    lock_mode=REC_NOT_GAP 表示 没有间隙
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| temp          | stu         | NULL       | TABLE     | IS            | NULL      |
| temp          | stu         | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)
-- ######################################################################
-- 窗口B：
-- 对于不加锁的select和加共享锁的select都可以执行，操作id=1的数据
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu where id=1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | tom  |   1 |
+----+------+-----+
1 row in set (0.00 sec)

mysql> select * from stu where id=1 lock in share mode;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | tom  |   1 |
+----+------+-----+
1 row in set (0.00 sec)

共享锁与排他锁之间互斥

-- 窗口1：对id=1的数据加共享锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu where id=1 lock in share mode;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | tom  |   1 |
+----+------+-----+
1 row in set (0.00 sec)

###############################################################

-- 窗口2：修改id=1的数据（带着排它锁）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update stu set name = 'jerry' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

############################################################################

-- 要等到窗口1的数据commit之后，窗口2才能修改
mysql> commit; -- 窗口1 commit之后解锁
Query OK, 0 rows affected (0.00 sec)

-- 窗口2 的数据才能修改，否则超时
mysql> update stu set name = 'jerry' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

排它锁与排他锁之间互斥

-- 窗口1 修改id=1 的数据
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update stu set name = 'jerry2' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 窗口3 查看锁的情况
-- lock_type=RECORD， lock_mode=X 表示使用了排它锁，锁主的数据 lock_data=1
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| temp          | stu         | NULL       | TABLE     | IX            | NULL      |
| temp          | stu         | PRIMARY    | RECORD    | X,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+

-- 窗口2 修改id=1的数据
mysql> update stu set name = 'marry' where id=1;
# 光标一直闪烁，表示sql被阻塞

-- 窗口1 提交事务，释放锁
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

-- 窗口2 修改才能成功
mysql> update stu set name = 'marry' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

无索引行锁升级为表锁

-- 窗口1：对name=marry的数据进行修改，也就是id=1的数据
mysql> select * from stu;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | marry |   1 |
|  3 | cat   |   3 |
|  8 | rose  |   8 |
| 11 | jetty |  11 |
| 19 | lily  |  19 |
| 25 | luci  |  25 |
+----+-------+-----+
6 rows in set (0.00 sec)

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

mysql> update stu set name='bob' where name='marry';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

###################################################################

-- 窗口2 ：修改id=2的数据
mysql> update stu set name = 'xiexie' where id=2;
# 光标一直闪烁，表示sql被阻塞

#################################################################

-- 窗口3：查询数据库锁的情况———— 发现整张表都被锁了 lock_type=RECORD，lock_mode=X
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+------------------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data              |
+---------------+-------------+------------+-----------+-----------+------------------------+
| temp          | stu         | NULL       | TABLE     | IX        | NULL                   |
| temp          | stu         | PRIMARY    | RECORD    | X         | supremum pseudo-record |
| temp          | stu         | PRIMARY    | RECORD    | X         | 3                      |
| temp          | stu         | PRIMARY    | RECORD    | X         | 8                      |
| temp          | stu         | PRIMARY    | RECORD    | X         | 11                     |
| temp          | stu         | PRIMARY    | RECORD    | X         | 19                     |
| temp          | stu         | PRIMARY    | RECORD    | X         | 25                     |
| temp          | stu         | PRIMARY    | RECORD    | X         | 1                      |
+---------------+-------------+------------+-----------+-----------+------------------------+
8 rows in set (0.01 sec)

##################################################################################################

-- 窗口1 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

###################################################################################################

-- 窗口2 : 修改成功
mysql> update stu set name = 'xiexie' where id=2;
Query OK, 1 row affected (9.17 sec)
Rows matched: 1  Changed: 1  Warnings: 0

##################################################################################################

-- 窗口3 查看锁的情况，在窗口1提交事务之后，都解锁了
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.00 sec)

之所以这样是因为，InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。如果想优化，可以对上面的name字段添加索引

间隙锁（Gap Lock）和临键锁（Next-Key Lock）

介绍

间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

所谓间隙就是两个数之间的范围，比如（18, 29) 又比如(29,34)，不包含两边的值。这样就无法再把数据insert进入这个间隙了。

临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

比如，给id=34的锁加临键锁，那么id=34的会加锁，它之后的间隙（如(29,34)）都会加锁

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁。
索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock 退化为间隙锁。
索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

演示

索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁。

-- 窗口1 查询数据
mysql> select * from stu;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | xiexie |   1 |
|  3 | cat    |   3 |
|  8 | rose   |   8 |
| 11 | jetty  |  11 |
| 19 | lily   |  19 |
| 25 | luci   |  25 |
+----+--------+-----+
6 rows in set (0.00 sec)

############################################################

-- 窗口1 开启事务，更新id=10的数据
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update stu set age =10 where id = 10;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0


#############################################################################

-- 窗口3：查看数据库锁的情况
-- lock_mode=GAP 表示开启了间隙锁
-- lock_data = 11 表示11之后的间隙{(8,11)}的无法插入新的值
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+-----------+-----------+
| temp          | stu         | NULL       | TABLE     | IX        | NULL      |
| temp          | stu         | PRIMARY    | RECORD    | X,GAP     | 11        |
+---------------+-------------+------------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

##########################################################################################

-- 窗口2 尝试插入间隙(8,11)失败
mysql> insert into stu value(9,'ming',11);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

-- 窗口2 但是插入间隙(3,8)成功
mysql> insert into stu value(5,'ming',11);
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | xiexie |   1 |
|  3 | cat    |   3 |
|  5 | ming   |  11 |
|  8 | rose   |   8 |
| 11 | jetty  |  11 |
| 19 | lily   |  19 |
| 25 | luci   |  25 |
+----+--------+-----+
7 rows in set (0.00 sec)

索引上的等值查询(非唯一普通索引)，向右遍历（向数值越大的方向）时最后一个值不满足查询需求时，会对该值加临键锁，对被搜索的值也加临键锁

分析一下：

我们知道InnoDB的B+树索引，叶子节点是有序的双向链表。假如，我们要根据这个二级索引查询值为18的数据，并加上共享锁，我们是只锁定18这一行就可以了吗？并不是，因为是非唯一索引，这个结构中可能有多个18的存在，所以，在加锁时会继续往后找，找到一个不满足条件的值（当前案例中也就是29）。此时会对18加临键锁，并对29之前的间隙加锁。

此时会对18加临键锁：18这行数据会加锁，(16,18)的区间会有间隙锁

·

-- 窗口3 ：查询stu表的信息
mysql> select * from stu order by age;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | xiexie |   1 |
|  3 | cat    |   3 |
|  8 | rose   |   8 |
|  5 | ming   |  11 |
| 11 | jetty  |  11 |
| 19 | lily   |  19 |
| 25 | luci   |  25 |
+----+--------+-----+
7 rows in set (0.00 sec)

-- 窗口3 给age创建索引
mysql> create index idx_stu_age on stu(age);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

######################################################################

-- 窗口1：开启事务，用共享锁查询age=11的数据
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu where age=11 lock in share mode;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  5 | ming  |  11 |
| 11 | jetty |  11 |
+----+-------+-----+
2 rows in set (0.01 sec)

###############################################################################
-- 窗口3：查看锁的情况
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+-------------+-----------+---------------+-----------+
| object_schema | object_name | index_name  | lock_type | lock_mode     | lock_data |
+---------------+-------------+-------------+-----------+---------------+-----------+
| temp          | stu         | NULL        | TABLE     | IS            | NULL      |
| temp          | stu         | idx_stu_age | RECORD    | S             | 11, 5     |
| temp          | stu         | idx_stu_age | RECORD    | S             | 11, 11    |
| temp          | stu         | PRIMARY     | RECORD    | S,REC_NOT_GAP | 11        |
| temp          | stu         | PRIMARY     | RECORD    | S,REC_NOT_GAP | 5         |
| temp          | stu         | idx_stu_age | RECORD    | S,GAP         | 19, 19    |
+---------------+-------------+-------------+-----------+---------------+-----------+
6 rows in set (0.00 sec)
-- index_name=idx_stu_age的情况：变成了临建锁
-- -- | RECORD    | S             | 11, 11    | 用共享锁锁住了 age =11 id=11的数据
-- --  RECORD    | S             | 11, 5     |用共享锁锁住了 age =11 id=5的数据
-- -- | RECORD    | S,GAP         | 19, 19    | 表示锁住了age=19，id=19 之前的间隙（GAP 间隙）

#####################################################################################################

-- 窗口2 
-- 向 age为（11,19）的区间插入一条数据：无法插入
mysql> insert into stu value(2,'hong',18);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 向age为（8,11）的区间插入一条数据：无法插入
mysql> insert into stu value(30,'bai',9);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 向age为（3,8）的区间插入一条数据：成功插入
mysql> insert into stu value(30,'bai',7);
Query OK, 1 row affected (0.00 sec)
-- 修改age=11的数据，无法修改
mysql> update stu set age = 12 where age=11;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 修改id=11的数据，无法修改
mysql> update stu set age = 12 where id=11;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 修改id=19的数据，无法修改
mysql> update stu set age = 12 where id=19;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

索引上的范围查询(唯一索引)会访问到不满足条件的第一个值为止。

-- 窗口1 查询id>=19的数据，并加共享锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu where id>=19 lock in share mode;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 19 | lily |  19 |
| 25 | luci |  25 |
| 30 | bai  |   7 |
+----+------+-----+
3 rows in set (0.00 sec)

#######################################################################################################

-- 窗口3 查询锁的情况
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+------------------------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data              |
+---------------+-------------+------------+-----------+---------------+------------------------+
| temp          | stu         | NULL       | TABLE     | IS            | NULL                   |
| temp          | stu         | PRIMARY    | RECORD    | S,REC_NOT_GAP | 19                     |
| temp          | stu         | PRIMARY    | RECORD    | S             | supremum pseudo-record |
| temp          | stu         | PRIMARY    | RECORD    | S             | 25                     |
| temp          | stu         | PRIMARY    | RECORD    | S             | 30                     |
+---------------+-------------+------------+-----------+---------------+------------------------+
5 rows in set (0.00 sec)

查询的条件为id>=19，并添加共享锁。此时我们可以根据数据库表中现有的数据，将数据分为三个部分：

[19]，(19,25]，(25,30]，(30, supremum pseudo-record]–>(30,+∞]

所以数据库数据在加锁是，就是将19加了行锁，25的临键锁，30的临键锁和正无穷的键锁

-- 窗口2 测试各个区间的锁的情况
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into stu value(22,'hei',22);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> update stu set age = 12 where id=19;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into stu value(100,'hei',22);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> update stu set age = 12 where id=25;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into stu value(18,'hei',22);
Query OK, 1 row affected (0.00 sec)

表级锁

对于表级锁，主要分为以下三类：表锁、元数据锁（meta data lock，MDL）、意向锁

表锁

对于表锁，分为两类：表共享读锁（read lock）、表独占写锁（write lock）

语法：

加锁：lock tables 表名... read/write
释放锁：unlock tables / 客户端断开连接

表共享读锁（read lock）

读锁的特点：

左侧为客户端一，对指定表加了读锁，不会影响右侧客户端二的读，但是会阻塞右侧客户端的写。

-- 客户端一：加读锁
mysql> lock tables stu read;
Query OK, 0 rows affected (0.00 sec)

############################################################

-- 客户端二 ： 可以读取数据
mysql> select * from stu;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | xiexie |   1 |
|  3 | cat    |   3 |
|  5 | ming   |  11 |
|  6 | hong   |   1 |
|  7 | hong   |  20 |
|  8 | rose   |   8 |
| 11 | jetty  |  11 |
| 18 | hei    |  22 |
| 19 | lily   |  19 |
| 25 | luci   |  25 |
| 30 | bai    |   7 |
+----+--------+-----+
11 rows in set (0.00 sec)

-- 客户端二：不可以新增修改和删除数据
mysql> delete from stu where id =25;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

表独占写锁（write lock）

左侧为客户端一，对指定表加了写锁，会阻塞右侧客户端的读和写。

-- 客户端一：加读锁
mysql> lock tables stu write;
Query OK, 0 rows affected (0.00 sec)


-- 客户端二：读操作和写操作都会阻塞
mysql> select * from stu;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> delete from stu where id =25;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

元数据锁

meta data lock , 元数据锁，简写MDL。

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

这里的元数据，大家可以简单理解为就是一张表的表结构。也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL共享锁（读锁）；当对表结构进行变更操作的时候，加MDL排它锁（写锁）。

共享锁（读锁）：这里的读锁是相对表的结构来说的，增删改查相当于读取表结构
排它锁（写锁）：这里的读锁是相对表的结构来说的，alter语句相当于修改表结构

查看元数据所的情况

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;

常见的SQL操作时，所添加的元数据锁：

对应SQL	锁类型	说明
lock tables xxx read / write	SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select 、select … lock in share mode	SHARED_READ 读锁(共享)	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
insert 、update、delete、select … for update	SHARED_WRITE 写锁(排他)	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
alter table …	EXCLUSIVE	与其他的MDL都互斥

当执行SELECT、INSERT、UPDATE、DELETE等语句时，添加的是元数据共享锁（SHARED_READ /SHARED_WRITE），之间是兼容的。

当执行SELECT语句时，添加的是元数据共享锁（SHARED_READ），会阻塞元数据排他锁（EXCLUSIVE），之间是互斥的。

-- 窗口1 开启使用，执行一条查询语句
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | xiexie |   1 |
|  3 | cat    |   3 |
|  5 | ming   |  11 |
|  6 | hong   |   1 |
|  7 | hong   |  20 |
|  8 | rose   |   8 |
| 11 | jetty  |  11 |
| 18 | hei    |  22 |
| 19 | lily   |  19 |
| 25 | luci   |  25 |
| 30 | bai    |   7 |
+----+--------+-----+
11 rows in set (0.00 sec)

################################################################

-- 窗口3 查看元数据锁的情况，有一条 共享读锁 lock_type=SHARED_READ
mysql> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;
+-------------+--------------------+----------------+-------------+---------------+
| object_type | object_schema      | object_name    | lock_type   | lock_duration |
+-------------+--------------------+----------------+-------------+---------------+
| TABLE       | temp               | stu            | SHARED_READ | TRANSACTION   |
| TABLE       | performance_schema | metadata_locks | SHARED_READ | TRANSACTION   |
+-------------+--------------------+----------------+-------------+---------------+

###########################################################################

-- 窗口1 修改一条数据
mysql> update stu set age = 10 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

###############################################################################

-- 窗口3 查看元数据锁的情况，有一条共享写锁
mysql> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;
+-------------+--------------------+----------------+--------------+---------------+
| object_type | object_schema      | object_name    | lock_type    | lock_duration |
+-------------+--------------------+----------------+--------------+---------------+
| TABLE       | temp               | stu            | SHARED_READ  | TRANSACTION   |
| TABLE       | temp               | stu            | SHARED_WRITE | TRANSACTION   |
| TABLE       | performance_schema | metadata_locks | SHARED_READ  | TRANSACTION   |
+-------------+--------------------+----------------+--------------+---------------+
3 rows in set (0.01 sec)

####################################################################################################

-- 窗口2 删除一条数据，删除成功，又新增一条共享写锁
mysql> delete from stu where id = 30;
Query OK, 1 row affected (0.00 sec)
-- 窗口 3
mysql> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;
+-------------+--------------------+----------------+--------------+---------------+
| object_type | object_schema      | object_name    | lock_type    | lock_duration |
+-------------+--------------------+----------------+--------------+---------------+
| TABLE       | temp               | stu            | SHARED_READ  | TRANSACTION   |
| TABLE       | temp               | stu            | SHARED_WRITE | TRANSACTION   |
| TABLE       | temp               | stu            | SHARED_WRITE | TRANSACTION   |
| TABLE       | performance_schema | metadata_locks | SHARED_READ  | TRANSACTION   |
+-------------+--------------------+----------------+--------------+---------------+

##############################################################################################

-- 窗口2 修改表结构，无法修改，会被阻塞
mysql> alter table stu add column gender int;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

意向锁

介绍

为什么需要意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

假如没有意向锁：

客户端一对表加了行锁后，客户端二如何给表加表锁呢，来通过示意图简单分析一下：

首先客户端一，开启一个事务，然后执行DML操作，在执行DML语句时，会对涉及到的行加行锁。

当客户端二，想对这张表加表锁时（lock tables xxx read/write），会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就会从第一行数据，检查到最后一行数据，效率较低。

有了意向锁之后 :

客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。

而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。

分类

意向共享锁(IS)：由语句select ... lock in share mode添加。与表锁共享锁(read)兼容，与表锁排他锁(write)互斥。

意向排他锁(IX)：由insert、update、delete、select...for update添加。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

查看意向锁及行锁的加锁情况

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

演示

意向共享锁与表的读锁是兼容的

-- 客户端一：添加共享锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu where id=1 lock in share mode;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | xiexie |  10 |
+----+--------+-----+
1 row in set (0.00 sec)

-- 这里 lock_mode=IS 就是意向共享锁
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| temp          | stu         | NULL       | TABLE     | IS            | NULL      |
| temp          | stu         | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+

###############################################################################################

-- 客户端二 添加表所成功
mysql> lock tables stu read;
Query OK, 0 rows affected (0.00 sec)

###############################################################################################

-- 客户端二：添加读锁被阻塞
mysql> lock tables stu write;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

意向排他锁与表读锁、写锁都是互斥的

-- 客户端一：添加意向排它锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update stu set age = 14 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- lock_mode= IX 意向排他锁
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| temp          | stu         | NULL       | TABLE     | IX            | NULL      |
| temp          | stu         | PRIMARY    | RECORD    | X,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)

######################################################################################################

-- 客户端二：添加表读锁和表写锁都被阻塞
mysql> lock tables stu read;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> lock tables stu write;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

全库逻辑备份加全局锁的原因

我们一起先来分析一下不加全局锁，可能存在的问题。

假设在数据库中存在这样三张表: tb_stock 库存表，tb_order 订单表，tb_orderlog 订单日志表。

在进行数据备份时，先备份了tb_stock库存表
然后接下来，在业务系统中，执行了下单操作，扣减库存，生成订单（更新tb_stock表，插入tb_order表）。
然后再执行备份tb_order表的逻辑。
业务中执行插入订单日志操作
最后，又备份了tb_orderlog表
此时备份出来的数据，是存在问题的。因为备份出来的数据，tb_stock表与tb_order表的数据不一致(有最新操作的订单信息,但是库存数没减)。

再来分析一下加了全局锁后的情况

对数据库进行进行逻辑备份之前，先对整个数据库加上全局锁，一旦加了全局锁之后，其他的DDL、DML全部都处于阻塞状态，但是可以执行DQL语句，也就是处于只读状态，而数据备份就是查询操作。那么数据在进行逻辑备份的过程中，数据库中的数据就是不会发生变化的，这样就保证了数据的一致性和完整性。

DDL（Data Definition Language）语句： 数据定义语言，主要是进行定义/改变表的结构、数据类型、表之间的链接等操作。常用的语句关键字有 CREATE、DROP、ALTER 等。

DML（Data Manipulation Language）语句: 数据操纵语言，主要是对数据进行增加、删除、修改操作。常用的语句关键字有 INSERT、UPDATE、DELETE 等。

DQL（Data Query Language）语句：数据查询语言，主要是对数据进行查询操作。常用关键字有 SELECT、FROM、WHERE 等。

DCL（Data Control Language）语句： 数据控制语言，主要是用来设置/更改数据库用户权限。常用关键字有 GRANT、REVOKE 等。一般人员很少用到DCL语句。

语法

# 加全局锁
flush tables with read lock ;
# 数据备份
# # 备份整个数据库
mysqldump -u username -p your_database > your_backup_file.sql
# # 备份数据库中的表
mysqldump -u username -p your_database table1 table2 > your_backup_file.sql
# 释放锁
unlock tables ;

数据备份演示

第一步：给数据库加全局锁

-- 客户端一：加全局锁
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.07 sec)

-- 客户端二：可以查询
mysql> select * from stu;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | xiexie |  14 |
|  3 | cat    |   3 |
|  5 | ming   |  11 |
|  6 | hong   |   1 |
|  7 | hong   |  20 |
|  8 | rose   |   8 |
| 11 | jetty  |  11 |
| 18 | hei    |  22 |
| 19 | lily   |  19 |
| 25 | luci   |  25 |
+----+--------+-----+
10 rows in set (0.00 sec)

-- 客户端二：但是不能DDL或者DML
mysql> alter table stu add column gender int;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> delete from stu where id = 30;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

第二步：退出MySQL，在控制台进行备份

如果在MySQL命令行里面会报错误

mysql> mysqldump -uroot -p1234 temp stu > stu.sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump -u root -p 1234 temp stu > stu.sql' at line 1

要在命令行里面运行

[root@centos01 ~]# mysqldump -uroot -p1234 temp stu > stu.sql;
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@centos01 ~]# ll
总用量 32
-rw-r--r--  1 root root 18623 11月 17 2021 03.jpg
-rw-------. 1 root root  1246 9月  15 2021 anaconda-ks.cfg
drwxr-xr-x  3 root root    18 11月 17 2021 docker-volumn
-rw-r--r--  1 root root    45 11月 18 2021 index.html
-rw-r--r--  1 root root     0 9月  25 16:24 mysqldump
-rw-r--r--  1 root root  2073 9月  25 16:25 stu.sql

释放锁

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

全局锁特点

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。

如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数 –single-transaction 参数来完成不加锁的一致性数据备份。

mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 1909773034@qq.com