MySQL分析与整理 — 锁

文章中所有操作均是在 MySQL 5.7 版本下进行的

数据库中的锁是指一种软件机制，用来控制防止某个用户（进程会话）在已经占用了某种数据资源时，其他用户做出影响本用户数据操作或导致数据非完整性和非一致性问题发生的手段。所以提及锁，一般是和事务一起说的。

按照锁的级别划分：

• 读锁，也叫共享锁。针对同一块数据，多个读操作可以同时进行而不会互相影响。读锁只针对 update 时候加锁，在未对 update 操作提交之前，其他事务只能够获取最新的记录但不能够 update 操作。
• 写锁，也叫排它锁。当前写操作没有完成前，阻断其他写锁和读锁。

按照锁的范围划分：

• 行级锁，开销大，加锁慢，经常会出现死锁，锁定力度最小，发生锁冲突的概率最低，并发度高。
• 表级锁，开销小，加锁快，不会出现死锁，锁定力度大，发生冲突所的概率高，并发度低。
• 页面锁，开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定力度介于表和行行级锁之间，并发度一般。

1 MySQL的锁机制

MySQL 的锁机制比较简单，不同的存储引擎支持不同的锁机制。MyISAM 和 MEMORY 存储引擎采用表级锁。InnoDB 支持行级锁、表级锁，默认情况采用是行级锁。

2 表级锁

存储引擎 MyISAM 和 InnoDB 都支持表级锁。其中 MyISAM 存储引擎支持的表级锁为了保证数据的一致性，更改数据时，防止其他人更改数据，可以人工添加表级锁。可以使用命令对数据库的表加锁，使用命令对数据库的表解锁。

2.1 引入测试表及测试数据

-- 创建测试表
drop table if exists tbl_account;
create table tbl_account(
	id int,
    aname varchar(10),
    balance double
)ENGINE=MyISAM default charset=utf8;
-- 插入示例数据
insert into tbl_account values 
(1001, '老孙', 1000),(1002, '老唐', 2300),(1003, '老猪', 20000);
-- 再创建一个简单测试表测试备用
drop table if exists tbl_test;
create table tbl_test(
	id int primary key,
    info varchar(10)
)ENGINE=MyISAM default charset=utf8;

2.2 表级读锁

“客户端A”对表 tbl_account 加读锁，加锁后只可以查询已经加锁的表，查询没有加锁的表就报错，如下图：

“客户端B”对已加锁的 tbl_account 表进行查询，成功。对已加锁的表进行更新操作，会出现等待状态。是因为“客户端A”给表加了读锁，只能查询该表，但更新或访问其他表都会提示错误。“客户端B”可以查询表中的记录，但更新就会出现锁等待。如下图：

“客户端A”对表进行解锁，“客户端B”就可以更新成功，如果这个时候“客户端A”再给表 tbl_account 进行加锁，且后面带了 local 参数，如下图：

local 参数允许在表尾并发插入，只锁定表中当前记录，其他会话，比如“客户端B”可以插入新的记录，如下图：

但是在“客户端A”查询 tbl_account 表没有插入记录，就是因为 local 参数的作用，解锁后，即可查看到其它客户端更新的数据，如下图：

2.3 表级读锁的并发性

读锁是共享锁，不影响其它会话的读取，但不能更新已经加读锁的数据表。存储引擎是 MyISAM 的表的读写是串行的，但是总体而言的，在一定条件下，MyISAM 的表也支持查询和插入操作的并发进行。

存储引擎 MyISAM 有一个系统变量“concurrent_insert”，用以控制其并发插入的行为，其值分别可以为 0、1 或 2。其中 0 代表不允许并发操作。1 代表如果存储引擎是 MyISAM 的表中没有被删除的行，MyISAM 允许在一个进程读表的同时，另一个进程从表尾插入记录，这个是 MySQL 的默认设置（新版本的 MySQL 的这个参数默认值是 auto）。2 代表无论 MyISAM 表中有没有被删除的行，都允许在表尾并发插入记录。

因为“concurrent_insert”的是一个“GLOBAL”的设置，需要在 MySQL 配置文件 my.ini / my.cnf 中添加，需要重启 MySQL 服务设置生效。

2.3.1 验证concurrent_insert=0

在“客户端A”对表 tbl_account 加锁，在“客户端B”中对表 tbl_account 插入一条数据，此时 tbl_account 是被锁定的，插入数据只能等待，直到“客户端A”解锁表 tbl_account，“客户端B”才能插入成功。如下图：

2.3.2 验证concurrent_insert=1

在“客户端A”中删除 id=1003 的数据，然后对表 tbl_account 进行加锁。然后在“客户端B”中插入一条数据，此时表 tbl_account 中有被删除的行，插入数据只能等待。如下图：

“客户端A”进行解锁操作，“客户端B”的插入才能成功。此时表 tbl_account 中没有被删除的行了，再对表 tbl_account 进行加锁，“客户端B”再插入一条数据，插入成功，支持并发插入。如下图：

2.3.3 验证concurrent_insert=2

在”客户端A“删除 id=1006 的数据，制造一个被删除的行，对表 tbl_account 进行加锁。然后在”客户端B“中对表 tbl_account 插入一条数据，直接会插入成功，现在是支持无条件的并发插入。如下图：

2.4 表级写锁

添加表级写锁之后，其它的客户端查询，修改，插入都不允许。

lock tables 表名 write;

3 行级锁

存储引擎 InnoDB 实现的是基于多版本的并发控制协议 MVCC，这个 MVCC 的优点是读不加锁，读写不冲突。在读多写少的应用程序中，读写不冲突是非常重要的，极大的增加了系统的并发性能。

读操作可以分成两类，快照读和当前读。快照读，读取的是记录的可见版本，不用加锁。当前读，读取的是记录的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发修改。事务加锁，是针对所操作的行，对其他行不进行加锁处理。

简单的SELECT操作，属于快照读，不加锁。

select * from table where xxx;

特殊的读操作，insert，update，delete，都属于当前读，需要加锁。

select * from table where xxx lock in share mode;
select * from table where xxx for update;
insert into table values (…);
update table set xxx where xxx
delete from table where xxx;

以上 SQL 语句属于当前读，读取记录的最新版本。并且读取之后还需要保证其它并发事务不能修改当前记录，对读取记录加锁。除了第一条语句，对读取记录加共享锁外，其他的操作都加的排它锁。

3.1 引入测试表及测试数据

-- 创建测试表
-- 别忘设置主键，因为没有主键事务不支持行级锁，事务会给整张表加了锁
drop table if exists tbl_account;
create table tbl_account(
	id int primary key,
    aname varchar(10),
    balance double
)ENGINE=InnoDB default charset=utf8;
-- 插入示例数据
insert into tbl_account values 
(1001, '老孙', 1000),(1002, '老唐', 2300),(1003, '老猪', 20000);

3.2 快照读

在“客户端A”开始事务，然后查询 id=1001 的数据记录信息。切换到“客户端B”，对 id=1001 的记录进行更新操作，查询并确认更新完成。切换回“客户端A”查询之前的 id=1001 的数据并没有更新，这个时候提交，再次查看数据已更新。如下图：

3.3 当前读

在“客户端A”开始事务，给 id=1001 的数据记录添加共享锁。切换到“客户端B”对 id=1001 的数据记录进行更新，这里不会马上更新，会进入锁等待。切换回“客户端A”提交事务，“客户端B”的更新操作执行成功。如下图：

3.4 事务给数据记录加锁

在“客户端A”“开始事务，然后对 id=1001 的数据记录进行更新。切换到”客户端B“也开始事务，先对 id=1002 的数据记录进行更新，更新成功。继续对 id=1001 的数据记录进行更新，出现等待，因为这个时候”客户端A“给 id=1001 的行添加了独占锁。此时”客户端A“提交事务，”客户端B“也提交事务，两个客户端中都更新生效了。如下图：

3.5 死锁产生

在“客户端A”开始事务，并给 id=1001 的数据记录添加了共享锁。此时“客户端B”也开始事务，也对 id=1001 的数据记录添加了共享锁。这时候“客户端A”要更新 id=1001 的数据记录，出现了锁等待，“客户端B”也要更新 id=1001 的数据记录，也出现锁等待，这时就产生了死锁。如下图：

结语

啰嗦了这么多，那么锁到底是干什么用的？有什么用处？什么情况下要用呢？锁了又有何意义？首先一般情况下，提及锁就会提及事务，那事务的好处是什么不言而喻了，事务可以保证数据的原子性，完整性，一致性，只有加锁者释放了锁，别人才能改变数据。当然也有不好的地方，就是增加了系统开销，有可能产生锁等待，造成数据库运行异常。这都是不正常的使用锁带来的问题。