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的时候,可以并发执行,之间并不会相互影响。
三、深入探究: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) |
mysql> begin; mysql> select * from t1 where c2 = 7 for update;
… |
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) |
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://blog.csdn.net/wodeshouji6/article/details/104323875
https://imysql.com/2020/07/14/why-update-rows-using-index-but-cause-row-lock.shtml