Mysql 图解锁机制_mysql in_use

MySQL 锁机制

MySQL锁: 锁是计算机协调多个进程或线程并发访问某一资源的机制. 在数据库中, 数据是一种供许多用户共享的资源. 因此需要借助锁来解决数据并发访问的一致性、有效性

MySQL锁的分类:

• 从对数据操作的类型分: 读锁、写锁
  • 读锁(共享锁): 针对同一份数据, 多个读操作可以同时进行而不会相互影响
  • 写锁(排他锁): 当前写操作没有完成前, 它会阻断其他写锁和读锁
• 从对数据操作的粒度分: 表锁、行锁
  • 表锁: 偏向MyISAM存储引擎, 开销小加锁快, 无死锁(锁粒度大, 发生锁冲突的概率最高, 并发度最低)
  • 行锁: 偏向InnoDB存储引擎, 开销大加锁慢, 会出现死锁(锁粒度小, 发生冲突的概率最低, 并发度最高)

前面只是对 (读锁、写锁)、(表锁、行锁), 一个大概的解释或者说是结论. 接下来笔者将举详细的例子解释锁机制, 分别会从以下角度解析:

• 表锁(MyISAM存储引擎)下, 分别加 读锁 和 写锁. 会对多个连接MySQL会话的造成什么影响 ?
• 行锁(InnoDB存储引擎)下, 分别加 读锁 和 写锁. 会对多个连接MySQL会话的造成什么影响 ?

在此之前我们需要先熟悉一下的几个MySQL命令操作

show open tables;  # 查看所有数据库中正在打开的非临时表
show open tables from 数据名 where `Table` = '表名'; # 查询指定数据库指定表的是否打开(使用或锁住)
show open tables from test where `Table` = 'mylock';
1
2
3

• Database: 数据库名称
• Table: 表名
• In_use: 打开表的表锁的次数, 0 代表未打开, 1 代表 表已锁住
• Name_locked; 表名是否被锁, 0 代表未锁, 只有当删除表时或者重命名表名时, 才为1

lock table 表名1 read|writer, 表名1 read|writer, ...; # 手动为表加上 读锁 或者 写锁
lock table mylock read; # 为 mylock表加上读锁
show open tables from test where `Table` = 'mylock'; 
1
2
3

unlock tables; # 释放锁有的锁
1

1.0.1: 表锁(MyISAM存储引擎)下, 加读锁, 对多个连接MySQL(并发)会话的造成影响

表锁(MyISAM存储引擎), 在使用SELECT查询语句前, 会自动给涉及的所有表加读锁, 查询操作完成之后会释放锁

由于读锁的添加和释放由MySQL自动完成, 且无法具体捕获加锁和释放锁的时机, 因此需要手动通过命令加锁和释放锁

# 创建存储引擎为MyISAM的mylock表
create table mylock(
 id int not null primary key auto_increment,
 name varchar(20)
)engine myisam;
 
# 插入5条数据 
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');
1
2
3
4
5
6
7
8
9
10
11
12

此时, 同时打开两个MySQL连接: A连接 和 B连接, 模拟并发的情况, 在加读锁的情况下, 会造成什么样的影响:

先对 A连接 操作:

lock table mylock read; # 为mylock加上读锁
1

select * from mylock; # 在mylock表有读锁的情况下, 查询 mylock表
1

update mylock set name = 'a2' where id = 1; # 在mylock表有读锁的情况下, 修改 mylock表
1

select * from book; # 在mylock表有读锁的情况下, 查询其他表 book
1

此时, 在 A连接的mylock表有读锁的情况下, 操作B连接:

select * from mylock; # 在A连接的mylock表有读锁的情况下, B连接查询 mylock表
1

select * from book; # 在A连接的mylock表有读锁的情况下, B连接查询其他表 book
1

update mylock set name = 'a2' where id = 1; # 在A连接的mylock表有读锁的情况下, B连接修改 mylock表
1

unlock tables; # A连接释放锁,  B连接从等待状态 变成 修改成功状态
1

1.0.2: 表锁(MyISAM存储引擎)下, 加写锁, 对多个连接MySQL(并发)会话的造成影响

表锁(MyISAM存储引擎), 在使用Insert/Update更新语句前, 会自动给涉及的所有表加写锁, 更新操作完成之后会释放锁

由于写锁的添加和释放由MySQL自动完成, 且无法具体捕获加锁和释放锁的时机, 因此需要手动通过命令加锁和释放锁

先对 A连接 操作:

lock table mylock write; # 为mylock加上写锁
1

select * from mylock; # 在mylock表有写锁的情况下, 查询 mylock表
1

update mylock set name = 'a3' where id = 1; # 在mylock表有写锁的情况下, 修改 mylock表
1

select * from book; # 在mylock表有写锁的情况下, 查询其他表 book
1

此时, 在 A连接的mylock表有写锁的情况下, 操作B连接:

select * from mylock; # 在A连接的mylock表有写锁的情况下, B连接查询 mylock表
1

update mylock set name = 'a4' where id = 1; # 在A连接的mylock表有写锁的情况下, B连接修改 mylock表
1

select * from book; # 在A连接的mylock表有写锁的情况下, B连接查询其他表 book
1

1.0.3: 总结

MyIASM在执行查询语句(SELECT)前, 会自动给涉及到的所有表加读锁; 在执行增删改操作前, 会自动给涉及到的所有表加写锁, 操作完成之后自动释放锁

MyIASM的锁有两种模式: 表共享读锁、表独占写锁

根据之前的SQL例子, 我们不难得出如下的结论: 读锁 和 写锁 的兼容性

结论: 读锁(共享锁)只会阻塞其他线程的写操作, 不会堵塞其他线程的读操作; 而写锁(排它锁)则会把其他线程的读操作和写操作都堵塞

分析优化表锁定

show status like 'table_locks%'; 
1

• Table_locks_immediate: 产生表级锁定的次数, 每次获取锁时 值+1
• Table_locks_waited: 出现表级锁争用而发生的等待次数, 即 不能立即获取锁的查询次数, 每等待一次 值+1

MyISAM存储引擎的读写锁调度是写优先的, 这也是MyISAM不适合做写为主表的引擎. 因为加上写锁后, 其他线程不能做任何操作, 大量的更新会使查询很难得到锁, 从而造成永久阻塞. 因此在做读写分离时, 通常是读的库选择MyISAM存储引擎(偏读)

2.0.1: 行锁(InnoDB存储引擎)下, 加读锁, 对多个连接MySQL(并发)会话的造成影响

行锁(InnoDB存储引擎): 开销小加锁快, 无死锁(锁粒度大, 发生锁冲突的概率最高, 并发度最低. InnoDB 与 MyISAM 的最大不同有两点: 支持事务 和 采用了行级锁(主键索引 和 自建索引)

由于锁的添加和释放由InnoDB存储引擎的事务自动完成, 且无法具体捕获释放锁的时机, 因此需要关闭自动提交事务, 手动提交事务, 来达到释放锁的效果

其实结论是与 “表锁(MyISAM存储引擎)下, 加读锁” 的结论是一致的: 读锁(共享锁)只会阻塞其他线程的写操作, 不会堵塞其他线程的读操作

只是测试的方法(SQL), 不一致:

• 必须存在索引(主键索引 或 自建索引)
• 必须要手动提交事务(释放锁)
• 为SELECT语句加共享读锁: 在查询语句后面增加 LOCK IN SHARE MODE, MySQL 会对查询结果中的每一行加共享锁
• 为SELECT语句加排他写锁: 在查询语句后面增加 FOR UPDATE, MySQL 会对查询结果中的每一行加排他锁

# 创建存储引擎为Innodb的innodb_lock表
CREATE TABLE innodb_lock (
	a INT(11), 
	b VARCHAR(16)
) ENGINE = INNODB

# 插入4条数据 
INSERT INTO innodb_lock VALUES(1, 'a1');
INSERT INTO innodb_lock VALUES(3, 'a3');
INSERT INTO innodb_lock VALUES(4, 'a4');
INSERT INTO innodb_lock VALUES(5, 'a5');


# 分别为 a 和 b 建立一个单值索引
CREATE INDEX idx_innodb_lock_a ON innodb_lock (a);
CREATE INDEX idx_innodb_lock_b ON innodb_lock (b);

# 关闭自动提交事务
SET autocommit = 0;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

2.0.2: 行锁(InnoDB存储引擎)下, 加写锁, 对多个连接MySQL(并发)会话的造成影响

同时打开两个MySQL连接: A连接 和 B连接, 模拟行锁定的情况

先对 A连接 操作:

# 关闭自动提交事务
SET autocommit = 0; 
# 更新
Update innodb_lock SET b = 'a11' WHERE a = 1
# 查询
SELECT * FROM innodb_lock

# 此时事务还未提交, 即 写锁还未释放
1
2
3
4
5
6
7
8

此时, 在A连接的innodb_lock表有写锁且事务还未提交的情况下, 操作B连接:

# 关闭自动提交事务
SET autocommit = 0;
# 查询
SELECT * FROM innodb_lock;
1
2
3
4

UPDATE innodb_lock SET b = 'a111' WHERE a = 1;
1

commit; # A连接提交事务
commit; # B连接提交事务
SELECT * FROM innodb_lock;
1
2
3

注: 在此操作中很有可能发生死锁; 无索引或者索引失效会导致行锁升级为表锁

[SQL]UPDATE innodb_lock SET b = 'a111' WHERE a = 1;
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction
1
2

产生这个错误是因为: 多个会话连接在等待获取锁的时间 超过了 系统设定的时间(50s)

show variables like "Innodb_lock_wait_timeout";
set Innodb_lock_wait_timeout = 5;
1
2

2.0.3 总结

间隙(GAP): 当SQL语句中的WHERE是范围条件, 如 WHERE a < 5. 来检索查询数据时, 并请求共享锁或排他锁时, InnoDB 会给符合条件的已有数据记录加锁; 对于将至在条件范围内但并不存在的记录, 叫做 间隙(GAP)

select * from innodb_lock where a < 6;
1

间隙锁(GAP Lock): InnoDB 会对满足检索条件的不存在的间隙记录加锁

InnoDB存储引擎由于时间了行级锁定, 虽然在锁定机制的实现方面所带来的性能损耗要比表级锁定会要更高一些, 但是在整体并发处理能力方面远远优于MyISAM的表级锁定.

但是, InnoDB的行级锁定同样也有其脆弱的一面, 当我们使用不当, 导致行级锁定失败升级成表级锁定, 可能会让InnoDB的整体性能比MyISAM更差

分析优化行锁定:

show status like 'innodb_row_lock%';
1

• Innodb_row_lock_current_waits：当前正在等待锁定的数量;
• Innodb_row_lock_time：从系统启动到现在锁定总时间长度(等待总时长)；
• Innodb_row_lock_time_avg：每次等待所花平均时间；
• Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
• Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

优化建议:

• 尽可能让所有数据检索都通过索引来完成, 避免无索引导致行锁升级为表锁
• 尽可能较少检索条件, 避免间隙锁
• 尽量控制事务大小, 酱烧锁定资源量和时间长度
• 涉及相同表的事务, 对于调用表的顺序尽量保持一致
• 在业务环境允许的情况下, 尽可能使用低级别的事务隔离