Mysql锁机制--索引失效导致行锁变表锁

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 的行锁是通过索引实现的!

 

posted @ 2018-04-24 15:21  cyhbyw  阅读(5971)  评论(1编辑  收藏  举报