【mysql】有索引和没有索引字段更新时锁的不同

结论

    对于有索引的的字段作为更新条件，如果更加语句用上了索引，那么只会在对于的更新字段的索引和对于记录的主键索引上加上x锁

    如果更新字段没有索引，由于需要全部扫描，那么就会给所有主键索引加上x，导致其他事务的更新操作被阻塞

准备

有表 t_person_info，create_time没有加索引，有记录： 4429

执行无索引字段更新

执行下面的事务，不提交

begin;
update  t_person_info set type='2' where create_time>SYSDATE()-2 and create_time<=SYSDATE()-1;

rollback;

查看锁信息(mysql8版本)

执行有索引记录更新

   给create_time创建索引。

ALTER TABLE `datacatlog`.`t_person_info` 
ADD INDEX `idx_create_time`(`create_time`) USING BTREE;

sql: 

select  * from t_person_info where create_time<STR_TO_DATE('05/10/2024 14:30:00', '%d/%m/%Y %H:%i:%s')
   and create_time>STR_TO_DATE('05/10/2022 14:30:00', '%d/%m/%Y %H:%i:%s')

 执行计划：

EXPLAIN select  * from t_person_info where create_time<STR_TO_DATE('05/10/2024 14:30:00', '%d/%m/%Y %H:%i:%s')
   and create_time>STR_TO_DATE('05/10/2022 14:30:00', '%d/%m/%Y %H:%i:%s')

然后再更新：

begin;
update  t_person_info set type='2' where create_time>SYSDATE()-2 and create_time<=SYSDATE()-1;

rollback;

 查看锁信息(mysql8版本)

可以看到上面查询的符合条件的记录都被加上x锁了。

然后执行其他更新操作：

执行无主键和无唯一键的表更新 

    t_test表有test1 test2 test3三个字段，但是没有任何索引，也没有主键

 开始一个执行更新事务

BEGIN;

update t_test set test1='r2rr' where test2='233';


ROLLBACK