【SQL】MySQL进阶4：锁机制

原创 已于 2025-04-02 08:27:52 修改 · 787 阅读 · 27 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#sql #mysql #数据库

于 2025-03-02 11:46:57 首次发布

文章目录


为保证并发访问数据的一致性，需要使用锁机制。可以参考操作系统进程同步相关的知识。
MySQL锁的类型：全局锁，表级锁，行级锁。

1. 全局锁

保证备份数据库期间数据不被业务修改，避免数据不一致。给数据库加上全局锁后，只允许读操作，不允许更新和删除操作（阻塞直到全局锁被释放，必须由加锁的客户端释放才行）。

1.1 全局锁的约束

-- client1：加全局锁
use test;
flush tables with read lock;
select * from Customers;  -- 查询操作正常
update Customers set cust_email='222@xxx.com' where cust_city = 'Chicago';  -- 报错：操作和读锁冲突

-- client2：查询正常
use test;
select * from Customers;  -- 查询操作正常
update Customers set cust_email='222@xxx.com' where cust_city = 'Chicago';  -- 阻塞：等待全局锁被释放

-- client1：释放锁
unlock tables;  -- 释放后，client2的阻塞更新操作顺利执行。

1.2 使用全局锁进行备份

-- mysql client session1：加全局锁
use test;
flush tables with read lock;

-- 备份，注意：要退出mysql cli，在容器的命令行中执行
mysqldump -uroot -p test > test.sql

-- mysql client session1: 备份完毕释放锁
unlock tablesl;

1.2 在不加锁同时保证数据一致性的情况下进行备份

# innodb提供
mysqldump -uroot -p --single-transaction test > test.sql

当使用 --single-transaction 参数时，mysqldump 会在开始备份之前启动一个事务。在 InnoDB 存储引擎中，事务具有隔离性，通过设置合适的隔离级别（通常是可重复读隔离级别），可以保证在事务执行期间，读取的数据是事务开始时的快照。·

1.3 补充

1.3.1 导入备份数据

# 命令行导入，注意要先创建数据库test
mysql -u 用户名 -p test < test.sql

# mysql cli中导入
use test;
.\ /path/to/test.sql;
source /path/to/test.sql;

2. 表级锁

2.1 表锁

2.1.1 表共享锁（read lock）

对表添加读锁，多个客户端只读不能修改和删除表数据。

-- client1: 添加读锁
lock tables Customers read;
select * from Customers;  -- 查询操作正常
update Customers set cust_email='222@xxx.com' where cust_city = 'Chicago';  -- 报错：操作和读锁冲突

-- client2: 其他客户端读正常，写受限
select * from Customers;  -- 查询操作正常
update Customers set cust_email='222@xxx.com' where cust_city = 'Chicago';  -- 阻塞，直到读锁被client1释放


-- client1：释放读锁：执行unlock tables；断开加锁的连接
unlock tables;  -- 释放后，阻塞的更新操作继续执行。

2.1.2 表独占锁（write lock）

客户端A加写锁，则本客户端可以读写表数据，其他客户端不能读，也不能写。表独占锁只能被一个客户端持有，可以看成一个互斥访问的资源。

-- cleint1: 加写速
lock tables users Customers write;
select * from Customers;  -- 查询操作正常
update Customers set cust_email='222@xxx.com' where cust_city = 'Chicago';  -- 更新操作正常

-- client2: 其他客户端读写受限
select * from Customers;  -- 阻塞：直到写锁被持有者释放才能执行
update Customers set cust_email='222@xxx.com' where cust_city = 'Chicago';  -- 阻塞：直到写锁被持有者释放才能执行

-- client1: 释放写锁
unlock tables;  -- 释放后，client2中阻塞的操作顺利执行。

2.2 元数据锁

  • Metadata Data Lock（MDL），主要用于限制事务之间DML和DDL之间的冲突来维护数据一致性（对表进行操作的同时不允许修改表结构）。
  • 当一个事务需要访问某个数据库对象（如表、视图等）时，会自动获取该对象的 MDL。根据操作类型，会获取不同类型的锁，即共享锁（SHARED_READ，SHARED_WRITE）和独占锁（EXCLUSIVE）。
  • 共享锁（SHARED_READ，SHARED_WRITE）之间是兼容的，允许多个事务同时获取共享锁（SHARED_READ，SHARED_WRITE）来读取数据，因为数据读写不会影响表的元数据（表结构）。
  • 独占锁（EXCLUSIVE）与其他任何类型的MDL都不兼容，同一时间只能有一个事务获取EXCLUSIVE锁来对元数据（表结构）进行修改操作。

2.2.1 SQL语句及其对应的MDL

SQL StatementMDL TypeCompatibility
Lock tables xxx with read/write lockSHARED_READ_ONLY/SHARED_NOT_READ_WRITE
select, select … lock in share modeSHARED_READ和SHARED_WRITE, SHARED_READ兼容，与EXCLUSIVE互斥
update, insert, delete, select … for updateSHARED_WRITE和SHARED_WRITE, SHARED_READ兼容，与EXCLUSIVE互斥
alter table …EXCLUSIVE和其他MDL不兼容

2.2.2 MDL在不同客户端中的可见性

  • 客户端在事务中获得的锁对其他客户端可见。各个客户端在开启事务之前持有关于performance_schema.metadata_locks的共享读锁。但是各个客户端只能看见自己持有这个共享读锁，看不到其他客户端持的共享读锁。
  • 客户端开启事务后则其对performance_schema.metadata_locks的共享读锁以及事务内创建的共享锁对其他客户端可见。相当于客户端开启事务后自己持有的MDL就公开了，无论它们是否在事务中创建的。
-- client1: OWNER_THREAD_ID=61，只能看见自己持有的对metadata_locks的共享读锁
select OBJECT_SCHEMA,OBJECT_NAME, LOCK_TYPE,OWNER_THREAD_ID from performance_schema.metadata_locks;
+--------------------+----------------+-------------+-----------------+
| OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE   | OWNER_THREAD_ID |
+--------------------+----------------+-------------+-----------------+
| performance_schema | metadata_locks | SHARED_READ |              61 |
+--------------------+----------------+-------------+-----------------+


-- client2: OWNER_THREAD_ID=62，只能看见自己持有的对metadata_locks的共享读锁
select OBJECT_SCHEMA,OBJECT_NAME, LOCK_TYPE,OWNER_THREAD_ID from performance_schema.metadata_locks;
+--------------------+----------------+-------------+-----------------+
| OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE   | OWNER_THREAD_ID |
+--------------------+----------------+-------------+-----------------+
| performance_schema | metadata_locks | SHARED_READ |              62 |
+--------------------+----------------+-------------+-----------------+


-- client1: 开启事务
use test;
start transaction;
select * from customers;
select OBJECT_SCHEMA,OBJECT_NAME, LOCK_TYPE,OWNER_THREAD_ID
from performance_schema.metadata_locks;  -- 两个锁：开启事务之前自己持有的共享读锁（1，metadata_locks）；事务内创建的共享读锁（1，customers）
+--------------------+----------------+-------------+-----------------+
| OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE   | OWNER_THREAD_ID |
+--------------------+----------------+-------------+-----------------+
| test               | customers      | SHARED_READ |              61 |
| performance_schema | metadata_locks | SHARED_READ |              61 |
+--------------------+----------------+-------------+-----------------+


-- client2: client1在事务中创建的MDL对其他客户端可见
select OBJECT_SCHEMA,OBJECT_NAME, LOCK_TYPE,OWNER_THREAD_ID 
from performance_schema.metadata_locks;  -- 3个锁：开启事务之前自己持有的共享读锁（1，metadata_locks）；client1持有的全部锁（2，metadata_locks，customers）
+--------------------+----------------+-------------+-----------------+
| OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE   | OWNER_THREAD_ID |
+--------------------+----------------+-------------+-----------------+
| test               | customers      | SHARED_READ |              61 |
| performance_schema | metadata_locks | SHARED_READ |              61 |
| performance_schema | metadata_locks | SHARED_READ |              62 |
+--------------------+----------------+-------------+-----------------+


-- client2
start transaction;
select * from users;
select OBJECT_SCHEMA,OBJECT_NAME, LOCK_TYPE,OWNER_THREAD_ID 
from performance_schema.metadata_locks;  -- 4个锁：自己开启事务前持有的锁（1，metadata_locks）；自己开启事务后的锁（1，customers）；client1持有的全部锁（2，metadata_locks，customers）
+--------------------+----------------+-------------+-----------------+
| OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE   | OWNER_THREAD_ID |
+--------------------+----------------+-------------+-----------------+
| test               | customers      | SHARED_READ |              61 |
| performance_schema | metadata_locks | SHARED_READ |              61 |
| test               | customers      | SHARED_READ |              62 |
| performance_schema | metadata_locks | SHARED_READ |              62 |
+--------------------+----------------+-------------+-----------------+

-- client1
select OBJECT_SCHEMA,OBJECT_NAME, LOCK_TYPE,OWNER_THREAD_ID 
from performance_schema.metadata_locks;  -- 4个锁：自己持有的全部锁（2，metadata_locks，customers）；client2持有的全部锁（2，metadata_locks，customers）
+--------------------+----------------+-------------+-----------------+
| OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE   | OWNER_THREAD_ID |
+--------------------+----------------+-------------+-----------------+
| performance_schema | metadata_locks | SHARED_READ |              61 |
| test               | customers      | SHARED_READ |              62 |
| performance_schema | metadata_locks | SHARED_READ |              62 |
| test               | customers      | SHARED_READ |              61 |
+--------------------+----------------+-------------+-----------------+

commit;  -- 释放事务中创建的共享锁（customers）。不释放开启事务前创建的共享锁（metadata_locks）
select OBJECT_SCHEMA,OBJECT_NAME, LOCK_TYPE,OWNER_THREAD_ID 
from performance_schema.metadata_locks;  -- 3个锁：自己持有的锁（1，metadata_locks），client2持有的全部锁（2，metadata_locks，customers）
+--------------------+----------------+-------------+-----------------+
| OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE   | OWNER_THREAD_ID |
+--------------------+----------------+-------------+-----------------+
| performance_schema | metadata_locks | SHARED_READ |              61 |
| test               | customers      | SHARED_READ |              62 |
| performance_schema | metadata_locks | SHARED_READ |              62 |
+--------------------+----------------+-------------+-----------------+

-- client2：client1提交了事务，client2只能看见自己持有的锁（2，metadata_locks，customers）
select OBJECT_SCHEMA,OBJECT_NAME, LOCK_TYPE,OWNER_THREAD_ID 
from performance_schema.metadata_locks;  -- 2个锁：client2自己的2个锁（metadata_locks，customers）
+--------------------+----------------+-------------+-----------------+
| OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE   | OWNER_THREAD_ID |
+--------------------+----------------+-------------+-----------------+
| test               | customers      | SHARED_READ |              62 |
| performance_schema | metadata_locks | SHARED_READ |              62 |
+--------------------+----------------+-------------+-----------------+

commit;  -- 释放事务中创建的共享锁（customers）。不释放开启事务前创建的共享锁（metadata_locks）
select OBJECT_SCHEMA,OBJECT_NAME, LOCK_TYPE,OWNER_THREAD_ID 
from performance_schema.metadata_locks;  -- 1个锁：client2自己的1个锁（metadata_locks）
+--------------------+----------------+-------------+-----------------+
| OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE   | OWNER_THREAD_ID |
+--------------------+----------------+-------------+-----------------+
| performance_schema | metadata_locks | SHARED_READ |              62 |
+--------------------+----------------+-------------+-----------------+

2.2.3 MDL限制DDL和DML

同一客户端内的共享锁和独占锁
-- client1
use test;
start transaction;
select * from users where uid = 0;  -- 获取users的SHARED_READ锁
update users set name='U0' where uid = 0;  -- 获取users的SHARED_WRITE锁
select OBJECT_SCHEMA,OBJECT_NAME, LOCK_TYPE,OWNER_THREAD_ID 
from performance_schema.metadata_locks;  -- 查看client1拥有的锁
+--------------------+----------------+--------------+-----------------+
| OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE    | OWNER_THREAD_ID |
+--------------------+----------------+--------------+-----------------+
| performance_schema | metadata_locks | SHARED_READ  |              66 |
| test               | users          | SHARED_READ  |              66 |
| test               | users          | SHARED_WRITE |              66 |
+--------------------+----------------+--------------+-----------------+

-- 由于共享锁和独占锁互斥，此时独占锁空闲（没有其他客户端使用独占锁），在获取独占锁前要释放client1持有的所有共享锁。
alter table users add column new_col varchar(20) default '';  -- 获取EXCLUSIVE锁，执行成功
select OBJECT_SCHEMA,OBJECT_NAME, LOCK_TYPE,OWNER_THREAD_ID 
from performance_schema.metadata_locks;  -- 查看client11拥有的锁
+--------------------+----------------+-------------+-----------------+
| OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE   | OWNER_THREAD_ID |
+--------------------+----------------+-------------+-----------------+
| performance_schema | metadata_locks | SHARED_READ |              66 |
+--------------------+----------------+-------------+-----------------+
不同客户端内独占锁和共享锁
-- client1
use test;
start transaction;
select * from users where uid = 0;  -- 获取users的SHARED_READ锁
update users set name='U0' where uid = 0;  -- 获取users的SHARED_WRITE锁
select OBJECT_SCHEMA,OBJECT_NAME, LOCK_TYPE,OWNER_THREAD_ID 
from performance_schema.metadata_locks;  -- 查看client1拥有的锁
+--------------------+----------------+--------------+-----------------+
| OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE    | OWNER_THREAD_ID |
+--------------------+----------------+--------------+-----------------+
| performance_schema | metadata_locks | SHARED_READ  |              66 |
| test               | users          | SHARED_READ  |              66 |
| test               | users          | SHARED_WRITE |              66 |
+--------------------+----------------+--------------+-----------------+

-- client2
alter table users add column new_col varchar(20) default '';  -- 获取EXCLUSIVE锁失败，阻塞执行

-- client1
commit;  -- client1事务提交后，client2中阻塞的DML语句顺利执行

2.2.4 补充

  • 客户端在获取关于某表的独占锁之前要确认没有关于这表的共享锁被任何客户端持有。如果关于此表的共享锁被自己持有，那么自己可以释放这些共享锁再获取独占锁来进行操作。如果关于此表的共享锁被其他客户端持有，则获取独占锁会阻塞，直到其他客户端释放共享锁（提交事务，断开连接等）。

  • 如果在use db后开启事务，则事务涉及哪些表，就对那些表加对应的MDL。如果开启事务后再use db，则该db的所有表都会被加MDL。即在事务中执行use db将导致对db内所有表加MDL。

  • 对比元数据锁和表锁，它们都有各自的共享锁和排他锁，但是针对的是不同场景。元数据锁主要用于限制并发事务之间变动表结构的操作，元数据要在事务中创建；表锁主要用于限制不同客户端变更表数据的操作，不要求在事务中创建表锁。

2.3 意向锁

  • 客户端A给表加了行锁，客户端B需要给统一表加表锁。客户端需要逐行检查是否有表锁，这个检查是低效的。意向锁主要用来快速判断一个表是否有行锁。具体地，如果客户端A执行更新操作，它会先给这行加上行锁，同时再对这张表加上意向锁。客户端B要给这张表加表锁，客户端B将直接检查意向锁，如果表锁和意向锁类型兼容，则客户端B可以添加表锁。
  • 意向锁类型：意向共享锁（IS），与表读锁兼容，与表写锁互斥；意向排他锁（IX），与表读锁和表写锁互斥。
  • 在事务中添加行锁和表意向锁。

2.3.1 IS和表锁的兼容与互斥

-- client1：在事务中添加行锁和IS
start transaction;
select * from users where uid=0 lock in share mode;  -- 添加行锁和IS
select OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_MODE from performance_schema.data_locks;  -- 查看IS和行锁
+---------------+-------------+-----------+---------------+
| OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_MODE     |
+---------------+-------------+-----------+---------------+
| test          | users       | TABLE     | IS            |
| test          | users       | RECORD    | S,REC_NOT_GAP |
+---------------+-------------+-----------+---------------+

-- client2: 添加表锁
lock tables users read;  -- 表读锁和IS兼容，正常执行
select OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE from performance_schema.data_locks; 
unlock tables;
lock tables users write;  -- 表写锁和IS互斥，阻塞直到client1事务提交

-- client1: 提交事务，client2阻塞的操作顺利执行
commit;

2.3.2 IX与表锁互斥

-- client1：在事务中添加行锁和IX
start transaction;
update users set name = 'U0' where uid = 0;  -- 更新行将加行锁和IX
select OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_MODE from performance_schema.data_locks;
+---------------+-------------+-----------+---------------+
| OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_MODE     |
+---------------+-------------+-----------+---------------+
| test          | users       | TABLE     | IX            |
| test          | users       | RECORD    | X,REC_NOT_GAP |
+---------------+-------------+-----------+---------------+

-- client2
lock tables users read;  -- 阻塞，IX与S互斥
lock tables users write;  -- 阻塞，IX与X互斥

-- client1
commit;  -- client2的阻塞操作顺利执行

3. 行级锁

3.1 行锁

行锁主要用于限制并发事务对同一行数据的更改操作。行锁有共享锁（S）和独占锁（X）。并发事务之间的行共享锁兼容，并发事务之间的独占锁与共享锁互斥，且一个时刻只能有一个事务持有独占锁。

3.1.1 SQL语句机器对应的行锁

SQL Statement行锁说明
insert排他锁自动添加
update排他锁自动添加
delete排他锁自动添加
select … for update排他锁结尾：for update
select … lock in share mode共享锁结尾：lock in share mode
select不加锁

3.1.2 行锁兼容和互斥

只有一个事务

一个事务可以同时持有共享锁和独占锁。

start transaction;
select * from users where uid=0 lock in share mode;  -- 添加共享锁
select OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_MODE 
from performance_schema.data_locks; 
+---------------+-------------+-----------+---------------+
| OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_MODE     |
+---------------+-------------+-----------+---------------+
| test          | users       | TABLE     | IS            |
| test          | users       | RECORD    | S,REC_NOT_GAP |
+---------------+-------------+-----------+---------------+

select * from users where uid=0 for update;  -- 添加独占锁
+---------------+-------------+-----------+---------------+
| OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_MODE     |
+---------------+-------------+-----------+---------------+
| test          | users       | TABLE     | IX            |
| test          | users       | TABLE     | IS            |
| test          | users       | RECORD    | S,REC_NOT_GAP |
| test          | users       | RECORD    | X,REC_NOT_GAP |
+---------------+-------------+-----------+---------------+

commit;  -- 提交事务，释放共享锁和独占锁
select OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_MODE 
from performance_schema.data_locks;  -- empty set
多个事务

独占锁只能被一个事务持有，如果一个事务已经持有独占锁，则其他事务申请独占锁需要阻塞直到持有它的事务提交释放独占锁。

-- client1
start transaction;
select * from users where uid=0;  -- 系统不会为select语句自动添加行锁
select * from users where uid=0 lock in share mode;  -- 手动为select语句添加行锁
select OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_MODE 
from performance_schema.data_locks;  -- 查看行锁和表意向锁：REC_NOT_GAP表示为行锁
+---------------+-------------+-----------+---------------+
| OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_MODE     |
+---------------+-------------+-----------+---------------+
| test          | users       | TABLE     | IS            |
| test          | users       | RECORD    | S,REC_NOT_GAP |
+---------------+-------------+-----------+---------------+

-- client2：申请共享锁
select * from users where uid=0 lock in share mode;  -- client2申请行锁成功，因为client1对同一行持有的共享锁兼容client2正申请的共享锁
select OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_MODE 
from performance_schema.data_locks;
+---------------+-------------+-----------+---------------+
| OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_MODE     |
+---------------+-------------+-----------+---------------+
| test          | users       | TABLE     | IS            |
| test          | users       | RECORD    | S,REC_NOT_GAP |
| test          | users       | TABLE     | IS            |
| test          | users       | RECORD    | S,REC_NOT_GAP |
+---------------+-------------+-----------+---------------+

-- client2：申请独占锁
select * from users where uid=0 for update;  -- 阻塞，因为client2申请的独占锁与client1持有的共享锁互斥

-- client1
commit;  -- client1的事务提交，释放独占锁，client2不再阻塞而获得独占锁

3.2 间隙锁和临键锁

3.2.1 什么是间隙锁和临键锁

间隙锁（Gap Locks）

MySQL每行表数据是按照B+tree索引结构组织的，聚集索引中每个B+Tree叶子结点存储一行数据，所有叶子结点用指针链接起来，形成链表，且该链表是有序的。相邻记录之间存在主键值上间隙。间隙锁主要用于限制往这些间隙中插入数据。多个事务可以对同一间隙获取间隙锁。

临键锁（Next-Key Locks）

对某行的临键锁等价于该行的行锁加上该行之前的间隙锁。InnoDB默认在可重复度隔离等级下使临键锁进行行搜索和索引扫描来防止幻读。

-- 临界锁例子
+-----------+---------------+-------------+---------------+-----------+---------------+-----------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME    | LOCK_TYPE | LOCK_MODE     | LOCK_DATA |
+-----------+---------------+-------------+---------------+-----------+---------------+-----------+
|        82 | test          | users       | NULL          | TABLE     | IS            | NULL      |
|        82 | test          | users       | idx_users_age | RECORD    | S             | 3, 3      |  -- 临键锁
|        82 | test          | users       | PRIMARY       | RECORD    | S,REC_NOT_GAP | 3         |
|        82 | test          | users       | idx_users_age | RECORD    | S,GAP         | 7, 7      |
+-----------+---------------+-------------+---------------+-----------+---------------+-----------+
/*
INDEX_NAME=idx_users_age, LOCK_MODE=S, LOCK_DATA=3,3 表明这个临键锁中的行锁是共享锁，且行锁锁定的是age=3的一行，间隙锁锁的是 x<age<3 的间隙，x为在索引 idx_users_age中 age=3 的前驱。3,3分别指示age=3这一项的索引值和主键值。
*/

3.2.2 情形一

使用唯一索对不存在的行加入行锁，行锁转化为间隙锁。

-- 索引上的等值查询（唯一索引）：更新不存在的记录，添加行独占锁转化为添加间隙锁
-- client1: 假设记录uid=[0,4,5,6...]
start transaction;
select * from users where uid = 2 lock in share mode; -- 对uid=[1,2,3]的间隙加上间隙锁（共享锁）
update users set name='u-1' where uid = 3; -- 对uid=[1,2,3]的间隙加上间隙锁（独占锁）
select OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_MODE 
from performance_schema.data_locks;
+-----------+---------------+-------------+-----------+-----------+-----------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+-----------+---------------+-------------+-----------+-----------+-----------+
|        82 | test          | users       | TABLE     | IX        | NULL      |
|        82 | test          | users       | TABLE     | IS        | NULL      |
|        82 | test          | users       | RECORD    | S,GAP     | 4         |
|        82 | test          | users       | RECORD    | X,GAP     | 4         |
+-----------+---------------+-------------+-----------+-----------+-----------+

-- client2：其他客户端对相同的间隙也可以获取锁，无论是共享锁还是独占锁
start transaction;
select * from users where uid = 2 lock in share mode;
update users set name='u3' where uid = 3;
select THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
from performance_schema.data_locks;
+-----------+---------------+-------------+-----------+-----------+-----------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+-----------+---------------+-------------+-----------+-----------+-----------+
|        81 | test          | users       | TABLE     | IX        | NULL      |
|        81 | test          | users       | TABLE     | IS        | NULL      |
|        81 | test          | users       | RECORD    | S,GAP     | 4         |
|        81 | test          | users       | RECORD    | X,GAP     | 4         |
|        82 | test          | users       | TABLE     | IX        | NULL      |
|        82 | test          | users       | TABLE     | IS        | NULL      |
|        82 | test          | users       | RECORD    | S,GAP     | 4         |
|        82 | test          | users       | RECORD    | X,GAP     | 4         |
+-----------+---------------+-------------+-----------+-----------+-----------+

-- client2：间隙锁限制插入
commit;  -- 先释放client2持有的锁
select THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
from performance_schema.data_locks;
+-----------+---------------+-------------+-----------+-----------+-----------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+-----------+---------------+-------------+-----------+-----------+-----------+
|        82 | test          | users       | TABLE     | IX        | NULL      |
|        82 | test          | users       | TABLE     | IS        | NULL      |
|        82 | test          | users       | RECORD    | S,GAP     | 4         |
|        82 | test          | users       | RECORD    | X,GAP     | 4         |
+-----------+---------------+-------------+-----------+-----------+-----------+
insert into users values(3, 'u3', 12, NOW(), null, null);  -- 阻塞，间隙锁锁不让插入

3.2.3 情形二

使用普通索引上等值查询时对匹配项加行锁，匹配项之前的间隙加间隙锁，匹配项之后的间隙加间隙锁。

-- 对users的name字段加普通索引
create index idx_users_age on users(age);
select * from users;
+-----+------+------+---------------------+-------------+---------+
| uid | name | age  | create_time         | update_time | new_col |
+-----+------+------+---------------------+-------------+---------+
|   1 | u1   |    1 | 2025-03-02 02:40:59 | NULL        | NULL    |
|   3 | u3   |    3 | 2025-03-02 02:40:59 | NULL        | NULL    |
|   7 | u7   |    7 | 2025-03-02 02:40:59 | NULL        | NULL    |
|   9 | u9   |    9 | 2025-03-02 02:40:59 | NULL        | NULL    |
|  13 | u13  |   13 | 2025-03-02 02:40:59 | NULL        | NULL    |
+-----+------+------+---------------------+-------------+---------+

start transaction;
select * from users where age = 3 lock in share mode;
select THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
from performance_schema.data_locks;
+-----------+---------------+-------------+---------------+-----------+---------------+-----------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME    | LOCK_TYPE | LOCK_MODE     | LOCK_DATA |
+-----------+---------------+-------------+---------------+-----------+---------------+-----------+
|        82 | test          | users       | NULL          | TABLE     | IS            | NULL      |
|        82 | test          | users       | idx_users_age | RECORD    | S             | 3, 3      |  -- 临键锁
|        82 | test          | users       | PRIMARY       | RECORD    | S,REC_NOT_GAP | 3         |
|        82 | test          | users       | idx_users_age | RECORD    | S,GAP         | 7, 7      |
+-----------+---------------+-------------+---------------+-----------+---------------+-----------+

3.2.4 情形三

使用唯一索引进行范围查询时，会加行锁和临键锁。

select * from users;
/*
+-----+------+------+---------------------+-------------+---------+
| uid | name | age  | create_time         | update_time | new_col |
+-----+------+------+---------------------+-------------+---------+
|   1 | u1   |    1 | 2025-03-02 02:40:59 | NULL        | NULL    |
|   3 | u3   |    3 | 2025-03-02 02:40:59 | NULL        | NULL    |
|   7 | u7   |    7 | 2025-03-02 02:40:59 | NULL        | NULL    |
|   9 | u9   |    9 | 2025-03-02 02:40:59 | NULL        | NULL    |
|  13 | u13  |   13 | 2025-03-02 02:40:59 | NULL        | NULL    |
+-----+------+------+---------------------+-------------+---------+
*/

start transaction;
select * from users where uid >= 9 lock in share mode;
/*
+-----+------+------+---------------------+-------------+---------+
| uid | name | age  | create_time         | update_time | new_col |
+-----+------+------+---------------------+-------------+---------+
|   9 | u9   |    9 | 2025-03-02 02:40:59 | NULL        | NULL    |
|  13 | u13  |   13 | 2025-03-02 02:40:59 | NULL        | NULL    |
+-----+------+------+---------------------+-------------+---------+
*/
select THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
from performance_schema.data_locks;
/* 对于 "="项加行锁，对于 ">" 项加临键锁，uid=13为最后一项，还需对(13，+∞)加临键锁
+-----------+---------------+-------------+------------+-----------+---------------+------------------------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_DATA              |
+-----------+---------------+-------------+------------+-----------+---------------+------------------------+
|        82 | test          | users       | NULL       | TABLE     | IS            | NULL                   |
|        82 | test          | users       | PRIMARY    | RECORD    | S,REC_NOT_GAP | 9                      |
|        82 | test          | users       | PRIMARY    | RECORD    | S             | supremum pseudo-record |
|        82 | test          | users       | PRIMARY    | RECORD    | S             | 13                     |
+-----------+---------------+-------------+------------+-----------+---------------+------------------------+
*/

4. 补充

4.1 常用术语

表锁：共享锁（S，读锁），排他锁/独占锁（X，写锁）
意向锁：意向共享锁（IS），意向排他/独占锁（IX）

元数据锁：共享锁（SHARED_READ：读锁，SHARED_WRITE：写锁），排他/独占锁（EXCLUSIVE）
锁主要有两种类型：共享锁和排他锁（独占锁）；这两种锁在不同层面出现：表锁，意向锁，元数据锁，行锁，间隙锁，临键锁。导致不同的特点。最好是具体讨论，而且需要注意有些用在处理并发事务层面的，有些是应用在会话层面的。

4.2 参考

https://www.bilibili.com/video/BV1Kr4y1i7ru

MySQL进阶系列：锁-InnoDB中锁的情况
Firstlucky77的博客
06-26 936
一，锁的基本介绍 相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。Record L
MySQL编程进阶之：`deadlock`的编程处理：如何通过重试机制解决死锁问题。
weixin_41455464的博客
08-18 822
这玩意儿就像程序里的“恋爱脑”，双方互相等待，谁也不肯放手，最终一起“殉情”——程序卡死。在实际应用中，我们应该尽量预防死锁的发生，同时结合重试机制，才能更好地保证程序的稳定性和性能。举个例子，就像两个人同时想过一个只能容纳一个人的桥，甲走到桥中间，乙也走到桥中间，甲说：“你先退回去，让我过去。你先退回去，让我过去。简单来说，死锁就是两个或多个事务，因为互相持有对方需要的资源，又都想拿到对方手里的资源，结果谁也拿不到，就这么僵持住了。这样，你就可以通过查看日志，了解死锁发生的情况，并进行相应的优化。
MySQL锁机制详解
qq_43842093的博客
07-15 4123
会话192.168.11.101（1）”给表locktab加了锁，其他会话的操作：1、可以对其他表（表locktab以外的表）进行读、写操作。2、对表locktab：读操作可以，写操作需要等待释放锁。
MySQL数据库锁机制（全面讲解）
程序员阿龙的博客
08-23 5472
在这个数据驱动的时代，数据库的重要性不言而喻。尤其是当我们面对成千上万的用户同时操作数据库时，如何保证数据不会乱套，这就得聊聊MySQL的锁机制了。锁，听起来好像有点神秘，但其实它就像是数据库的一道安全门，接下来，我会和大家一起探讨一下MySQL数据库锁的点点滴滴，包括它是怎么工作的，有哪些类型，以及在实际应用中怎么用。你能对数据库锁有个更清晰的了解，以后遇到并发问题也能更从容应对。那么，让我们开始吧！
MySQL 锁机制
最新发布
txj520lml的博客
11-01 765
MySQL 的锁机制是为了在并发访问下，保证数据的一致性、完整性而设计的。：锁定的范围大小：锁的兼容性，即多个锁之间能否共存。
『 MySQL篇 』：MySQL 锁机制介绍
m0_56361048的博客
06-15 8723
MySQL锁机制详解
查看Mysql锁表语句
weixin_33743248的博客
05-09 469
大早上的刷数据，大量的改表结构alter语句，就把表锁了。数据库的使用过程中可能遇到锁表的情况，导致其他进程访问同样的表时出现超时的情况，如果是生产还会看到大量的pool 跪了，如果你在刷sql，你应该立即就会明白什么情况了。show full processlist;显示哪些线程正在运行，查看锁表语句等：锁表的情况如上图：waiting for table metadata lock，如果不是和...
MySQL的表锁
weixin_56289362的博客
04-29 8931
共享锁与排它锁（读锁和写锁） 读锁（共享锁）：针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，互相不阻塞 写锁（排它锁）：当前写操作没有完成前，他会阻断其他的写锁和读锁。这样就保证了在一定时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的统一资源 对于innoDB来说，读锁可以加在表上，也可以加在行上。 （行级读写锁）如果一个事务T1已经获得了某行R的读锁，那么此时另一个的事务T2是可以去获得这个行R的读锁的，因为读取的操作并没有改变行而的数据；但是，如果某个事务T3想获得行R
MySQL进阶实践：查询优化与高可用性策略
"MySQL进阶：优化查询性能与高可用性的实践" MySQL数据库在当今的互联网环境中扮演着核心角色，尤其在大数据量的场景下，优化查询性能和保证高可用性显得尤为重要。本篇将深入讨论这两个关键方面。 一、查询优化 ...
MySQL进阶指南：索引优化与事务隔离
- **锁机制**：MySQL中的行级锁、页级锁和表级锁等，以及乐观锁和悲观锁的概念，都是理解和控制事务并发的重要部分。 - **MVCC（多版本并发控制）**：InnoDB引擎采用MVCC来实现高并发读写，保证事务的一致性。 3....
黑马程序员MySQL-进阶篇资料### MySQL 进阶技术总结. 存储
04-14
内容概要：本文档《MySQL-进阶篇.pdf》深入探讨了MySQL数据库的高级特性，涵盖了存储引擎、索引、SQL性能优化、视图、存储过程、触发器、锁机制、InnoDB引擎内部原理以及MySQL管理工具等多个方面。首先，文档详细...
mysql 锁表锁行语句分享(MySQL事务处理)
09-11
下面这个语句是锁定一行数据，开始读取，一直到删除后都不会有第二个人也读到这条数据
mysql锁表和解锁语句分享
09-11
对于MySQL来说，有三种锁的级别：页级、表级、行级
mysql锁全表语句_MySql锁表语句
weixin_29666725的博客
02-07 1716
2019独角兽企业重金招聘Python工程师标准>>> 方便个人记忆：Mysql 5.5 基于innodb 引擎insert into table1 values select … from table2 …. 此种方法，会锁table2delete table1 from table1 inner join table2 on table1.id=table2.id … 此种方...
mysql中的锁表语句查看方法汇总
WGH100817的博客
06-20 787
mysql> show status like 'Table%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | Table_locks_immediate | 105 ...
mysql 锁表语句_mysql锁表和解锁语句
weixin_36060465的博客
02-02 1517
如果想要在一个表上做大量的 INSERT 和 SELECT 操作，但是并行的插入却不可能时，可以将记录插入到临时表中，然后定期将临时表中的数据更新到实际的表里。可以用以下命令实现：mysql> LOCK TABLES real_table WRITE, insert_table WRITE;mysql> INSERT INTO real_table SELECT * FROM inse...
mysql 锁行语句_mysql 锁表锁行语句分享(MySQL事务处理)
weixin_42465158的博客
01-21 366
复制代码 代码如下:mysql_query("set autocommit=0");$list_one = $db->fetch_first("select * from prizes where id = ".$id." FOR UPDATE");$db->query("DELETE from prizes WHERE id =".$list_one['id']);mysql_que...
mysql 锁语句_mysql表锁演示的语句
weixin_34397537的博客
01-18 215
欢迎进入Linux社区论坛，与200万技术人员互动交流 >>进入 首先看表结构，引擎innodb +----+----------+------+ | id | name | seat | +----+----------+------+ | 1 | 管理员 | 98 | | 2 | 维护人员 | 98 | | 3 | 主任 | 97 | | 4 | 班主任 | 96 | +----+...
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值