526互联

Update和Insert操作与行锁表锁

发布时间 2023-09-27 23:59:28作者: 若-飞

概述：

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的时候，可以并发执行，之间并不会相互影响。

三、深入探究：UPDATE能走索引还会锁全表吗

导读

执行UPDATE时，WEHRE条件列虽已有索引，但还会锁全表，肿么回事？

问题描述

　　叶师傅有次上课过程中执行UPDATE测试案例时，发现虽然WHERE条件列已有索引，有时候能利用二级索引进行更新（且只锁定相应必要的几行记录），但有时候却变成了根据主键进行更新，且会锁全表。我们先来看看下面的例子。

测试表 t1

CREATE TABLE `t1` (
  `c1` int(10) unsigned NOT NULL DEFAULT '0',
  `c2` int(10) unsigned NOT NULL DEFAULT '0',
  `c3` int(10) unsigned NOT NULL DEFAULT '0',
  `c4` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

表中数据

+----+----+----+----+
| c1 | c2 | c3 | c4 |
+----+----+----+----+
|  0 |  0 |  0 |  0 |
|  1 |  1 |  1 |  0 |
|  3 |  3 |  3 |  0 |
|  4 |  2 |  2 |  0 |
|  6 |  8 |  5 |  0 |
|  7 |  6 |  6 | 10 |
| 10 | 10 |  4 |  0 |
+----+----+----+----+

case1：根据二级索引UPDATE，不锁全表

先看执行计划

yejr@imysql.com [yejr]>desc update t1 set c4=123 where c2>=8\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: t1
   partitions: NULL
         type: range
possible_keys: c2
          key: c2
      key_len: 4
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using where

启动两个session执行UPDATE测试

session1 session 2(后执行)
mysql> begin;
mysql> update t1 set c4=123 where c2>=8;

 

Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

 mysql> begin;
mysql> select * from t1 where c2 = 7 for update;

 

…
1 row in set (0.00 sec)
#直接可得到结果，不会被阻塞

case2：根据PRIMARY KEY更新，锁全表

yejr@imysql.com [yejr]>desc update t1 set c4=123 where c2>=6\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: t1
   partitions: NULL
         type: index
possible_keys: c2
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: Using where

我们能看到本次执行计划是 根据主键索引进行更新，且会锁全表。

同样地，启动两个session执行UPDATE测试

session1 session2(后执行)
mysql> begin;
mysql> update t1 set c4=123 where c2>=6;

 

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

 mysql> begin;
mysql> select * from t1 where c2 = 3 for update;
#无法得到结果，被阻塞了

mysql> rollback;
#执行rollback，释放锁

=============================================

…
1 row in set (4.23 sec)
#session1释放锁后才能得到结果

查看行锁等待情况

yejr@imysql.com [yejr]>select * from sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2017-08-15 15:20:20
                    wait_age: 00:00:17
               wait_age_secs: 17
                locked_table: `yejr`.`t1`
                locked_index: PRIMARY  <--主键上加锁
                 locked_type: RECORD
              waiting_trx_id: 268350
         waiting_trx_started: 2017-08-15 15:20:20
             waiting_trx_age: 00:00:17
     waiting_trx_rows_locked: 2
   waiting_trx_rows_modified: 0
                 waiting_pid: 13
               waiting_query: select * from t1 where c2 = 3 for update
             waiting_lock_id: 268350:387:3:4
           waiting_lock_mode: X
             blocking_trx_id: 268349
                blocking_pid: 12
              blocking_query:  NULL
            blocking_lock_id: 268349:387:3:4
          blocking_lock_mode: X
        blocking_trx_started: 2017-08-15 15:20:18
            blocking_trx_age: 00:00:19
    blocking_trx_rows_locked: 8  <-- 所有记录都被加锁了
  blocking_trx_rows_modified: 3  <---持有锁的事务更新了3行记录
     sql_kill_blocking_query: KILL QUERY 12
sql_kill_blocking_connection: KILL 12

问题分析

好了，案例说完了，也该说原因了。

肾好的同学可能记得我说过一个结论：当MySQL预估扫描行数超过全表总数约 20% ～ 30% 时，即便有二级索引，也会直接升级为全表扫描。

这个结论的原因并不难理解，二级索引的顺序和主键顺序一般来说是不一样的，根据二级索引的顺序回表读数据时，实际上对于主键则很可能是随机扫描，因此当需要随机扫描的数量超过一定比例时（一般是20% ～ 30%），则优化器会决定直接改成全表扫描。

上述说法出处：WHERE Clause Optimization

Each table index is queried, and the best index is used unless the optimizer believes 
that it is more efficient to use a table scan. At one time, a scan was used based on whether 
the best index spanned more than 30% of the table, but a fixed percentage no longer determines 
the choice between using an index or a scan. The optimizer now is more complex and bases its estimate 
on additional factors such as table size, number of rows, and I/O block size.

不过，上面这个结论是针对读数据的情况，UPDATE/DELETE修改数据时是否也这样呢？

答案是肯定的，要不然上面的测试结果怎么解释……

按照官方开发者的说法，当优化器评估根据二级索引更新行数超过约50%（从上面测试结果来看，其实20% ～ 30%就已经是这样了，不过这个比例并不是固定值，会根据各种代价计算自动调整）就会改成走主键索引，并且锁全表，这么做是既定的策略，原因和上面一样，也是为了提高检索效率。

总结

老调重弹，几点建议：

  • 不管检索还是更新，都尽可能利用索引；
  • 不要一次性检索或更新大批量数据，建议分城多批次；
  • 事务尽快提交，降低行锁持有时间及其影响。

引用自：https://www.cnblogs.com/better-farther-world2099/articles/14718907.html