MYSQL锁对UPDATE及INSERT的影响

前言

我们知道，MYSQL的InnoDB引擎是有行锁和表锁的。行锁，即锁住特定的记录行，对该特定行的更新操作会进行阻塞；表锁，即锁住整张表，对这张表的更新操作会阻塞；
下面进行一个测试来看看，行锁和表锁会在什么情况下被使用。

准备工作

数据库使用MYSQL5.7版本，事务的隔离级别采用默认的可重复读（REPEATABLE-READ）
建立一个简单的表，往里面插入一些基础数据

`CREATE TABLE `t_emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` tinyint(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`

insert into `t_emp`(`name`, `age`) values ('lx', 20), ('xc', 22), ('wc', 24);

现表中有三条数据

id	name	age
1	lx	20
2	xc	22
3	wc	24

测试目的及方案

目的：探究行锁及表锁对UPDATE和INSERT的影响
方案：先后开启两个事务，分别执行UPDATE和INSERT，观察结果
每测试完一个场景都进行数据回滚，以保持表中只有最初的三条数据

场景1：先UPDATE后INSERT

分别开启事务1和事务2

mysql> begin;
Query OK, 0 rows affected

先对事务1执行

mysql> update t_emp set age = 30 where age = 20;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

再对事务2执行

mysql> insert into t_emp(`name`,`age`) values ('tt', 20);

此时事务2会阻塞住，对事务1执行提交

mysql> commit;
Query OK, 0 rows affected

此时事务2会打印

Query OK, 1 row affected

表示INSERT语句执行成功。
结论：先执行UPDATE再执行INSERT，会使用表锁

场景2：先INSERT后UPDATE

先对事务1执行

mysql> insert into t_emp(`name`,`age`) values ('tt', 20);
Query OK, 1 row affected

在对事务2执行

mysql> update t_emp set age = 30 where age = 20;

此时事务2会阻塞住，对事务1执行提交

mysql> commit;
Query OK, 0 rows affected

此时事务2会打印

Query OK, 2 row affected
Rows matched: 2  Changed: 2  Warnings: 0

表示UPDATE语句执行成功，至于这里为什么受影响行数是2，因为事务1插入的数据age是20，而UPDATE是属于当前读，读的总是最新的数据，所以受影响行数是2。
结论：先执行INSERT后执行UPDATE，也会使用表锁

场景3：主键索引下，先UPDATE后INSERT

先对事务1执行

mysql> update t_emp set age = 30 where id = 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

再对事务2执行

mysql> insert into t_emp(`name`,`age`) values ('tt', 20);
Query OK, 1 row affected

可以发现事务2的INSERT直接执行成功了。
结论：使用主键索引，先执行UPDATE后执行INSERT，会使用行锁

场景4：主键索引下，先INSERT后UPDATE

先对事务1执行

mysql> insert into t_emp(`name`,`age`) values ('tt', 20);
Query OK, 1 row affected

再对事务2执行

mysql> update t_emp set age = 30 where id = 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

可以发现事务2的UPDATE直接执行成功了。
结论：使用主键索引，先执行INSERT后执行UPDATE，也会使用行锁

场景5：普通索引下，先UPDATE后INSERT

建立一个普通索引

alter table `t_emp` add index ind_age(`age`);

先对事务1执行

mysql> update t_emp set age = 30 where age = 20;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

再对事务2执行

mysql> insert into t_emp(`name`,`age`) values ('tt', 20);

此时事务2会阻塞住，和以上情况类似，等待事务1完成之后才会完成；
我们观察到，插入的数据是满足更新要求的，因为age都是20，那么如果插入的数据不满足更新要求的话是不是就不会阻塞呢？
先将数据回滚，再重新分别开启两个事务，再次先对事务1执行同样操作。
此时对事务2执行

mysql> insert into t_emp(`name`,`age`) values ('tt', 25);
Query OK, 1 row affected

会发现事务2的INSERT语句也是直接完成了，说明使用普通索引也不会将整张表锁住。
结论：使用普通索引，先执行UPDATE后执行INSERT，会使用行锁

场景6：普通索引下，先INSERT后UPDATE

同样借助场景5创建的普通索引
先对事务1执行

mysql> insert into t_emp(`name`,`age`) values ('tt', 20);
Query OK, 1 row affected

再对事务2执行

mysql> update t_emp set age = 30 where age = 20;

此时事务2会阻塞住，和以上情况类似，等待事务1完成之后才会完成；
同样的，观察到插入的数据是满足更新要求的，于是再做第二次测试；先将数据回滚，再重新分别开启两个事务，再次先对事务1执行同样操作。
此时对事务2执行

mysql> update t_emp set age = 30 where age = 22;
Query OK, 1 row affected

会发现事务2的INSERT语句也是直接完成了，说明使用普通索引也不会将整张表锁住。
结论：使用普通索引，先执行INSERT后执行UPDATE，会使用行锁

结论

1. 在不使用索引的情况下，无论是先执行UPDATE或是INSERT都会使用表锁
2. 在使用索引的情况下，无论是先执行UPDATE或是INSERT都会使用行锁
3. 表锁会阻塞所有的更新操作，行锁则只会对锁住的特定行进行阻塞