死磕三天深入学习MySQL数据库锁机制

1 锁的分类

1.1 操作类型

• 读锁(共享锁)：针对同一份数据，多个操作可以同时进行而不会互相影响
• 写锁(排它锁)：当写操作没有完成前，它会阻塞其他读锁或者写锁

1.2 操作粒度

• 表锁：锁住整张表
• 行锁：锁住某行表记录
• 间隙锁：锁住某个区间行记录

2 表锁(偏读锁)

偏向MyISAM引擎，开销小，加锁快；无死锁；锁粒度大，发生锁冲突的概率最高，并发度最低

2.1 创建新表

CREATE TABLE csde_myisam (
`id` VARCHAR(64),
`user_name` VARCHAR(512) not null,
`password` VARCHAR(256),
`display_name` VARCHAR(128),
primary key (`id`))ENGINE myisam;

2.2 插入数据插入数据

INSERT INTO csde_myisam
(id, user_name, password, display_name) 
VALUES
('1', 'kai', '123', 'wukai'), 
('2', 'jay', '123', 'jayy'), 
('3', 'beasyer', '123', 'beasyer liu');

2.3 查看表锁情况

SHOW OPEN TABLES;

In_use

• 0：没有加表锁
• 1：加了表锁

mysql> SHOW OPEN TABLES;
+--------------------+---------------------------+--------+-------------+
| Database           | Table                     | In_use | Name_locked |
+--------------------+---------------------------+--------+-------------+
| mysql              | index_stats               |      0 |           0 |
....
| mysql              | gtid_executed             |      0 |           0 |
| information_schema | SHOW_STATISTICS           |      0 |           0 |
| mysql              | component                 |      0 |           0 |
| mysql              | columns                   |      0 |           0 |
| kaiwu3             | csde_myisam               |      1 |           0 |
| mysql              | func                      |      0 |           0 |
| information_schema | COLUMNS                   |      0 |           0 |
| mysql              | events                    |      0 |           0 |
| mysql              | catalogs                  |      0 |           0 |
| kaiwu3             | csde                      |      0 |           0 |
| mysql              | collations                |      0 |           0 |
| mysql              | table_partitions          |      0 |           0 |
| information_schema | TABLES                    |      0 |           0 |
| mysql              | time_zone_transition_type |      0 |           0 |
| mysql              | tablespaces               |      0 |           0 |
+--------------------+---------------------------+--------+-------------+
54 rows in set (0.00 sec)

2.4 手动增加表锁

LOCK TABLE table_name READ/WRITE, table_name2 READ/WRITE;

2.5 释放表锁

UNLOCK TABLES;

2.6 案例分析

# 给csde_myisam这张表加读锁==>myisam只支持表锁，不支持行锁
LOCK TABLE csde_myisam READ, csde WRITE;

2.6.1 表读锁案例分析

分析：写操作（update, insert, delete）都会自动添加写锁（排它锁）。

2.6.2 表写锁案例分析

2.7 总结

MyISAM在执行select语句前，会自动给涉及的表加读锁，执行写操作之前，会自动给涉及的表加写锁。

• 对MyISAM的读操作（加读锁)：不会阻塞其他session对同一表的读操作，但是会阻塞其他session的写操作，直到读锁释放。
• 对MyISAM的写操作（加写锁)：会阻塞其他session对同一表的读写操作，直到写锁释放。

3 行锁

• 偏向InnodB引擎，开销大，加锁慢；会出现死锁；锁粒度最小，发生锁冲突的概率最低，并发度也最高。
• InnoDB与MyISAM的最大不同有两点：InnoDB支持事务，行锁，MyISAM不支持事务，只支持表锁。
• InnodDB引擎，添加行锁需要针对索引字段过滤，对非索引字段进行过滤，行锁会失效升级为表锁。注意：针对索引字段进行过滤时，如果索引失效，同样会将行锁升级为表锁（开发巨坑，勿踩）。

3.1 创建新表

CREATE TABLE csde_innodb (
`id` VARCHAR(64),
`user_name` VARCHAR(512) NOT NULL,
`password` VARCHAR(256),
`display_name` VARCHAR(128),
primary key (`id`))ENGINE INNODB;

3.2 插入数据

INSERT INTO csde_innodb
(id, user_name, password, display_name) 
VALUES
('1', 'kai', '123', 'wukai'), 
('2', 'jay', '123', 'jayy'), 
('3', 'beasyer', '123', 'beasyer liu');

3.3 创建索引

行级锁需要加在索引字段上，否则会升级为表锁

ALTER TABLE csde_innodb ADD INDEX idx_user_name(user_name);

3.4 关闭自动提交

• 正常情况下，输入";"会自动提交，关闭自动提交后，必须手动输入“commit;”才会真正提交
• Innodb引擎下，写操作(update, insert, delete)的过滤条件为索引字段时针对该索引字段添加行锁。这里关闭自动提交，主要用于方便debug

SET autocommit=0; # 每个session需单独设置

3.5 添加行锁的方法

• 对数据进行写操作时，数据库会自动为该行记录添加行锁(过滤条件为索引字段)
• 主动针对某一行添加行锁(过滤条件为索引字段)

SELECT * FROM csde_innodb WHERE id=4 FOR UPDATE;

3.6 使用默认隔离级别–可重复读

MySQL事务的隔离级别分别为：

• 读未提交（Read Uncommitted）
• 读已提交（Read Committed）
• 可重复读（Repeatable Read）
• 串行化（Serializable）

关于事务隔离，后续会单独再写一篇博客，这里就不详细介绍了。

mysql> SELECT @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+

mysql> SELECT @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| REPEATABLE-READ                 |
+---------------------------------+

3.7 行锁案例分析

3.8 行锁失效–升级表锁

前面我们说过，要想行锁生效，过滤条件（where）一定要加在索引字段上，否则将升级为表锁。但是，在实际开发过程中，由于虽然过滤条件为索引字段，但真正的查询不一定是走索引的–索引失效，此时行锁同样会升级为表锁。

查询csde_innodb这张表，存在两个单键索引（Primary(id), idx_user_name(user_name)

3.8.1 非索引字段

3.8.2 索引失效

老开发应该都非常熟悉，并非针对索引行过滤就一定会生效，某些场景下，即使针对索引过滤，依旧会存在索引不生效的案例。这里简单介绍几种索引失效场景：

• 字段类型转换
• 前导模糊查询
• 数据库执行计算
• 不遵循最左前缀匹配规则

关于索引失效或者优化问题，后续会单独再出一篇文章，这里就不详细展开介绍了。

3.9 查看行锁信息

• Innodb_row_lock_current_waits： 当前正在锁定等待的数量
• Innodb_row_lock_time： 从系统启动到现在锁定的总时间
• Innodb_row_lock_time_avg：每次等待花费的平均时间
• Innodb_row_lock_time_max： 锁定等待花费的最长时间

当数据库的总等待时间比较长，平均等待时间比较高，等待锁定的总次数比较时，应该排查系统，进行相应的优化。Innodb_row_lock_waits：从系统启动到现在锁定等待的总次数

4 间隙锁

间隙锁(Next-Key): 针对某个索引字段，锁定一定范围的行记录。

• 过滤索引字段为范围时，Mysql自动给该范围的行记录添加间隙锁；
• 在可重复读（Mysql默认）隔离级别下，普通索引字段添加行锁时自动升级为间隙锁，间隙为上下两条表记录（左闭右开区间），主键索引添加行锁时则为普通行锁。

4.1 修改表结构

mysql> ALTER TABLE csde_innodb CHANGE COLUMN id id INTEGER(74) NOT NULL;
mysql> ALTER TABLE csde_innodb CHANGE COLUMN password password INTEGER(64);
mysql> ALTER TABLE csde_innodb ADD COLUMN number INTEGER(64);

4.2 查看表结构

mysql> DESC csde_innodb;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int          | NO   | PRI | NULL    |       |
| user_name    | varchar(256) | NO   | MUL | NULL    |       |
| password     | int          | YES  |     | NULL    |       |
| display_name | varchar(128) | YES  |     | NULL    |       |
| number       | int          | YES  | MUL | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

4.3 添加索引

mysql> ALTER TABLE csde_innodb ADD INDEX idx_numer(number);

4.4 查看索引

mysql> SHOW INDEX FROM csde_innodb;
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table       | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| csde_innodb |          0 | PRIMARY       |            1 | id          | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| csde_innodb |          1 | idx_user_name |            1 | user_name   | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| csde_innodb |          1 | idx_numer     |            1 | number      | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

4.5 案例分析

4.5.1 普通索引

默认隔离级别，根据普通索引字段过滤，针对某一行记录进行写操作或者主动添加行锁，将自动升级为间隙锁，间隙为排序后上下表记录左闭右开区间。

4.5.2 主键索引

默认隔离级别下，根据主键索引字段过滤，对某一行记录进行写操作或者主动添加行锁，仅添加普通的行锁。

5 优化总结

• 检索数据时，尽量选择索引字段作为过滤条件，防止行锁升级为表锁；
• 合理设计索引，尽量缩小锁的范围
• 尽量避免使用范围检索，减小间隙锁的锁定范围
• 尽量控制事务的大小，减少锁定资源的时间和范围