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 表读锁案例分析
| Session-1 | Session-2 |
|
mysql> lock table csde_myisam read;
mysql> select * from csde_myisam;
+----+-------------+------------+-----------------+
| id | user_name | password | display_name |
+----+-------------+------------+-----------------+
| 1 | kai | 123 | wukai |
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+------------+-----------------+
mysql> select * from csde;
ERROR 1100 (HY000): Table 'csde' was not locked with LOCK TABLES
当前csde_myisam表被锁，释放后当前session才可以操作其他表
mysql> update csde_myisam set password='111' where id='1';
ERROR 1099 (HY000): Table 'csde_myisam' was locked with a READ lock and can't be updated
mysql> unlock tables;
当前session对锁定表可读，不可写，不可读其他表
|
mysql> select * from csde_myisam;
+----+-------------+-----------+----------------+
| id | user_name | password | display_name |
+----+-------------+-----------+----------------+
| 1 | kai | 123 | wukai |
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
mysql> select * from csde;
+----+-------------+-----------+----------------+
| id | user_name | password | display_name |
+----+-------------+-----------+----------------+
| 1 | kai | 123 | wuka |
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
mysql> update csde_myisam set password='111' where id='1';
blocking....
Query OK, 1 row affected (1 min 55.56 sec)==>update success.
当前session对锁定表可读，写阻塞，可读其他表
|
分析：写操作（update, insert, delete）都会自动添加写锁（排它锁）。
2.6.2 表写锁案例分析
| Session-1 | Session-2 |
|
mysql> lock table csde_myisam write;
mysql> select * from csde_myisam;
+----+-------------+-----------+----------------+
| id | user_name | password | display_name |
+----+-------------+-----------+----------------+
| 1 | kai | 123 | wukai |
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
mysql> select * from csde;
ERROR 1100 (HY000): Table 'csde' was not locked with LOCK TABLES
当前csde_myisam表被锁，释放后当前session才可以操作其他表
mysql> update csde_myisam set password='111' where id='1';
Query OK, 0 rows affected (0.00 sec)==>update success
mysql> unlock tables;
当前session对锁定表可读，可写，不可读其他表
|
mysql> select * from csde_myisam;
blocking...
==>取消
mysql> select * from csde;
+----+-------------+-----------+----------------+
| id | user_name | password | display_name |
+----+-------------+-----------+----------------+
| 1 | kai | 123 | wukai |
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
mysql> update csde_myisam set password='111' where id='1';
blocking....
Query OK, 1 row affected (1 min 55.56 sec)==>update success.
当前session对锁定表不可读，不可写，对其他表可读写
|
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 行锁案例分析
| Session-1 | Session-2 |
|
select * from csde_inndob;
+----+-------------+-----------+----------------+
| id | user_name | password | display_name |
+----+-------------+-----------+----------------+
| 1 | kai | 123 | kai |
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
update csde_innodb set display_name='wukai' where id='1';
select * from csde_innodb;
+----+-------------+-----------+----------------+
| id | user_name | password | display_name |
+----+-------------+-----------+----------------+
| 1 | kai | 123 | wukai |
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
commit;
update csde_innodb set display_name='wukai' where id='1'; (添加行锁)
commit;
select * from csde_innodb;
+----+-------------+-----------+----------------+
| id | user_name | password | display_name |
+----+-------------+-----------+----------------+
| 1 | kai | 123 | wukai |
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
commit;
select * from csde_innodb;
+----+-------------+-----------+----------------+
| id | user_name | password | display_name |
+----+-------------+-----------+----------------+
| 1 | kai | 124 | wukai |
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
|
select * from innodb
+----+-------------+-----------+----------------+
| id | user_name | password | display_name |
+----+-------------+-----------+----------------+
| 1 | kai | 123 | kai |
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
select * from csde_innodb;
+----+-------------+-----------+----------------+
| id | user_name | password | display_name |
+----+-------------+-----------+----------------+
| 1 | kai | 123 | kai |
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
*没有提交，还在上个事务中，可重复读
commit;
select * from csde_innodb;
+----+-------------+-----------+----------------+
| id | user_name | password | display_name |
+----+-------------+-----------+----------------+
| 1 | kai | 123 | wukai |
| 2 | jay | 123 | jayy |
| 3 | beasyer | 123 | beasyer liu |
+----+-------------+-----------+----------------+
update csde_innodb set password='124' where id='1';
blacking...
Query OK...
commit;
|
3.8 行锁失效--升级表锁
前面我们说过，要想行锁生效，过滤条件（where）一定要加在索引字段上，否则将升级为表锁。但是，在实际开发过程中，由于虽然过滤条件为索引字段，但真正的查询不一定是走索引的--索引失效，此时行锁同样会升级为表锁。
查询csde_innodb这张表，存在两个单键索引（Primary(id), idx_user_name(user_name)
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 |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3.8.1 非索引字段
| Session-1 | Session-2 |
|
update csde_innodb set password='125' where password='124';
Query OK..(过滤条件为password，非索引字段）
行锁转换为表锁...
commit;(表锁移除)
|
update csde_innodb set display_name='jay yu' where user_name='jay';
Blocking...
虽然这里user_name为索引字段，且修改的和session-1不是同一行记录，但是此时表被锁。
Query OK...(此时user_name='jay‘这行数据被加行锁)
commit;(行锁移除）
|
3.8.2 索引失效
老开发应该都非常熟悉，并非针对索引行过滤就一定会生效，某些场景下，即使针对索引过滤，依旧会存在索引不生效的案例。这里简单介绍几种索引失效场景：
- 字段类型转换
- 前导模糊查询
- 数据库执行计算
- 不遵循最左前缀匹配规则
关于索引失效或者优化问题，后续会单独再出一篇文章，这里就不详细展开介绍了。
| Session-1 | Session-2 |
|
update csde_innodb set password='321' where id=1;
Query OK..(过滤条件为id，索引字段，但类型由varchar转换为int，索引失效）
行锁升级为表锁...
commit;(表锁移除)
|
UPDATE csde_innodb SET display_name='jay yu' WHERE user_name='jay';
Blocking...
虽然这里user_name为索引字段，且修改的和session-1非同一行记录，但是此时表被锁。
Query OK...(此时user_name='jay‘这行数据被加行锁)
commit;(行锁移除）
|
3.9 查看行锁信息
mysql> SHOW STATUS LIKE 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 1 |
| Innodb_row_lock_time | 482516 |
| Innodb_row_lock_time_avg | 10489 |
| Innodb_row_lock_time_max | 51036 |
| Innodb_row_lock_waits | 46 |
+-------------------------------+--------+
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 普通索引
默认隔离级别，根据普通索引字段过滤，针对某一行记录进行写操作或者主动添加行锁，将自动升级为间隙锁，间隙为排序后上下表记录左闭右开区间。
| Session-1 | Session-2 |
|
mysql> select * from csde_innodb;
+----+-------------+-----------+----------------+----------+
| id | user_name | password | display_name | number |
+----+-------------+-----------+----------------+----------+
| 1 | kai | 666 | wukai3 | 3 |
| 2 | jay | 666 | jay yu | 7 |
| 3 | beasyer | 666 | beasyer liu | 9 |
| 4 | neal | 123 | neal chen | 4 |
+----+-------------+-----------+----------------+----------+
mysql> update csde_innodb set password=125 where number=4;
自动添加间隙锁[3,7)
commit;(间隙锁锁移除)
|
mysql> select * from csde_innodb;
+----+-------------+-----------+----------------+----------+
| id | user_name | password | display_name | number |
+----+-------------+-----------+----------------+----------+
| 1 | kai | 666 | wukai3 | 3 |
| 2 | jay | 666 | jay yu | 7 |
| 3 | beasyer | 666 | beasyer liu | 9 |
| 4 | neal | 123 | neal chen | 4 |
+----+-------------+-----------+----------------+----------+
mysql> insert into csde_innodb(id, user_name, password,display_name, number) values(5, 'bevis', 100, 'bevis duan', 3);
Blocking...
mysql> insert into csde_innodb(id, user_name, password,display_name, number) values(5, 'bevis', 100, 'bevis duan', 6);
Blocking...
mysql> insert into csde_innodb(id, user_name, password,display_name, number) values(5, 'bevis', 100, 'bevis duan', 7);
Query OK...
mysql> insert into csde_innodb(id, user_name, password,display_name, number) values(6, 'bevis', 100, 'bevis duan', 2);
Query OK...
|
4.5.2 主键索引
默认隔离级别下，根据主键索引字段过滤，对某一行记录进行写操作或者主动添加行锁，仅添加普通的行锁。
| Session-1 | Session-2 |
|
mysql> select * from csde_innodb;
+----+-------------+-----------+----------------+----------+
| id | user_name | password | display_name | number |
+----+-------------+-----------+----------------+----------+
| 1 | kai | 666 | wukai3 | 3 |
| 2 | jay | 666 | jay yu | 7 |
| 3 | beasyer | 666 | beasyer liu | 9 |
| 4 | neal | 123 | neal chen | 4 |
| 7 | bevis | 100 | bevis duan | 3 |
+----+-------------+-----------+-----------------+---------+
mysql> update csde_innodb set password=123 where id=4;
自动添加行锁
commit;(行锁移除)
|
mysql> select * from csde_innodb;
+----+-------------+-----------+----------------+----------+
| id | user_name | password | display_name | number |
+----+-------------+-----------+----------------+----------+
| 1 | kai | 666 | wukai3 | 3 |
| 2 | jay | 666 | jay yu | 7 |
| 3 | beasyer | 666 | beasyer liu | 9 |
| 4 | neal | 123 | neal chen | 4 |
| 7 | bevis | 100 | bevis duan | 3 |
+----+-------------+-----------+-----------------+---------+
mysql> insert into csde_innodb(id, user_name, password,display_name, number) values(5, 'bevis', 100, 'bevis duan', 3);
Query OK...(不存在间隙锁)
mysql> update csde_innodb set number=6 where id=4;
Blocking...(存在行锁)
Query OK...
|
5 优化总结
- 检索数据时，尽量选择
索引字段作为过滤条件，防止行锁升级为表锁； - 合理设计索引，尽量
缩小锁的范围 - 尽量
避免使用范围检索，减小间隙锁的锁定范围 - 尽量
控制事务的大小，减少锁定资源的时间和范围
作者：吴家二少
博客地址：博客园
本文欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接
