MySQL数据库——Update和Insert操作是锁表还是锁行

概述：

Update和Insert是锁表还是锁行，会影响到程序中并发程序的设计。

总结：

（1）Update时，where中的过滤条件列，如果用索引，锁行，无法用索引，锁表。按照索引规则，如果能使用索引，锁行，不能使用索引，锁表。

（2）Insert时，锁行。

 

一、Update操作

1. 实验一

（1）创建表和基础数据，id是主键，如下图：

（2）在navicat中，新建一个查询页面，如下图：

关闭自动提交，并更新第1条数据，执行上图中的sql语句。

由于没有使用commit;进行提交，所以id=1数据的age并没有被更新为111。

（3）在navicat中，再次新建一个查询页面，如下图：

关闭自动提交，并修改id=1的数据的age值为1111，执行上图中的sql语句，结果如下：

可以看到，无法对id=1的数据进行修改。但是，此时只知道id=1的数据无法修改，无法确定是锁表还是锁行。

（4）在navicat中再次新建一个查询页面，如下图：

关闭自动提交，并更新id=2的数据的age列为222，执行上图中的sql语句，结果如下：

可以看到，id=2的数据的age被修改。也就是说，update时，where中使用id作为过滤条件时，只是锁行，而不是锁表。

2.实验二

（1）将表中数据重置为初始值，如下图：

（2）以上步骤，将过滤条件，由id改为name，例如：

由于没有commit，所以，在执行之后，表中数据并没有改变。

（3）接着，执行以下语句，如下图：

（4）查看执行结果

执行失败。如果只是锁行，那么where name='陈二'的数据应该是可以修改的。所以这里是锁表。

3.实验三

（1）将表中数据重置为初始值，如下图：

（2）给name列添加索引

ALTER TABLE `tb_user` ADD INDEX index_name ( `name` ) ;

（3）重新执行查询4的语句，如下图：

（4）接着，执行查询5的语句，如下图：

（5）查看结果

可以看到，where name='陈二'的数据被成功修改，意味着，当name有索引的时候，是锁行。

4. 结论

（1）实验一和实验二，实验变量是过滤条件where中的列，实验一是id列（主键，有索引），实验二是name列（没有索引）。结果是以id列为过滤条件，也就是使用有索引的列时，只是锁行，而以name列为过滤条件，也就是没有索引的列时，会锁表。

（2）为验证以上结果，实验三给name列添加普通索引，实验二和实验三种，where的过滤条件都是name列，实验变量是name是否有索引。结果是：有索引，锁行；无索引，锁表。同结论（1），验证完成。

 

二、Insert操作

0.准备

注意，将索引删除（并没有影响，但是要减少变量），如下：

ALTER TABLE `tb_user` DROP INDEX index_name;

1. 实验一

（1）关闭自动提交，insert一条新数据，如下图：

查看tb_user表，发现并没有插入。（原因是没有执行commit。）

（2）再次insert一条新数据（这里并没有关闭autocommit），如下图：

结果如下：

可以看到，插入成功。也就是说id=5被锁定。之后的insert语句并没有受到影响。

2. 结论

insert的时候，可以并发执行，之间并不会相互影响。可以理解为，insert是锁行，而不会锁表。

 

三、关于索引

当有多个列时，满足索引的规则：

1.以下语句会锁表：

set autocommit = 0;
update tb_user set age = 111
where id = 1 or name = '张三';

2.以下语句会锁行：

set autocommit = 0;
update tb_user set age = 111
where id = 1 and name = '刘一';

3.结论

符合使用索引时的规则。