MySQL-锁
一、锁的核心作用
控制并发冲突：阻止多个事务同时修改同一资源，或读取未提交的修改保证隔离级别：不同隔离级别（如读已提交、可重复重复读）通过不同的锁策略实现维护数据一致性：确保事务执行过程中，数据不会因并发操作出现逻辑错误（如库存超卖）
显式加锁
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: TABLE
OBJECT_SCHEMA: test
OBJECT_NAME: student
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140166806706944
LOCK_TYPE: SHARED_READ
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6156
OWNER_THREAD_ID: 161
OWNER_EVENT_ID: 53
*************************** 2. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: performance_schema
OBJECT_NAME: metadata_locks
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140167004566656
LOCK_TYPE: SHARED_READ
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6156
OWNER_THREAD_ID: 163
OWNER_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: 0
root@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: 0
root@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: 0
root@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: 0
root@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: 0
T2：
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 transaction
T2:
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: 0
T2：
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 transaction
root@test 16: 34>update info set money=money+1 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
三、悲观锁和乐观锁
悲观锁：数据库中默认都是悲观锁
核心思想：
- 对数据中数据的读写持悲观态度
- 假定在读写数据的同一时刻会有其他事务来修改数据
- 所以在操作操作前事先对数据进行加锁
- 防止其他事务对数据进行修改
实现方式：先加锁再操作数据
乐观锁：无锁思想
核心思想：
对数据中数据的读写持乐观态度
假定在读写数据的同一时刻不会有其他事务来修改数据
所以在操作操作前不会对数据进行加锁
在更新数据时，会检查数据是否被其他事务修改，
如果没被修改则更新成功，如果被修改了则更新失败
乐观锁是靠用户代码逻辑实现
通过版本号或者时间戳机制来实现
如果是基于版本号的乐观锁
- 设计表结构，多加一个字段pid, product_name, price, version
- 每次更新数据时，将版本号和要更新的数据取出来
- 更新操作时，检查当前版本号与之前取出来的版本号一致
开始更新，数据更新，version+1
四、死锁
并发可能存在的问题：死锁
什么情况下发生死锁
- 多个进程分别获取了部分资源，等待其他资源
- 进程获取到资源之后不释放，导致2个进程僵死，都不能进行后续操作
进行某个操作需要1资源和2资源，
多个进程同时访问，A进程获取了1资源，等待2资源
B进程获取了2资源，等待1资源
如何避免死锁？
- 设计资源获取流程：先拿到1，再拿到2
- 设计一个单独的进程，去检查是否发生死锁，如果发生了，
设计一个算法，权限利弊，杀死一个进程，释放资源
总结
锁是控制多个并发事务对共享资源（数据或元数据）访问的机制
目的是保证数据的一致性和隔离性，防止因并发操作导致的数据冲突（如脏读、丢失更新等）。锁的设计直接影响数据库的并发性能和数据可靠性，是事务隔离性的核心实现手段