当前位置： 首页 > news >正文

MySQL-锁

news 2025/8/12 12:27:14

MySQL-锁

  • 一、锁的核心作用
  • 二、锁的分类
    • 2.1 锁颗粒度划分
      • 2.1.1 全局锁
      • 2.1.2 表锁：锁住整张表
      • 2.1.3 行锁：锁定指定行
    • 2.2 互斥性划分
      • 2.2.1 共享锁（S锁/读锁）
      • 2.2.2 排他锁（X锁/写锁）
  • 三、悲观锁和乐观锁
  • 四、死锁
  • 总结

一、锁的核心作用

  • 控制并发冲突：阻止多个事务同时修改同一资源，或读取未提交的修改
  • 保证隔离级别：不同隔离级别（如读已提交、可重复重复读）通过不同的锁策略实现
  • 维护数据一致性：确保事务执行过程中，数据不会因并发操作出现逻辑错误（如库存超卖）

显式加锁
SELECT ... LOCK IN SHARE MODE;
SELECT ... FOR SHARE;

锁释放
MySQL释放锁操作都是隐式的，由MySQL自己来干

读未提交：当SQL执行完成，锁被释放
可重复读：当事务结束，锁被释放

二、锁的分类

2.1 锁颗粒度划分

2.1.1 全局锁

整个数据实例加锁，整个实例处于只读状态
（不能增删改、不能修改表结构、创建表…)

使用场景： 数据库备份

添加全局锁
flush tables with read lock;

root@test 16: 45>insert into info values(4, ‘D’, 1000);
# 阻塞

备份
mysqldump -uroot -p test > ./backup.sql

释放锁
unlock tables;

2.1.2 表锁：锁住整张表

特点：加锁快，开销小，不会产生死锁
颗粒度大，并发性低，锁冲突概率高
MyISAM, InnoDB 支持

表共享读锁：read lock
表独占写锁：write lock
读锁允许多事务读，写锁仅允许持锁事务读写。

添加锁
Lock table 表名 read;
Lock table 表名 write;

删除锁
Unlock tables;

查看锁
show open tables where in_use >0;

  • 元数据锁： 锁住表结构（添加列、删除列、修改列名、列类型…）
    MDL(Meta Data Lock) -> 维护数据的一致性
    元数据：表结构
    元数据锁是隐式锁，不需要手动添加
    1.当对表数据进行增删改查的时候，加MDL读锁
    2.当对表结构进行修改时，加MDL写锁

查看元数据锁

performance_schema.metadata_locks

select * from metadata_locks\G

root@performance_schema 10: 16> desc metadata_locks;
+-----------------------+-----------------+------+-----+---------+-------+
| Field                 | Type            | Null | Key | Default | Extra |
+-----------------------+-----------------+------+-----+---------+-------+
| OBJECT_TYPE           | varchar(64)     | NO   | MUL | NULL    |       |
| OBJECT_SCHEMA         | varchar(64)     | YES  |     | NULL    |       |
| OBJECT_NAME           | varchar(64)     | YES  |     | NULL    |       |
| COLUMN_NAME           | varchar(64)     | YES  |     | NULL    |       |
| OBJECT_INSTANCE_BEGIN | bigint unsigned | NO   | PRI | NULL    |       |
| LOCK_TYPE             | varchar(32)     | NO   |     | NULL    |       |
| LOCK_DURATION         | varchar(32)     | NO   |     | NULL    |       |
| LOCK_STATUS           | varchar(32)     | NO   |     | NULL    |       |
| SOURCE                | varchar(64)     | YES  |     | NULL    |       |
| OWNER_THREAD_ID       | bigint unsigned | YES  | MUL | NULL    |       |
| OWNER_EVENT_ID        | bigint unsigned | YES  |     | NULL    |       |
+-----------------------+-----------------+------+-----+---------+-------+
11 rows in set (0.00 sec)root@performance_schema 10: 16>select * from metadata_locks\G
*************************** 1. row ***************************OBJECT_TYPE: TABLEOBJECT_SCHEMA: testOBJECT_NAME: studentCOLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140166806706944LOCK_TYPE: SHARED_READLOCK_DURATION: TRANSACTIONLOCK_STATUS: GRANTEDSOURCE: sql_parse.cc:6156OWNER_THREAD_ID: 161OWNER_EVENT_ID: 53
*************************** 2. row ***************************OBJECT_TYPE: TABLEOBJECT_SCHEMA: performance_schemaOBJECT_NAME: metadata_locksCOLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140167004566656LOCK_TYPE: SHARED_READLOCK_DURATION: TRANSACTIONLOCK_STATUS: GRANTEDSOURCE: sql_parse.cc:6156OWNER_THREAD_ID: 163OWNER_EVENT_ID: 174
2 rows in set (0.00 sec)
  • 意向锁： 为了提升锁的效率，配行行锁使用
    隐式锁（不需要手动添加）
    当给表添加行锁的时候，会自动添加一个意向锁
    用于快速判断表锁与行锁的冲突

意向共享锁与表锁
session A

root@test 10: 23> begin;# 给pid=1行加了行共享锁，给product表添加共享意向锁
root@test 10: 23> select * from product where pid=1 lock in share mode;     
+-----+--------+-------+------+-------+  
| pid | pname  | price | cid  | test1 |
+-----+--------+-------+------+-------+
|   1 | 联想   |  5000 | c001 |  NULL |
+-----+--------+-------+------+-------+
1 row in set (0.00 sec)

session B

root@test 10: 23> begin;
Query OK, 0 rows affected (0.00 sec)# 获取表共享锁成功
root@test 10: 25> lock table product read;
Query OK, 0 rows affected (0.00 sec)# 查询当前被锁定的表
root@test 10: 28> show open tables where in_use >0;
+----------+---------+--------+-------------+
| Database | Table   | In_use | Name_locked |
+----------+---------+--------+-------------+
| test     | product |      1 |           0 |
+----------+---------+--------+-------------+
1 row in set (0.00 sec)# 获取表排他锁
root@test 10: 26> lock table product write;
-- 阻塞状态root@test 10: 29> unlock tables;
Query OK, 0 rows affected (0.00 sec)root@test 10: 29> show open tables where in_use >0;
Empty set (0.00 sec)

意向排他锁与表锁
session A

root@test 10: 30> begin;
Query OK, 0 rows affected (0.00 sec)root@test 10: 31> update product set price=price+1;
Query OK, 16 rows affected (0.00 sec)
Rows matched: 16  Changed: 16  Warnings: 0

session B

root@test 10: 30> begin;
Query OK, 0 rows affected (0.00 sec)root@test 10: 32> lock table product read;
-- 阻塞
root@test 10: 32> lock table product write;
-- 阻塞

2.1.3 行锁：锁定指定行

特点： 开销大，加锁慢
粒度小，并发度高，锁冲突的概率小

行锁也分为共享行锁和排他行锁

  • 记录锁：锁定某行 在RC和RR级别下支持（update/delete）
  • 间隙锁：锁定索引间隙 id=1,(2-9) id=10，RR级别下支持(不允许insert操作，避免幻读)
  • 临键锁：间隙锁的升级=> 记录锁+间隙锁，RR级别下支持

添加行锁
排他锁
select * from table for update
共享锁
select * from table lock in share mode

对表 test_lock1 测试

id: 主键（索引）
price: 普通索引
product_name: 无索引

id: 1,2,(3),4,5,6,(7,8),9
price: 5,10,15,20,25,30

root@test 11: 05>select * from test_lock1;
+----+--------------+-------+
| id | product_name | price |
+----+--------------+-------+
|  1 | product1     |  5.00 |
|  2 | product2     | 10.00 |
|  4 | product3     | 15.00 |
|  5 | product4     | 20.00 |
|  6 | product5     | 25.00 |
|  9 | product6     | 30.00 |
+----+--------------+-------+

1. 给非索引字段添加行锁

预期：给product_name="product1"行添加了排他锁
结果：当给非索引字段添加行锁时，升级为表锁

session A

root@test 11: 15> begin;
root@test 11: 15> select * from test_lock1 where product_name="product1" for update;
+----+--------------+-------+
| id | product_name | price |
+----+--------------+-------+
|  1 | product1     |  5.00 |
+----+--------------+-------+

session B

root@test 11: 15> begin;
root@test 11: 15> update test_lock1 set price=price+1 where product_name='product1';
-- 阻塞root@test 11: 16> update test_lock1 set price=price+1 where product_name='product2';
-- 阻塞root@test 11: 16> update test_lock1 set price=price+1 where product_name='product5';
-- 阻塞root@test 11: 17> select * from test_lock1 lock in share mode;
-- 阻塞

2. 在索引上(包括主键)加行锁
预期：行锁
结果：行锁

Session A

root@test 11: 21>begin;
Query OK, 0 rows affected (0.00 sec)root@test 11: 21>select * from test_lock1 where price=5 for update;
+----+--------------+-------+
| id | product_name | price |
+----+--------------+-------+
|  1 | product1     |  5.00 |
+----+--------------+-------+
1 row in set (0.00 sec)

Session B

begin;
root@test 11: 21>update test_lock1 set price=price+1 where price=5;root@test 11: 23>update test_lock1 set price=price+1 where price=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0root@test 11: 23>update test_lock1 set price=price+1 where price=6;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0root@test 11: 23>update test_lock1 set product_name='abc' where price=30;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3. 在索引上(包括主键)用in加行锁
预期：指定的多行加行锁
结果：指定的多行加行锁

session A

begin;
root@test 11: 28>select * from test_lock1 where price in (11, 25) for update;
+----+--------------+-------+
| id | product_name | price |
+----+--------------+-------+
|  2 | product2     | 11.00 |
|  6 | product5     | 25.00 |
+----+--------------+-------+
2 rows in set (0.00 sec)

session B

root@test 11: 30>update test_lock1 set product_name='abc' where price=20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0root@test 11: 30>update test_lock1 set price=price-1 where price=11;
-- 阻塞

4. 在唯一索引上进行等值（=）查询，给不存在的记录加锁
结果：将当前间隙锁住 => 优化成间隙锁
（9，4，6，2，1，5） => (索引会将数据进行排序)

session A

root@test 11: 37>begin;
Query OK, 0 rows affected (0.00 sec)root@test 11: 37>select * from test_lock1 where id=7 for update;
Empty set (0.00 sec)

session B

root@test 11: 37>begin;
Query OK, 0 rows affected (0.00 sec)root@test 11: 37>insert into test_lock1 values(7,'test1', 7);
-- 阻塞root@test 11: 39>insert into test_lock1 values(8,'test1', 7);
-- 阻塞root@test 11: 39>insert into test_lock1 values(3,'test1', 7);
Query OK, 1 row affected (0.01 sec)

问题：session A中可以插入 => A 持有锁

insert into test_lock1 values(7,‘test1’, 7);
Query OK, 1 row affected (0.01 sec)

5. 在普通索引上进行等值（=）查询加锁
5,11,15,20,25,30
锁：20
结果：间隙锁(15,20)+行锁20 +间隙锁(20,25)
（15,25）

session A

begin
root@test 11: 49>select * from test_lock1 where price=20 for update;
+----+--------------+-------+
| id | product_name | price |
+----+--------------+-------+
|  5 | abc          | 20.00 |
+----+--------------+-------+
1 row in set (0.00 sec)

session B

root@test 11: 37>begin;
Query OK, 0 rows affected (0.00 sec)root@test 11: 37>insert into test_lock1 values(7,'test1', 7);
-- 阻塞root@test 11: 39>insert into test_lock1 values(3,'test1', 7);
Query OK, 1 row affected (0.01 sec)root@test 11: 39>rollback;
Query OK, 0 rows affected (0.00 sec)root@test 11: 43>begin;
Query OK, 0 rows affected (0.00 sec)root@test 11: 49>insert into test_lock1 values(10,'test', 20);
-- 阻塞root@test 11: 52>insert into test_lock1 values(10,'test', 21);
-- 阻塞root@test 11: 52>insert into test_lock1 values(10,'test', 22);
-- 阻塞root@test 11: 52>insert into test_lock1 values(10,'test', 23);
-- 阻塞root@test 11: 52>insert into test_lock1 values(10,'test', 24);
-- 阻塞root@test 11: 52>insert into test_lock1 values(10,'test', 25);
Query OK, 1 row affected (0.00 sec)root@test 11: 54>insert into test_lock1 values(11,'test', 16);
-- 阻塞root@test 11: 54>insert into test_lock1 values(11,'test', 1);
Query OK, 1 row affected (0.00 sec)root@test 11: 55>insert into test_lock1 values(17,'test', 16);
-- 阻塞root@test 11: 56>insert into test_lock1 values(17,'test', 26);
Query OK, 1 row affected (0.00 sec)

6. 在索引上进行范围查询(非唯一索引)
结果：指定范围的记录和间隙都锁定，以及最后一个不满足条件的值

session A

root@test 11: 59>begin;
Query OK, 0 rows affected (0.00 sec)root@test 11: 59>select * from test_lock1 where price <20 for update;
+----+--------------+-------+
| id | product_name | price |
+----+--------------+-------+
|  1 | product1     |  5.00 |
|  2 | product2     | 11.00 |
|  4 | product3     | 15.00 |
+----+--------------+-------+
3 rows in set (0.00 sec)

session B

root@test 11: 59>BEGIN;
Query OK, 0 rows affected (0.00 sec)root@test 12: 01>update test_lock1 set product_name='abc' where price=5;
-- 阻塞root@test 12: 01>update test_lock1 set product_name='abc' where price=11;
-- 阻塞root@test 12: 01>update test_lock1 set product_name='abc' where price=15;
-- 阻塞root@test 12: 02>insert into test_lock1 values(17,'test', 1);
-- 阻塞root@test 12: 02>insert into test_lock1 values(17,'test', 6);
-- 阻塞root@test 12: 02>insert into test_lock1 values(17,'test', 14);
-- 阻塞root@test 12: 02>update test_lock1 set product_name='abc' where price=20;
-- 阻塞root@test 12: 03>update test_lock1 set product_name='abc' where price=21;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0root@test 12: 03>insert into test_lock1 values(17,'test', 21);
Query OK, 1 row affected (0.00 sec)root@test 12: 04>

2.2 互斥性划分

2.2.1 共享锁（S锁/读锁）

不同事务之间不相互排斥，可以同时获取锁

共享锁：

一个事务获取共享锁，当前事务可读可写
其他事务对数据进行读操作，不能写

SELECT ... LOCK IN SHARE MODE;

当一个事务获取共享锁，并修改了数据，其他事务不能获取共享锁

T1: 对id=1加一个共享锁，并修改数据
T2/T3: 读取id=1数据，不可以获取共享锁

T1
root@test 16: 20>begin;
Query OK, 0 rows affected (0.00 sec)root@test 16: 20>select * from info where id=1 for share;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |   302 |
+----+------+-------+
1 row in set (0.00 sec)
root@test 16: 20>update info set money=money+1 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0T2：
root@test 16: 17>begin;
Query OK, 0 rows affected (0.00 sec)root@test 16: 22>select * from info where id=1;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |   302 |
+----+------+-------+
1 row in set (0.00 sec)root@test 16: 22>select * from info where id=1 for share;
# 阻塞

当多个事务同时对数据获取共享锁，所有事务都只能读取数据

T1: 对id=1加一个共享锁，只读取数据
T2/T3: 读取id=1数据，可以获取共享锁

T1: 
root@test 16: 23>begin;
Query OK, 0 rows affected (0.00 sec)root@test 16: 23>select * from info where id=1 for share;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |   303 |
+----+------+-------+
1 row in set (0.00 sec)root@test 16: 24>update info set money=money+1 where id=1;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionT2: 
root@test 16: 23>begin;
Query OK, 0 rows affected (0.00 sec)root@test 16: 24>select * from info where id=1 for share;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |   303 |
+----+------+-------+
1 row in set (0.00 sec)

2.2.2 排他锁（X锁/写锁）

同一时刻只能有一个事务获取锁，一般写操作

SELECT ... FOR UPDATE

当一个事务获取排他锁后，当前事务可读可写
其他事务不能获取锁,会阻塞

T1：给id=1数据加了一个排他锁
T2:读取id=1数据，获取排他锁 -> 阻塞
修改id=1数据 -> 阻塞

T1:
root@test 16: 33>begin;
Query OK, 0 rows affected (0.00 sec)root@test 16: 33>select * from info where id=1 for update;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |   303 |
+----+------+-------+
1 row in set (0.00 sec)root@test 16: 33>update info set money=money+1 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0T2：
root@test 16: 33>begin;
Query OK, 0 rows affected (0.00 sec)root@test 16: 33>select * from info where id=1 for share;
root@test 16: 33>select * from info where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionroot@test 16: 34>update info set money=money+1 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

三、悲观锁和乐观锁

悲观锁：数据库中默认都是悲观锁

核心思想：

  • 对数据中数据的读写持悲观态度
  • 假定在读写数据的同一时刻会有其他事务来修改数据
  • 所以在操作操作前事先对数据进行加锁
  • 防止其他事务对数据进行修改

实现方式：先加锁再操作数据

乐观锁：无锁思想
核心思想：

  • 对数据中数据的读写持乐观态度

  • 假定在读写数据的同一时刻不会有其他事务来修改数据

  • 所以在操作操作前不会对数据进行加锁

  • 在更新数据时，会检查数据是否被其他事务修改，
    如果没被修改则更新成功，如果被修改了则更新失败

乐观锁是靠用户代码逻辑实现
通过版本号或者时间戳机制来实现

如果是基于版本号的乐观锁

  1. 设计表结构，多加一个字段pid, product_name, price, version
  2. 每次更新数据时，将版本号和要更新的数据取出来
  3. 更新操作时，检查当前版本号与之前取出来的版本号一致
    开始更新，数据更新，version+1

四、死锁

并发可能存在的问题：死锁

什么情况下发生死锁

  1. 多个进程分别获取了部分资源，等待其他资源
  2. 进程获取到资源之后不释放，导致2个进程僵死，都不能进行后续操作

进行某个操作需要1资源和2资源，
多个进程同时访问，A进程获取了1资源，等待2资源
B进程获取了2资源，等待1资源

如何避免死锁？

  1. 设计资源获取流程：先拿到1，再拿到2
  2. 设计一个单独的进程，去检查是否发生死锁，如果发生了，
    设计一个算法，权限利弊，杀死一个进程，释放资源

总结

锁是控制多个并发事务对共享资源（数据或元数据）访问的机制

目的是保证数据的一致性和隔离性，防止因并发操作导致的数据冲突（如脏读、丢失更新等）。锁的设计直接影响数据库的并发性能和数据可靠性，是事务隔离性的核心实现手段

http://www.xdnf.cn/news/1281781.html

相关文章：

  • MySQL数据库简介
  • 安装AI高性能推理框架llama.cpp
  • AR 智能眼镜：从入门到未来
  • 5G与云计算对代理IP行业的深远影响
  • Unknown collation: ‘utf8mb4_0900_ai_ci‘
  • ROS2学习(1)—基础概念及环境搭建
  • FinQ4Cn: 基于 MCP 协议的中国 A 股量化分析
  • P2865 [USACO06NOV] Roadblocks G
  • 第2节 PyTorch加载数据
  • 3.数据类型和类型装换
  • 爬虫和数据分析相结合案例
  • 安全合规4--下一代防火墙组网
  • 强化学习常用数据集
  • 【11-计算机视觉介绍】
  • RAG所存在的问题和解决方案
  • 贪心----3. 跳跃游戏 II
  • 2438. 二的幂数组中查询范围内的乘积
  • 零基础AI编程开发微信小程序赚流量主广告实战
  • MySQL高可用改造之数据库开发规范（大事务与数据一致性篇）
  • Kubernetes生产环境健康检查自动化指南
  • SQL复杂查询
  • Java AI生成长篇小说的实用
  • 基于大数据的个性化学习环境构建的研究与应用
  • Flutter Provider 状态管理全面解析与实战应用：从入门到精通
  • libwebsockets 服务端获取过代理的真实连接IP
  • 重学React（五）：脱围机制一
  • 使用Windbg分析多线程死锁项目实战问题分享
  • 金蝶云星空 × SRM 深度集成实战（附完整接口清单）
  • 两个Maven工程，使用idea开发，工程A中依赖了工程B，改了工程B，工程A如何获取最新代码
  • Java学习 -- 可变参数与Collections工具类