前言
我们前几篇讲了索引是什么,如何使用explain分析索引使用情况,如何去优化索引,以及show profiles分析SQL语句执行资源消耗的学习。今天我们来讲讲MySQL的各种锁,这里存储引擎我们使用InnoDB
准备工作
创建表 tb_innodb_lock
drop table if exists test_innodb_lock;CREATE TABLE test_innodb_lock ( a INT (11), b VARCHAR (20)) ENGINE INNODB DEFAULT charset = utf8;insert into test_innodb_lock values (1,'a');insert into test_innodb_lock values (2,'b');insert into test_innodb_lock values (3,'c');insert into test_innodb_lock values (4,'d');insert into test_innodb_lock values (5,'e');复制代码
创建索引
create index idx_lock_a on test_innodb_lock(a);create index idx_lock_b on test_innodb_lock(b);复制代码
MySQL 各种锁演示
先将自动提交事务改成手动提交:set autocommit=0;
我们启动两个会话窗口 A 和 B,模拟一个抢到锁,一个没抢到被阻塞住了。
行锁(写&读)
A 窗口执行
update test_innodb_lock set b='a1' where a=1;复制代码
SELECT * from test_innodb_lock;复制代码
我们可以看到 A 窗口可以看到更新后的结果
B 窗口执行
SELECT * from test_innodb_lock;复制代码
我们可以看到 B 窗口不能看到更新后的结果,看到的还是老数据,这是因为 a = 1 的这行记录被 A 窗口执行的 SQL 语句抢到了锁,并且没有执行 commit 提交操作。所以窗口 B 看到的还是老数据。这就是 MySQL 隔离级别中的"读已提交"。
窗口 A 执行 commit 操作
COMMIT;复制代码
窗口 B 查询
SELECT * from test_innodb_lock;复制代码
这个时候我们发现窗口 B 已经读取到最新数据了
行锁(写&写)
窗口 A 执行更新 a = 1 的记录
update test_innodb_lock set b='a2' where a=1;复制代码
这时候并没有 commit 提交,锁是窗口 A 持有。
窗口 B 也执行更新 a = 1 的记录
update test_innodb_lock set b='a3' where a=1;复制代码
可以看到,窗口 B 一直处于阻塞状态,因为窗口 A 还没有执行 commit,还持有锁。窗口 B 抢不到 a = 1 这行记录的锁,所以一直阻塞等待。
窗口 A 执行 commit 操作
COMMIT;复制代码
窗口 B 的变化.........