深入理解 MySQL —— MySQL 锁

1. 什么是锁

锁是用于管理不同事物对共享资源的并发访问。

锁分为表锁和行锁，它们的区别是：

• 锁定粒度：表锁 > 行锁
• 加锁效率：表锁 > 行锁
• 冲突概率：表锁 > 行锁
• 并发性能：表锁 < 行锁

Innodb 存储引擎支持行锁和表锁（另类的行锁）。

2. mysql Innodb 锁类型

• 共享锁（行锁）：Shared Locks。
• 排它锁（行锁）：Exclusive Locks。
• 意向共享锁（表锁）：Intention Shared Locks。
• 意向排它锁（表锁）：Intention Exclusive Locks。
• 自增锁：AUTO-INC Locks。

2.1 共享锁 VS 排它锁

共享锁

共享锁又称为读锁，简称 S 锁。顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

加锁方式：

select * from user_info where id = 1 lock in share mode; 

释放锁：

commit/rollback;

排它锁

排它锁又称写锁，简称 X 锁，排它锁不能与其他锁并存。如果一个事务获取了一个数据行的排它锁，其他事务就不能再获取该行的锁，包括共享锁和排它锁。只有获取了排它锁的事务可以对数据进行读取和修改（其他事务可以通过快照读取数据）。

加锁方式：

delete/update/insert 默认加锁排它锁
select * from table_name where ... for update;

释放锁：

commit/rollback;

2.2 意向共享锁（IS）和 意向排它锁（IX）

意向共享锁（IS）

表示事务准备给数据行加入共享锁，即一个数据行加共享锁前必须先取得该表的IS锁，意向共享锁之间是可以相互兼容的。

意向排它锁（IX）

表示事务准备给数据行加入排他锁，即一个数据行加排他锁前必须先取得该表的IX锁，意向排它锁之间是可以相互兼容的。

意向锁 (IS、IX) 是 InnoDB 操作数据之前自动加的，不需要用户干预。

意义：

当事务想去进行锁表时，可以先判断意向锁是否存在，存在时则可快速返回该表不能启用表锁。

2.3 自增锁

自增锁是针对自增列自增长的一个特殊的表级锁。

查看自增锁：

show variables like 'innodb_autoinc_lock_mode';

默认值为 1，代表连续。事务未提交 ID 永久丢失。

3. 行锁的实现

在 mysql 中，行锁的实现通过以下几个加锁方式实现：

• 记录锁
• 间隙锁
• 临键锁

3.1 Innodb 行锁到底锁了什么？

Innodb 的行锁是通过给索引加锁来实现的。

只有通过索引条件进行数据检索，Innodb 才使用行级锁。否则，Innodb 将使用表锁（锁住索引的所有记录）。

添加表锁：

--添加读锁
lock tables xx read [local];
--添加写锁
lock tables xx write;

释放锁：

unlock tables;

READ锁是一个共享锁，如果一个会话在一个表上获得一个 READ锁 后，该会话和所有其他会话只能从表中读。

READ锁加上 local修饰后，在其他会话中可以写（除 Innodb 引擎外）。

3.2 临键锁（Next-key Locks）

临键锁锁住记录和区间（左开右闭）。

当 sql 按照索引进行数据检索时，查询条件是范围查找，并有数据命中，则此时 sql 语句加上的为 临键锁。锁住索引的记录和区间（左开右闭）。

3.3 间隙锁（Gap Locks）

当临键锁锁住的数据不存在时启用间隙锁，锁住数据的不存在的区间（左开右开）。

当 sql 按照索引进行数据检索时，查询条件的数据不存在，这时 sql 语句加上的锁为 间隙锁。锁住索引不存在的区间（左开右开）。

间隙锁只在 可重复读的事务隔离级别存在。

3.4 记录锁（Record Locks）

记录锁锁住具体的索引项。

当 sql 按照主键索引 或 唯一索引进行数据检索时，查询条件等值匹配且查询的数据存在，这时 sql 语句上加的锁为 记录锁。锁住具体的索引项。

4. 利用锁解决的问题

创建表

CREATE TABLE user (
	id BIGINT auto_increment NOT NULL COMMENT '主键',
	name varchar(100) NULL,
	age varchar(100) NOT NULL,
	CONSTRAINT t_PK PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
CREATE INDEX user_age_IDX USING BTREE ON user (age);

4.1 利用锁怎么解决脏读

事务 B 给 ID=1 的记录加锁记录锁，事务 A 只能在事务 B 释放锁之后读到数据。最终事务 A 读到的是 age=16。

事务 A：

start transaction;

select * from user where id=1;

commit;

事务 B：

start transaction;

update user set age=18 where id=1;

rollback;

最终事务 A 读到的数据时 age=16。

4.2 利用锁怎么解决不可重复读

事务 A 第一次加锁共享锁读取数据，由于事务 B 正在对数据进行修改，只有事务 B 提交事务后事务 A 才能读到最终的数据。最终事务 B 提交事务，事务 A 通过锁的 可重复读读取到最终的数据 age=18。

事务 A：

start transaction;

select * from user where id=1 lock in share mode; 

commit;

事务 B：

start transaction;

update user set age=18 where id=1;

commit;

事务 A 对数据加锁共享锁，这是事务 B 对数据进行修改，但是事务 B 对事务不能提交，只有等到事务 A 提交事务后事务 B 才能提交。最终事务 B 提交，事务 A 再次查询，等到结果为 age=18。

4.3 利用锁怎么解决幻读

事务 A 读取数据，并且命中数据 age = 16。添加临键锁。数据记录 age：[16, +∞]，事务 B 无法插入数据，最终事务事务 A 读到的记录是 1 条。只有当事务 A 提交事务后，事务 B 才能插入数据。事务 A 再次查询得到 2 条记录

事务 A ：

start transaction;

select * from user where age > 15 for update;

commit;

事务 B：

start transaction;

insert into user values (2,'Bob',22);

commit;

5. 死锁

5.1 死锁产生的条件

• 多个并发事务（2个或者以上）。

• 每个事务都持有锁（或者是已经在等待锁）。

• 每个事务都需要再继续持有锁。

• 事务之间产生加锁的循环等待，形成死锁。

5.2 避免死锁

1. 类似的业务逻辑以固定的顺序访问表和行。

2. 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

3. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4. 降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择

5. 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁（或者说是表锁）