Mysql锁机制--索引失效导致行锁变表锁
===============
Tips:在阅读本文前,最好先阅读 这篇(Mysql锁机制--行锁)文章~
在上篇文章中,我们看到InnoDB默认的行锁可以使得操作不同行时不会产生相互影响、不会阻塞,从而很好的解决了多事务和并发的问题。但是,那得基于一个前提,即 Where 条件中使用上了索引;反之,如果没有使用上索引,则是全表扫描、全部阻塞。本文就以实际例子来演示这种情景。
1 准备数据
1.1 建表
DROP TABLE IF EXISTS employee; CREATE TABLE IF NOT EXISTS employee ( id INT PRIMARY KEY auto_increment, name VARCHAR(40), money INT )ENGINE INNODB;
注意:ENGINE 是 INNODB(因为 InnoDB 才支持行锁)
1.2 插入数据
INSERT INTO employee(name, money) VALUES('1001', 10000); INSERT INTO employee(name, money) VALUES('1002', 10000);
提示:'1001' & '1002' 既是字符串,也是数字(后面会用到)
2 没有建索引的情形
到现在为止,没有显示地为 Employee 表建立索引。
2.1 准备
还是老规矩,两个会话(终端),左边是白色背景的,右边是黑色背景的,并且均设置 autocommit = 0
2.2 测试
2.2.1 在左侧会话中执行更新
Sql 语句:
UPDATE employee SET money = money + 10000 WHERE name = '1001';
结果:
2.2.2 在右侧会话中执行更新
Sql 语句:
UPDATE employee SET money = money + 5000 WHERE name = '1002';
结果:如下图所示,更新操作被阻塞了!
2.2.3 在左侧执行 COMMIT 命令
提示:执行的时候注意查看右边Sql语句执行情况的变化
2.2.4 右侧的Sql语句正常执行了,耗时 18.19 秒
2.2.5 右侧也执行一下提交
2.2.6 左侧查看一下数据
2.2.7 右侧查看一下数据
两边结果相同,且正确。
但问题是,左侧操作的是 name = '1001' 记录而右侧操作的是 name = '1002' 记录,右侧还是被阻塞了,说明,左侧的更新操作锁住了全表!究其原因,是因为 name 字段上没有索引!
2.3 结论
当 Where 查询条件中的字段没有索引时,更新操作会锁住全表!
3 索引失效的情形
现在来演示索引失效的情形;要想索引失效,前提得要有索引啊;于是,先建立索引。
3.1 建立索引
CREATE INDEX idx_name ON employee(name);
3.2 有索引情况下的行锁演示
提示:还是老规矩,两个会话(终端),左边是白色背景的,右边是黑色背景的,并且均设置 autocommit = 0
3.2.1 在左侧会话中执行更新
Sql 语句:
UPDATE employee SET money = money + 10000 WHERE name = '1001';
结果:
3.2.2 在右侧会话中执行更新
Sql 语句:
UPDATE employee SET money = money + 5000 WHERE name = '1002';
结果:
立即执行,没有被阻塞!
3.2.3 左右两侧分别执行提交
3.2.4 左侧查看结果
3.2.5 右侧查看结果
两边的结果相同,且正确。
3.2.6 结论
可以看到,在有索引的情况下,更新不同的行,InnoDB 默认的行锁不会阻塞。
3.3 索引失效情况下的行锁演示
提示:还是老规矩,两个会话(终端),左边是白色背景的,右边是黑色背景的,并且均设置 autocommit = 0
3.3.1 在左侧会话中执行更新
Sql 语句:(注意:name = 1001 的 1001 两边没有加单引号)
UPDATE employee SET money = money + 10000 WHERE name = 1001;
结果:
3.3.2 在右侧会话中执行更新
Sql 语句:
UPDATE employee SET money = money + 5000 WHERE name = '1002';
结果:
被阻塞!说明左侧会话锁住了整张表!
3.3.3 左侧执行提交(注意查看右侧会话中Sql执行的情况变化)
3.3.4 查看右侧会话
右侧会话中的更新操作被执行,耗时 20.68 秒
3.3.5 右侧也提交
3.3.6 左侧查看结果
3.3.7 右侧查看结果
两边结果相同,且正确。
3.3.8 结论
Where 条件中的查询字段虽然有索引,但是索引失效时(本例子中是字符串没有加单引号),InnoDB 默认的行锁更新操作变为表锁。
4 结论
没有索引或者索引失效时,InnoDB 的行锁变表锁
原因:Mysql 的行锁是通过索引实现的!