一次死锁的排查过程

原创已于 2023-03-29 14:56:37 修改 · 1k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

于 2023-03-29 14:51:25 首次发布

情况说明：

### The error occurred while setting parameters
### SQL: update shift_ld_merge         set state = 3, delete_time = now()         where exec_date = ?         and state in (1, 2)         and route_id = ?         and sync_time  <= ?         and (data_source = 'LIVE' or (data_source = 'DAILY' and plan_shift_id is not null))
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:267)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
        at com.sun.proxy.$Proxy151.update(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:287)
        at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:65)
        at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96)
        at com.sun.proxy.$Proxy165.deleteOldShift(Unknown Source)
        at com.jtkj.buswash.manager.ShiftLdMergeManager.deleteOldShift(ShiftLdMergeManager.java:219)
        at com.jtkj.buswash.service.IBusRouteDrivingSyncService.removeAllOldShift(IBusRouteDrivingSyncService.java:148)
        at com.jtkj.buswash.service.IBusRouteDrivingSyncService.lambda$null$6(IBusRouteDrivingSyncService.java:194)
        at org.springframework.transaction.support.TransactionOperations.lambda$executeWithoutResult$0(TransactionOperations.java:68)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
        at org.springframework.transaction.support.TransactionOperations.executeWithoutResult(TransactionOperations.java:67)
        at com.jtkj.buswash.service.IBusRouteDrivingSyncService.lambda$getRouteDrivingList$7(IBusRouteDrivingSyncService.java:186)
        at java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1640)
        ... 3 common frames omitted
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
        at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
        at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
        at sun.reflect.GeneratedMethodAccessor181.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
--
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
        at com.sun.proxy.$Proxy448.update(Unknown Source)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
        at sun.reflect.GeneratedMethodAccessor302.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
        ... 16 common frames omitted

死锁的sql为：

update shift_ld_merge         set state = 3, delete_time = now()         where exec_date = ?         and state in (1, 2)         and route_id = ?         and sync_time  <= ?         and (data_source = 'LIVE' or (data_source = 'DAILY' and plan_shift_id is not null))

这是调度更新的定时任务，做的是老数据的软删除。
现在的逻辑是：

每条线路一个事务，查询公交云的调度数据，处理完成之后全量删除过期排班。
多线程执行多条线路。
死锁是：
互相占用了对方需要的资源不释放
（互斥、请求与保持、循环依赖、不剥夺）

测试：

 explain UPDATE shift_ld_merge_2
SET state = 3,
delete_time = now() 
WHERE
	exec_date = '2023-03-27 00:00:00' 
	AND state IN ( 1, 2 ) 
	AND route_id = 1001000067 
	AND sync_time <= '2023-03-28 19:37:08' 
	AND (
	data_source = 'LIVE' 
	OR ( data_source = 'DAILY' AND plan_shift_id IS NOT NULL ))
走了索引：
idx_exec_date_driver_id2（其实就是只走了exec_date索引，是复合索引的最左匹配原则）

语句update和select走的索引不一样：

查的28号select走了exec_date索引，update直接走的主键索引。
//1.走的exec_date索引
explain  UPDATE shift_ld_merge_2
SET state = 3,
delete_time = now() 
WHERE
	exec_date = '2023-03-27 00:00:00' 
	AND state IN ( 1, 2 ) 
	AND route_id = 1001000688 
	AND sync_time <= '2023-03-28 19:37:08' 
	AND (
	data_source = 'LIVE' 
	OR ( data_source = 'DAILY' AND plan_shift_id IS NOT NULL ))
	
//2.走的exec_date索引
explain select * from shift_ld_merge_2
WHERE
	exec_date = '2023-03-28 00:00:00' 
	AND state IN ( 1, 2 ) 
	AND route_id = 1001000688
	AND sync_time <= '2023-03-28 19:37:08' 
	AND (
	data_source = 'LIVE' 
	OR ( data_source = 'DAILY' AND plan_shift_id IS NOT NULL ));

//3.走的主键索引
	explain UPDATE shift_ld_merge_2
SET state = 3,
delete_time = now() 
WHERE
	exec_date = '2023-03-28 00:00:00' 
	AND state IN ( 1, 2 ) 
	AND route_id = 1001000688
	AND sync_time <= '2023-03-28 19:37:08' 
	AND (
	data_source = 'LIVE' 
	OR ( data_source = 'DAILY' AND plan_shift_id IS NOT NULL ));

2、3条件一样，一个select一个update。
1和2、3的exec_date不一样。
可能是因为exec_date在28号数据太多了，导致用了索引之后也没有快很多。
若在区分度较高的字段上添加索引，并以该字段为筛选条件进行更新，则无论是更新该字段还是其他字段，用到索引的更新都要快好多。
若在区分度很低的字段上添加索引，并以该字段为筛选条件进行更新，当更新其他字段时，有无索引区别不大，当更新这个区分度很低的字段时，用到索引的更新反而更慢。https://dianzubuluo.com/%E6%9C%AA%E5%91%BD%E5%90%8D/115781.html

于是设置索引exec_date_route_id，按理应该会走这个索引，但是没有。

还是走的primary

死锁分析：

死锁日志查询：

show engine innodb status

LATEST DETECTED DEADLOCK
------------------------
2023-03-28 17:59:53 0x7ffb85aff700
*** (1) TRANSACTION:
TRANSACTION 97481, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 26 lock struct(s), heap size 3520, 182 row lock(s), undo log entries 181
MySQL thread id 131, OS thread handle 140718564013824, query id 1240728 localhost 127.0.0.1 buswash updating
update shift_ld_merge
        set state = 3, delete_time = now()
        where exec_date = '2023-03-28'
        and state in (1, 2)
        and route_id = '1001000174'
        and sync_time  <= '2023-03-28 17:59:39.243'
        and (data_source = 'LIVE' or (data_source = 'DAILY' and plan_shift_id is not null))
//等待space id 131 page no 11 n bits 112
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 131 page no 11 n bits 112 index PRIMARY of table `buswash`.`shift_ld_merge` trx id 97481 lock_mode X waiting
Record lock, heap no 21 PHYSICAL RECORD: n_fields 51; compact format; info bits 0

//很下面
*** WE ROLL BACK TRANSACTION (1)

*** (2) TRANSACTION:
TRANSACTION 97477, ACTIVE 14 sec fetching rows
mysql tables in use 1, locked 1
197 lock struct(s), heap size 24784, 13410 row lock(s), undo log entries 345
MySQL thread id 133, OS thread handle 140718256420608, query id 1240162 localhost 127.0.0.1 buswash updating
update shift_ld_merge
        set state = 3, delete_time = now()
        where exec_date = '2023-03-28'
        and state in (1, 2)
        and route_id = '1001000227'
        and sync_time  <= '2023-03-28 17:59:39.243'
        and (data_source = 'LIVE' or (data_source = 'DAILY' and plan_shift_id is not null))
//持有space id 131 page no 11 n bits 112
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 131 page no 11 n bits 112 index PRIMARY of table `buswash`.`shift_ld_merge` trx id 97477 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
//这个在很下面
//等待space id 131 page no 276 n bits 128
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 131 page no 276 n bits 128 index PRIMARY of table `buswash`.`shift_ld_merge` trx id 97477 lock_mode X waiting

//更下面

可以看到是两个sql互相死锁了。

update shift_ld_merge
        set state = 3, delete_time = now()
        where exec_date = '2023-03-28'
        and state in (1, 2)
        and route_id = '1001000174'
        and sync_time  <= '2023-03-28 17:59:39.243'
        and (data_source = 'LIVE' or (data_source = 'DAILY' and plan_shift_id is not null))

update shift_ld_merge
        set state = 3, delete_time = now()
        where exec_date = '2023-03-28'
        and state in (1, 2)
        and route_id = '1001000227'
        and sync_time  <= '2023-03-28 17:59:39.243'
        and (data_source = 'LIVE' or (data_source = 'DAILY' and plan_shift_id is not null))

RECORD LOCKS space id 131 page no 11 n bits 112 index PRIMARY of table `buswash`.`shift_ld_merge` trx id 97481 lock_mode X waiting

两边走的都是primary索引，锁是X锁，NEXT-KEY锁，锁一条记录及其之前的间隙。

4种锁类型：

LOCK_ORDINARY：Next-Key 锁，锁一条记录及其之前的间隙，这是 RR 隔离级别用的最多的锁，从名字也能看出来；lock_mode X

LOCK_GAP：间隙锁，锁两个记录之间的 GAP，防止记录插入；lock_mode X locks gap before rec

LOCK_REC_NOT_GAP：记录锁只锁记录；lock_mode X locks rec but not gap

LOCK_INSERT_INTENSION：插入意向锁，插入意向 GAP 锁，插入记录时使用，是 LOCK_GAP 的一种特例。lock_mode X locks gap before rec insert intention
原文链接：https://blog.youkuaiyun.com/weiguang102/article/details/119108947

锁获取顺序（死锁过程）：

A事务等待space id 131 page no 11 n bits 112（bit112）
B事务持有：RECORD LOCKS space id 131 page no 11 n bits 112（bit112）
B事务等待RECORD LOCKS space id 131 page no 276 n bits 128（bit128）
A事务回滚。
但是日志上没有显示出事务A持有锁的信息。
所以顺序应该是
1.A事务持有bit128
2.B事务持有bit112
3.A事务申请bit112，发生锁等待
4.B事务申请bit128，发生死锁。

也就是说，在两边都是走的primary索引的情况下，并发update产生了死锁。
那么又有一个问题：

主键索引锁全表记录引起的死锁？

死锁的主要原因就是两个事务对相同数据（都是同一张表的所有记录）的行锁添加顺序不一致，导致互相占用了
只能认为在走primary索引情况下，行锁添加的顺序是不一致的。

语句执行如何选择索引？

首先我们认为是用了primary索引导致的。
那么为什么这里走了primary呢？
https://zhuanlan.zhihu.com/p/427766633

show index from shift_ld_merge_2

查看索引，Cardinality是选择性。
在上面介绍了那么多个关键字的意思，但是Cardinality这个关键字非常的关键，优化器会根据这个值来判断是否使用这个索引。在B+树索引中，只有高选择性的字段才是有意义的，高选择性就是这个字段的取值范围很广，比如姓名字段，会有很多的名字，可选择性就高了。

索引优化器的执行过程：

Optimizer Trace查看优化器执行过程

set optimizer_trace="enabled=on";

UPDATE shift_ld_merge 
SET state = 3,
delete_time = now() 
WHERE
	exec_date = '2023-03-28 00:00:00' 
	AND route_id = 1001000688
	AND state IN ( 1, 2 ) 
	AND sync_time <= '2023-03-28 19:37:08' 
	AND (
	data_source = 'LIVE' 
	OR ( data_source = 'DAILY' AND plan_shift_id IS NOT NULL ));

 select * from optimizer_trace;

{
  "steps": [
    {
      "IN_uses_bisection": true
    },
    {
      "substitute_generated_columns": {
      }
    },
    {
      "condition_processing": {
        "condition": "WHERE",
        "original_condition": "((`shift_ld_merge`.`exec_date` = '2023-03-28 00:00:00') and (`shift_ld_merge`.`route_id` = 1001000688) and (`shift_ld_merge`.`state` in (1,2)) and (`shift_ld_merge`.`sync_time` <= '2023-03-28 19:37:08') and ((`shift_ld_merge`.`data_source` = 'LIVE') or ((`shift_ld_merge`.`data_source` = 'DAILY') and (`shift_ld_merge`.`plan_shift_id` is not null))))",
        "steps": [
          {
            "transformation": "equality_propagation",
            "resulting_condition": "((`shift_ld_merge`.`route_id` = 1001000688) and (`shift_ld_merge`.`state` in (1,2)) and (`shift_ld_merge`.`sync_time` <= '2023-03-28 19:37:08') and ((`shift_ld_merge`.`data_source` = 'LIVE') or ((`shift_ld_merge`.`data_source` = 'DAILY') and (`shift_ld_merge`.`plan_shift_id` is not null))) and multiple equal('2023-03-28 00:00:00', `shift_ld_merge`.`exec_date`))"
          },
          {
            "transformation": "constant_propagation",
            "resulting_condition": "((`shift_ld_merge`.`route_id` = 1001000688) and (`shift_ld_merge`.`state` in (1,2)) and (`shift_ld_merge`.`sync_time` <= '2023-03-28 19:37:08') and ((`shift_ld_merge`.`data_source` = 'LIVE') or ((`shift_ld_merge`.`data_source` = 'DAILY') and (`shift_ld_merge`.`plan_shift_id` is not null))) and multiple equal('2023-03-28 00:00:00', `shift_ld_merge`.`exec_date`))"
          },
          {
            "transformation": "trivial_condition_removal",
            "resulting_condition": "((`shift_ld_merge`.`route_id` = 1001000688) and (`shift_ld_merge`.`state` in (1,2)) and (`shift_ld_merge`.`sync_time` <= '2023-03-28 19:37:08') and ((`shift_ld_merge`.`data_source` = 'LIVE') or ((`shift_ld_merge`.`data_source` = 'DAILY') and (`shift_ld_merge`.`plan_shift_id` is not null))) and multiple equal('2023-03-28 00:00:00', `shift_ld_merge`.`exec_date`))"
          }
        ]
      }
    },
    {
      "table": "`shift_ld_merge`",
      "range_analysis": {
        "table_scan": {
          "rows": 136458,
          "cost": 29758
        },
        "potential_range_indexes": [
          {
            "index": "PRIMARY",
            "usable": true,
            "key_parts": [
              "id"
            ]
          },
          {
            "index": "idx_bus_id",
            "usable": true,
            "key_parts": [
              "bus_id",
              "id"
            ]
          },
          {
            "index": "idx_exec_date_site_id",
            "usable": true,
            "key_parts": [
              "exec_date",
              "site_id",
              "id"
            ]
          },
          {
            "index": "idx_bus_number",
            "usable": true,
            "key_parts": [
              "bus_number",
              "id"
            ]
          },
          {
            "index": "idx_ exec_date",
            "usable": true,
            "key_parts": [
              "exec_date",
              "id"
            ]
          },
          {
            "index": "idx_exec_date_route_id",
            "usable": true,
            "key_parts": [
              "exec_date",
              "route_id",
              "id"
            ]
          }
        ],
        "setup_range_conditions": [
        ],
        "group_index_range": {
          "chosen": false,
          "cause": "no_join"
        },
        "analyzing_range_alternatives": {
          "range_scan_alternatives": [
            {
              "index": "idx_exec_date_site_id",
              "ranges": [
                "0x99afb80000 <= exec_date <= 0x99afb80000"
              ],
              "index_dives_for_eq_ranges": true,
              "rowid_ordered": false,
              "using_mrr": false,
              "index_only": false,
              "rows": 68229,
              "cost": 81876,
              "chosen": false,
              "cause": "cost"
            },
            {
              "index": "idx_ exec_date",
              "ranges": [
                "0x99afb80000 <= exec_date <= 0x99afb80000"
              ],
              "index_dives_for_eq_ranges": true,
              "rowid_ordered": true,
              "using_mrr": false,
              "index_only": false,
              "rows": 68229,
              "cost": 81876,
              "chosen": false,
              "cause": "cost"
            },
            {
              "index": "idx_exec_date_route_id",
              "ranges": [
                "0x99afb80000 <= exec_date <= 0x99afb80000"
              ],
              "index_dives_for_eq_ranges": true,
              "rowid_ordered": false,
              "using_mrr": false,
              "index_only": false,
              "rows": 68229,
              "cost": 81876,
              "chosen": false,
              "cause": "cost"
            }
          ],
          "analyzing_roworder_intersect": {
            "usable": false,
            "cause": "too_few_roworder_scans"
          }
        }
      }
    }
  ]
}

注意看"potential_range_indexes":
里面有对所有索引的分析

 "analyzing_range_alternatives": {
          "range_scan_alternatives": [
            {
              "index": "idx_exec_date_site_id",
              "ranges": [
                "0x99afb80000 <= exec_date <= 0x99afb80000"
              ],
              "index_dives_for_eq_ranges": true,
              "rowid_ordered": false,
              "using_mrr": false,
              "index_only": false,
              "rows": 68229,
              "cost": 81876,
              "chosen": false,
              "cause": "cost"
            },
            {
              "index": "idx_ exec_date",
              "ranges": [
                "0x99afb80000 <= exec_date <= 0x99afb80000"
              ],
              "index_dives_for_eq_ranges": true,
              "rowid_ordered": true,
              "using_mrr": false,
              "index_only": false,
              "rows": 68229,
              "cost": 81876,
              "chosen": false,
              "cause": "cost"
            },
            {
              "index": "idx_exec_date_route_id",
              "ranges": [
                "0x99afb80000 <= exec_date <= 0x99afb80000"
              ],
              "index_dives_for_eq_ranges": true,
              "rowid_ordered": false,
              "using_mrr": false,
              "index_only": false,
              "rows": 68229,
              "cost": 81876,
              "chosen": false,
              "cause": "cost"
            }
          ],

发现idx_exec_date_route_id还是扫描到了rows：68229，就跟没走route_id一样。
原来：
我sql里的route_id是字符串，我写了数字，导致发生了隐式转换，没有走索引。
但是代码里的类型是字符串，用mybatis怎么是int了？

Mybatis的字段类型判断

它把字符串的数字当作整型了。

void deleteOldShift(LocalDate date, LocalDateTime oldTime, String routeId);

<update id="deleteOldShift">
        update shift_ld_merge
        set state = 3, delete_time = now()
        where exec_date = #{date}
        and state in (1, 2)
        and route_id = #{routeId}
        and sync_time  &lt;= #{oldTime}
        and (data_source = 'LIVE' or (data_source = 'DAILY' and plan_shift_id is not null))
    </update>

要主动设置jdbcType

Update语句和Select语句走的索引不一样？

执行：

select：

explain select * from shift_ld_merge
WHERE
	exec_date = '2023-03-28 00:00:00' 
	AND route_id = 1001000688
	AND state IN ( 1, 2 ) 
	AND sync_time <= '2023-03-28 19:37:08' 
	AND (
	data_source = 'LIVE' 
	OR ( data_source = 'DAILY' AND plan_shift_id IS NOT NULL ));

走的索引是：idx_exec_date_site_id。

| explain select * from shift_ld_merge
WHERE
	exec_date = '2023-03-28 00:00:00' 
	AND route_id = 1001000688
	AND state IN ( 1, 2 ) 
	AND sync_time <= '2023-03-28 19:37:08' 
	AND (
	data_source = 'LIVE' 
	OR ( data_source = 'DAILY' AND plan_shift_id IS NOT NULL )) | {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "IN_uses_bisection": true
          },
          {
            "expanded_query": "/* select#1 */ select `shift_ld_merge`.`id` AS `id`,`shift_ld_merge`.`arrangews_id` AS `arrangews_id`,`shift_ld_merge`.`plan_shift_id` AS `plan_shift_id`,`shift_ld_merge`.`modify_tag` AS `modify_tag`,`shift_ld_merge`.`org_id` AS `org_id`,`shift_ld_merge`.`shift_num` AS `shift_num`,`shift_ld_merge`.`exec_date` AS `exec_date`,`shift_ld_merge`.`bus_id` AS `bus_id`,`shift_ld_merge`.`plate_num` AS `plate_num`,`shift_ld_merge`.`bus_number` AS `bus_number`,`shift_ld_merge`.`driver_id` AS `driver_id`,`shift_ld_merge`.`driver_job_id` AS `driver_job_id`,`shift_ld_merge`.`on_work_time1` AS `on_work_time1`,`shift_ld_merge`.`plan_on_time` AS `plan_on_time`,`shift_ld_merge`.`bus_class` AS `bus_class`,`shift_ld_merge`.`hand_over_time` AS `hand_over_time`,`shift_ld_merge`.`hand_over_station_id` AS `hand_over_station_id`,`shift_ld_merge`.`driver_id2` AS `driver_id2`,`shift_ld_merge`.`driver_job_id2` AS `driver_job_id2`,`shift_ld_merge`.`on_work_time2` AS `on_work_time2`,`shift_ld_merge`.`end_time` AS `end_time`,`shift_ld_merge`.`route_id` AS `route_id`,`shift_ld_merge`.`route_name` AS `route_name`,`shift_ld_merge`.`site_id` AS `site_id`,`shift_ld_merge`.`site_name` AS `site_name`,`shift_ld_merge`.`site_id_type` AS `site_id_type`,`shift_ld_merge`.`site_id2` AS `site_id2`,`shift_ld_merge`.`site_name2` AS `site_name2`,`shift_ld_merge`.`site_id_type2` AS `site_id_type2`,`shift_ld_merge`.`serial` AS `serial`,`shift_ld_merge`.`ext_bus_id` AS `ext_bus_id`,`shift_ld_merge`.`driver_id3` AS `driver_id3`,`shift_ld_merge`.`driver_job_id3` AS `driver_job_id3`,`shift_ld_merge`.`last_back_time` AS `last_back_time`,`shift_ld_merge`.`operate_user_id` AS `operate_user_id`,`shift_ld_merge`.`operate_user_name` AS `operate_user_name`,`shift_ld_merge`.`remark` AS `remark`,`shift_ld_merge`.`source` AS `source`,`shift_ld_merge`.`data_source` AS `data_source`,`shift_ld_merge`.`type` AS `type`,`shift_ld_merge`.`attend_type` AS `attend_type`,`shift_ld_merge`.`create_time` AS `create_time`,`shift_ld_merge`.`update_time` AS `update_time`,`shift_ld_merge`.`state` AS `state`,`shift_ld_merge`.`delete_time` AS `delete_time`,`shift_ld_merge`.`operate_state` AS `operate_state`,`shift_ld_merge`.`sync_time` AS `sync_time`,`shift_ld_merge`.`head_shift` AS `head_shift`,`shift_ld_merge`.`plan_date` AS `plan_date` from `shift_ld_merge` where ((`shift_ld_merge`.`exec_date` = '2023-03-28 00:00:00') and (`shift_ld_merge`.`route_id` = 1001000688) and (`shift_ld_merge`.`state` in (1,2)) and (`shift_ld_merge`.`sync_time` <= '2023-03-28 19:37:08') and ((`shift_ld_merge`.`data_source` = 'LIVE') or ((`shift_ld_merge`.`data_source` = 'DAILY') and (`shift_ld_merge`.`plan_shift_id` is not null))))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`shift_ld_merge`.`exec_date` = '2023-03-28 00:00:00') and (`shift_ld_merge`.`route_id` = 1001000688) and (`shift_ld_merge`.`state` in (1,2)) and (`shift_ld_merge`.`sync_time` <= '2023-03-28 19:37:08') and ((`shift_ld_merge`.`data_source` = 'LIVE') or ((`shift_ld_merge`.`data_source` = 'DAILY') and (`shift_ld_merge`.`plan_shift_id` is not null))))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`shift_ld_merge`.`route_id` = 1001000688) and (`shift_ld_merge`.`state` in (1,2)) and (`shift_ld_merge`.`sync_time` <= '2023-03-28 19:37:08') and ((`shift_ld_merge`.`data_source` = 'LIVE') or ((`shift_ld_merge`.`data_source` = 'DAILY') and (`shift_ld_merge`.`plan_shift_id` is not null))) and multiple equal('2023-03-28 00:00:00', `shift_ld_merge`.`exec_date`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`shift_ld_merge`.`route_id` = 1001000688) and (`shift_ld_merge`.`state` in (1,2)) and (`shift_ld_merge`.`sync_time` <= '2023-03-28 19:37:08') and ((`shift_ld_merge`.`data_source` = 'LIVE') or ((`shift_ld_merge`.`data_source` = 'DAILY') and (`shift_ld_merge`.`plan_shift_id` is not null))) and multiple equal('2023-03-28 00:00:00', `shift_ld_merge`.`exec_date`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`shift_ld_merge`.`route_id` = 1001000688) and (`shift_ld_merge`.`state` in (1,2)) and (`shift_ld_merge`.`sync_time` <= '2023-03-28 19:37:08') and ((`shift_ld_merge`.`data_source` = 'LIVE') or ((`shift_ld_merge`.`data_source` = 'DAILY') and (`shift_ld_merge`.`plan_shift_id` is not null))) and multiple equal('2023-03-28 00:00:00', `shift_ld_merge`.`exec_date`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`shift_ld_merge`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`shift_ld_merge`",
                "field": "exec_date",
                "equals": "'2023-03-28 00:00:00'",
                "null_rejecting": false
              },
              {
                "table": "`shift_ld_merge`",
                "field": "exec_date",
                "equals": "'2023-03-28 00:00:00'",
                "null_rejecting": false
              },
              {
                "table": "`shift_ld_merge`",
                "field": "exec_date",
                "equals": "'2023-03-28 00:00:00'",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`shift_ld_merge`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 149235,
                    "cost": 32313
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_bus_id",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_exec_date_site_id",
                      "usable": true,
                      "key_parts": [
                        "exec_date",
                        "site_id",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_bus_number",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_ exec_date",
                      "usable": true,
                      "key_parts": [
                        "exec_date",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_exec_date_route_id",
                      "usable": true,
                      "key_parts": [
                        "exec_date",
                        "route_id",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_route_id_exec_date",
                      "usable": true,
                      "key_parts": [
                        "route_id",
                        "exec_date",
                        "id"
                      ]
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_exec_date_site_id",
                        "ranges": [
                          "0x99afb80000 <= exec_date <= 0x99afb80000"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 74617,
                        "cost": 89541,
                        "chosen": false,
                        "cause": "cost"
                      },
                      {
                        "index": "idx_ exec_date",
                        "ranges": [
                          "0x99afb80000 <= exec_date <= 0x99afb80000"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 74617,
                        "cost": 89541,
                        "chosen": false,
                        "cause": "cost"
                      },
                      {
                        "index": "idx_exec_date_route_id",
                        "ranges": [
                          "0x99afb80000 <= exec_date <= 0x99afb80000"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 74617,
                        "cost": 89541,
                        "chosen": false,
                        "cause": "cost"
                      },
                      {
                        "index": "idx_route_id_exec_date",
                        "chosen": false,
                        "cause": "unknown"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`shift_ld_merge`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "idx_exec_date_site_id",
                      "rows": 74617,
                      "cost": 22315,
                      "chosen": true
                    },
                    {
                      "access_type": "ref",
                      "index": "idx_ exec_date",
                      "rows": 74617,
                      "cost": 22315,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "idx_exec_date_route_id",
                      "rows": 74617,
                      "cost": 22315,
                      "chosen": false
                    },
                    {
                      "rows_to_scan": 149235,
                      "access_type": "scan",
                      "resulting_rows": 90.029,
                      "cost": 32311,
                      "chosen": false
                    }
                  ]
                },
                "condition_filtering_pct": 0.1207,
                "rows_for_plan": 90.029,
                "cost_for_plan": 22315,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`shift_ld_merge`.`exec_date` = '2023-03-28 00:00:00') and (`shift_ld_merge`.`route_id` = 1001000688) and (`shift_ld_merge`.`state` in (1,2)) and (`shift_ld_merge`.`sync_time` <= '2023-03-28 19:37:08') and ((`shift_ld_merge`.`data_source` = 'LIVE') or ((`shift_ld_merge`.`data_source` = 'DAILY') and (`shift_ld_merge`.`plan_shift_id` is not null))))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`shift_ld_merge`",
                  "attached": "((`shift_ld_merge`.`route_id` = 1001000688) and (`shift_ld_merge`.`state` in (1,2)) and (`shift_ld_merge`.`sync_time` <= '2023-03-28 19:37:08') and ((`shift_ld_merge`.`data_source` = 'LIVE') or ((`shift_ld_merge`.`data_source` = 'DAILY') and (`shift_ld_merge`.`plan_shift_id` is not null))))"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`shift_ld_merge`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_explain": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}

执行：

update:

explain UPDATE shift_ld_merge
SET state = 3,
delete_time = now() 
WHERE
	exec_date = '2023-03-28 00:00:00' 
	AND route_id = 1001000688
	AND state IN ( 1, 2 ) 
	AND sync_time <= '2023-03-28 19:37:08' 
	AND (
	data_source = 'LIVE' 
	OR ( data_source = 'DAILY' AND plan_shift_id IS NOT NULL ));

| explain UPDATE shift_ld_merge
SET state = 3,
delete_time = now() 
WHERE
	exec_date = '2023-03-28 00:00:00' 
	AND route_id = 1001000688
	AND state IN ( 1, 2 ) 
	AND sync_time <= '2023-03-28 19:37:08' 
	AND (
	data_source = 'LIVE' 
	OR ( data_source = 'DAILY' AND plan_shift_id IS NOT NULL )) | {
  "steps": [
    {
      "IN_uses_bisection": true
    },
    {
      "substitute_generated_columns": {
      }
    },
    {
      "condition_processing": {
        "condition": "WHERE",
        "original_condition": "((`shift_ld_merge`.`exec_date` = '2023-03-28 00:00:00') and (`shift_ld_merge`.`route_id` = 1001000688) and (`shift_ld_merge`.`state` in (1,2)) and (`shift_ld_merge`.`sync_time` <= '2023-03-28 19:37:08') and ((`shift_ld_merge`.`data_source` = 'LIVE') or ((`shift_ld_merge`.`data_source` = 'DAILY') and (`shift_ld_merge`.`plan_shift_id` is not null))))",
        "steps": [
          {
            "transformation": "equality_propagation",
            "resulting_condition": "((`shift_ld_merge`.`route_id` = 1001000688) and (`shift_ld_merge`.`state` in (1,2)) and (`shift_ld_merge`.`sync_time` <= '2023-03-28 19:37:08') and ((`shift_ld_merge`.`data_source` = 'LIVE') or ((`shift_ld_merge`.`data_source` = 'DAILY') and (`shift_ld_merge`.`plan_shift_id` is not null))) and multiple equal('2023-03-28 00:00:00', `shift_ld_merge`.`exec_date`))"
          },
          {
            "transformation": "constant_propagation",
            "resulting_condition": "((`shift_ld_merge`.`route_id` = 1001000688) and (`shift_ld_merge`.`state` in (1,2)) and (`shift_ld_merge`.`sync_time` <= '2023-03-28 19:37:08') and ((`shift_ld_merge`.`data_source` = 'LIVE') or ((`shift_ld_merge`.`data_source` = 'DAILY') and (`shift_ld_merge`.`plan_shift_id` is not null))) and multiple equal('2023-03-28 00:00:00', `shift_ld_merge`.`exec_date`))"
          },
          {
            "transformation": "trivial_condition_removal",
            "resulting_condition": "((`shift_ld_merge`.`route_id` = 1001000688) and (`shift_ld_merge`.`state` in (1,2)) and (`shift_ld_merge`.`sync_time` <= '2023-03-28 19:37:08') and ((`shift_ld_merge`.`data_source` = 'LIVE') or ((`shift_ld_merge`.`data_source` = 'DAILY') and (`shift_ld_merge`.`plan_shift_id` is not null))) and multiple equal('2023-03-28 00:00:00', `shift_ld_merge`.`exec_date`))"
          }
        ]
      }
    },
    {
      "table": "`shift_ld_merge`",
      "range_analysis": {
        "table_scan": {
          "rows": 149757,
          "cost": 32418
        },
        "potential_range_indexes": [
          {
            "index": "PRIMARY",
            "usable": true,
            "key_parts": [
              "id"
            ]
          },
          {
            "index": "idx_bus_id",
            "usable": true,
            "key_parts": [
              "bus_id",
              "id"
            ]
          },
          {
            "index": "idx_exec_date_site_id",
            "usable": true,
            "key_parts": [
              "exec_date",
              "site_id",
              "id"
            ]
          },
          {
            "index": "idx_bus_number",
            "usable": true,
            "key_parts": [
              "bus_number",
              "id"
            ]
          },
          {
            "index": "idx_ exec_date",
            "usable": true,
            "key_parts": [
              "exec_date",
              "id"
            ]
          },
          {
            "index": "idx_exec_date_route_id",
            "usable": true,
            "key_parts": [
              "exec_date",
              "route_id",
              "id"
            ]
          },
          {
            "index": "idx_route_id_exec_date",
            "usable": true,
            "key_parts": [
              "route_id",
              "exec_date",
              "id"
            ]
          }
        ],
        "setup_range_conditions": [
        ],
        "group_index_range": {
          "chosen": false,
          "cause": "no_join"
        },
        "analyzing_range_alternatives": {
          "range_scan_alternatives": [
            {
              "index": "idx_exec_date_site_id",
              "ranges": [
                "0x99afb80000 <= exec_date <= 0x99afb80000"
              ],
              "index_dives_for_eq_ranges": true,
              "rowid_ordered": false,
              "using_mrr": false,
              "index_only": false,
              "rows": 74878,
              "cost": 89855,
              "chosen": false,
              "cause": "cost"
            },
            {
              "index": "idx_ exec_date",
              "ranges": [
                "0x99afb80000 <= exec_date <= 0x99afb80000"
              ],
              "index_dives_for_eq_ranges": true,
              "rowid_ordered": true,
              "using_mrr": false,
              "index_only": false,
              "rows": 74878,
              "cost": 89855,
              "chosen": false,
              "cause": "cost"
            },
            {
              "index": "idx_exec_date_route_id",
              "ranges": [
                "0x99afb80000 <= exec_date <= 0x99afb80000"
              ],
              "index_dives_for_eq_ranges": true,
              "rowid_ordered": false,
              "using_mrr": false,
              "index_only": false,
              "rows": 74878,
              "cost": 89855,
              "chosen": false,
              "cause": "cost"
            },
            {
              "index": "idx_route_id_exec_date",
              "chosen": false,
              "cause": "unknown"
            }
          ],
          "analyzing_roworder_intersect": {
            "usable": false,
            "cause": "too_few_roworder_scans"
          }
        }
      }
    }
  ]
}

我发现区别：
select时的primary索引是not_applicable

"potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
update时，可用
        "potential_range_indexes": [
          {
            "index": "PRIMARY",
            "usable": true,
            "key_parts": [
              "id"
            ]
          },

然后update就去选择primary了。
所以为什么select时primary不可用,目前没有找到解释。

补充：非主键索引引起的死锁：

https://blog.youkuaiyun.com/qq_38787653/article/details/128553784
非主键索引主要是因为要回表：
A拿主键索引1，
B拿索引2（拿到了并回表去拿主键索引1，拿不到），
A拿索引2（拿不到）。
死锁。

总结（重要）：

1.走主键索引和非主键索引都可能产生死锁的情况，我们要做的就是不同事务不要让他查到冲突的数据。
2.mybatis会把数字字符串当成integer类型，可能会造成索引命中不了的问题。

mysql的几个有用的查询条件：
SELECT * FROM information_schema.innodb_trx;
查看事务
show engine innodb status
查询死锁情况
set optimizer_trace=“enabled=on”;
执行sql
select * from optimizer_trace;
set optimizer_trace=“enabled=off”;
查询优化器执行顺序。

参考：

https://zhuanlan.zhihu.com/p/427766633
https://blog.youkuaiyun.com/TANGYUXIANG1/article/details/127316951
https://zhuanlan.zhihu.com/p/76390004
https://blog.youkuaiyun.com/n88Lpo/article/details/127130498
https://blog.youkuaiyun.com/weixin_39545269/article/details/111289632
https://blog.youkuaiyun.com/weiguang102/article/details/119108947