MySQL 死锁异常排查和处理

已于 2022-11-23 10:24:38 修改
阅读量4.3k 收藏 7
点赞数 3
分类专栏： 数据库 异常 MySQL 文章标签： mysql java
于 2022-11-18 18:12:21 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010026928/article/details/127925144
版权
数据库 同时被 3 个专栏收录
4 篇文章 0 订阅
订阅专栏
MySQL
2 篇文章 0 订阅
订阅专栏
异常
1 篇文章 0 订阅
订阅专栏

在这里插入图片描述

场景

  • 删除车辆信息同时异步通知查询服务更新

  • 查询服务采用 insert into view 方式增加数据（导致行级锁）

  • 查询服务和删除车辆 争夺车辆表的锁，造成死锁

服务报错日志

2022-11-18 14:56:17.133 DEBUG 8 --- [nio-6004-exec-7] s.j.s.SQLErrorCodeSQLExceptionTranslator : Translating SQLException with SQL state '40001', error code '1213', message [Deadlock found when trying to get lock; try restarting transaction] for task [
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in com/redstarcloud/sc/info/car/biz/mapper/InfoCarMapper.java (best guess)
### The error may involve com.redstarcloud.sc.info.car.biz.mapper.InfoCarMapper.updateById-Inline
### The error occurred while setting parameters
### SQL: UPDATE info_car SET del_flag = ? WHERE id = ?
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2c6f7da]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2c6f7da]
2022-11-18 14:56:17.134 DEBUG 8 --- [nio-6004-exec-7] o.s.j.d.DataSourceTransactionManager     : Initiating transaction rollback
2022-11-18 14:56:17.134 DEBUG 8 --- [nio-6004-exec-7] o.s.j.d.DataSourceTransactionManager     : Rolling back JDBC transaction on Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@3a5a10c]
2022-11-18 14:56:17.135 DEBUG 8 --- [nio-6004-exec-7] o.s.j.d.DataSourceTransactionManager     : Releasing JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@3a5a10c] after transaction
2022-11-18 14:56:17.136 DEBUG 8 --- [nio-6004-exec-7] .m.m.a.ExceptionHandlerExceptionResolver : Using @ExceptionHandler com.redstarcloud.sc.info.car.biz.handler.GlobalExceptionHandler#handleException(Exception)
2022-11-18 14:56:17.138 ERROR 8 --- [nio-6004-exec-7] c.r.s.i.c.b.h.GlobalExceptionHandler     : GlobalExceptionHandler system exception
org.springframework.dao.DeadlockLoserDataAccessException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in com/redstarcloud/sc/info/car/biz/mapper/InfoCarMapper.java (best guess)
### The error may involve com.redstarcloud.sc.info.car.biz.mapper.InfoCarMapper.updateById-Inline
### The error occurred while setting parameters
### SQL: UPDATE info_car SET del_flag = ? WHERE id = ?
### 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:271)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
        at com.sun.proxy.$Proxy211.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$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
        at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
        at com.sun.proxy.$Proxy217.updateById(Unknown Source)
        at com.redstarcloud.sc.info.car.biz.service.impl.InfoCarServiceImpl.deleteInfoCarById(InfoCarServiceImpl.java:351)
        at com.redstarcloud.sc.info.car.biz.service.impl.InfoCarServiceImpl$$FastClassBySpringCGLIB$$7ed26d7e.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
        at com.redstarcloud.sc.info.car.biz.service.impl.InfoCarServiceImpl$$EnhancerBySpringCGLIB$$c3af8a71.deleteInfoCarById(<generated>)
        at com.redstarcloud.sc.info.car.biz.controller.InfoCarController.deleteCarById(InfoCarController.java:99)
        at com.redstarcloud.sc.info.car.biz.controller.InfoCarController$$FastClassBySpringCGLIB$$f4538816.invoke(<generated>)
......
省略
......
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.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.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3461)
        at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459)
        at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
        at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
        at sun.reflect.GeneratedMethodAccessor265.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 com.sun.proxy.$Proxy358.execute(Unknown Source)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
        at sun.reflect.GeneratedMethodAccessor873.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
        at com.sun.proxy.$Proxy356.update(Unknown Source)
        at sun.reflect.GeneratedMethodAccessor873.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
        at com.sun.proxy.$Proxy356.update(Unknown Source)
        at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:56)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
        at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.update(MybatisCachingExecutor.java:85)
        at sun.reflect.GeneratedMethodAccessor920.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
        at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:106)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
        at com.sun.proxy.$Proxy355.update(Unknown Source)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
        at sun.reflect.GeneratedMethodAccessor1038.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)
        ... 122 common frames omitted
2022-11-18 14:56:17.140 DEBUG 8 --- [nio-6004-exec-7] m.m.a.RequestResponseBodyMethodProcessor : Using 'application/json', given [application/json, text/plain, */*] and supported [application/json, application/*+json, application/json, application/*+json]
2022-11-18 14:56:17.141 DEBUG 8 --- [nio-6004-exec-7] m.m.a.RequestResponseBodyMethodProcessor : Writing [R(code=1, msg=系统异常, data=null)]
2022-11-18 14:56:17.141 DEBUG 8 --- [nio-6004-exec-7] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.dao.DeadlockLoserDataAccessException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in com/redstarcloud/sc/info/car/biz/mapper/InfoCarMapper.java (best guess)
### The error may involve com.redstarcloud.sc.info.car.biz.mapper.InfoCarMapper.updateById-Inline
### The error occurred while setting parameters
### SQL: UPDATE info_car SET del_flag = ? WHERE id = ?
### 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]
2022-11-18 14:56:17.142 DEBUG 8 --- [nio-6004-exec-7] o.s.web.servlet.DispatcherServlet        : Completed 200 OK
2022-11-18 14:56:17.142 DEBUG 8 --- [nio-6004-exec-7] s.s.w.c.SecurityContextPersistenceFilter : Cleared SecurityContextHolder to complete request
2022-11-18 14:56:18.514 DEBUG 8 --- [isson-netty-2-6] org.redisson.connection.DNSMonitor       : Request sent to resolve ip address for master host: 10.0.10.12
2022-11-18 14:56:18.517 DEBUG 8 --- [sson-netty-2-20] org.redisson.connection.DNSMonitor       : Resolved ip: /10.0.10.12 for master host: 10.0.10.12

关键日志

### SQL: UPDATE info_car SET del_flag = ? WHERE id = ?
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

从这两行可以得知，该错误是数据库的错误，是死锁错误异常而导致的回滚，关键 SQL（车辆软删除） 是：UPDATE info_car SET del_flag = ? WHERE id = ?

2.核心错误的调用方法是哪个，即事务开始的方法是哪个?

com.redstarcloud.sc.info.car.biz.service.impl.InfoCarServiceImpl.deleteInfoCarById(InfoCarServiceImpl.java:351)

数据库死锁日志

对应数据库执行，数据库死锁日志

SHOW ENGINE INNODB STATUS;


=====================================
2022-11-18 14:04:51 140530272577280 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 58 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 268422 srv_active, 0 srv_shutdown, 669542 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 141626
OS WAIT ARRAY INFO: signal count 2846865
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-11-18 13:46:45 140531872175872
*** (1) TRANSACTION:
TRANSACTION 19529038, ACTIVE 0 sec fetching rows
mysql tables in use 15, locked 15
LOCK WAIT 44 lock struct(s), heap size 3488, 862 row lock(s), undo log entries 1
MySQL thread id 102080, OS thread handle 140528418883328, query id 14694528 10.244.0.216 root executing
insert into _multi_info select * from v_multi_info where id='Vehic9'

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 547 page no 6 n bits 184 index PRIMARY of table `dcxz_sc_prod`.`info_car` trx id 19529038 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 8; hex 0000000000000001; asc         ;;
 1: len 6; hex 0000007fcea3; asc       ;;
 2: len 7; hex 01000001571031; asc     W 1;;
 3: len 9; hex e6b2aa313233343536; asc    123456;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: len 9; hex e6af94e4ba9ae8bfaa; asc          ;;
 9: len 6; hex e7baa2e889b2; asc       ;;
 10: SQL NULL;
 11: len 1; hex 30; asc 0;;
 12: len 8; hex 8000000000000001; asc         ;;
 13: len 5; hex 99ac7ae900; asc   z  ;;
 14: len 5; hex 99ad7721e3; asc   w! ;;
 15: SQL NULL;
 16: len 10; hex 434f3030303030303031; asc CO00000001;;
 17: SQL NULL;
 18: len 1; hex 30; asc 0;;
 19: len 1; hex 31; asc 1;;
 20: len 1; hex 31; asc 1;;
 21: SQL NULL;
 22: SQL NULL;
 23: len 8; hex 8000000000000002; asc         ;;
 24: SQL NULL;
 25: SQL NULL;
 26: len 5; hex 99a5fa0000; asc      ;;
 27: len 5; hex 99b63b7efb; asc   ;~ ;;
 28: len 5; hex 6170705f63; asc app_c;;
 29: len 30; hex 687474703a2f2f34372e3130332e3133382e38363a31383133392f736d61; asc http://47.103.138.86:18139/sma; (total 85 bytes);
 30: len 4; hex 80000000; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 8; hex 0000000000000002; asc         ;;
 1: len 6; hex 0000007fcea3; asc       ;;
 2: len 7; hex 01000001571062; asc     W b;;
 3: len 10; hex e9999541313238373137; asc    A128717;;
 4: len 8; hex 8000000000000008; asc         ;;
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: len 0; hex ; asc ;;
 9: len 0; hex ; asc ;;
 10: len 0; hex ; asc ;;
 11: len 1; hex 31; asc 1;;
 12: len 8; hex 8000000000000005; asc         ;;
 13: len 5; hex 99ac7f08ae; asc      ;;
 14: len 5; hex 99ad7721e3; asc   w! ;;
 15: SQL NULL;
 16: len 10; hex 434f3030303030303138; asc CO00000018;;
 17: len 8; hex 8000000000000002; asc         ;;
 18: len 1; hex 30; asc 0;;
 19: len 1; hex 31; asc 1;;
 20: SQL NULL;
 21: SQL NULL;
 22: SQL NULL;
 23: SQL NULL;
 24: SQL NULL;
 25: len 0; hex ; asc ;;
 26: SQL NULL;
 27: SQL NULL;
 28: len 2; hex 5043; asc PC;;
 29: len 0; hex ; asc ;;
 30: len 4; hex 80000000; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 8; hex 0000000000000003; asc         ;;
 1: len 6; hex 0000007fcea3; asc       ;;
 2: len 7; hex 01000001571093; asc     W  ;;
 3: len 10; hex e9999541313233343536; asc    A123456;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: SQL NULL;
 9: SQL NULL;
 10: SQL NULL;
 11: len 1; hex 30; asc 0;;
 12: len 8; hex 8000000000000001; asc         ;;
 13: len 5; hex 99ac8ef448; asc     H;;
 14: len 5; hex 99ad7721e3; asc   w! ;;
 15: SQL NULL;
 16: len 10; hex 434f3030303030303032; asc CO00000002;;
 17: SQL NULL;
 18: len 1; hex 30; asc 0;;
 19: len 1; hex 31; asc 1;;
 20: len 1; hex 31; asc 1;;
 21: SQL NULL;
 22: SQL NULL;
 23: len 8; hex 8000000000000004; asc         ;;
 24: SQL NULL;
 25: SQL NULL;
 26: SQL NULL;
 27: SQL NULL;
 28: len 5; hex 6170705f63; asc app_c;;
 29: SQL NULL;
 30: len 4; hex 80000000; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 8; hex 0000000000000004; asc         ;;
 1: len 6; hex 0000007fcea3; asc       ;;
 2: len 7; hex 010000015710c4; asc     W  ;;
 3: len 9; hex e99995554651393930; asc    UFQ990;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: SQL NULL;
 9: SQL NULL;
 10: SQL NULL;
 11: len 1; hex 31; asc 1;;
 12: len 8; hex 8000000000000001; asc         ;;
 13: len 5; hex 99ac9108bb; asc      ;;
 14: len 5; hex 99ad7721e3; asc   w! ;;
 15: SQL NULL;
 16: len 10; hex 434f3030303030303138; asc CO00000018;;
 17: len 8; hex 8000000000000002; asc         ;;
 18: len 1; hex 30; asc 0;;
 19: len 1; hex 31; asc 1;;
 20: len 1; hex 31; asc 1;;
 21: SQL NULL;
 22: SQL NULL;
 23: len 8; hex 8000000000000007; asc         ;;
 24: SQL NULL;
 25: SQL NULL;
 26: SQL NULL;
 27: SQL NULL;
 28: len 5; hex 6170705f63; asc app_c;;
 29: len 30; hex 687474703a2f2f34372e3130332e3133382e38363a31383133392f736d61; asc http://47.103.138.86:18139/sma; (total 85 bytes);
 30: len 4; hex 80000000; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 8; hex 0000000000000005; asc         ;;
 1: len 6; hex 0000007fcea3; asc       ;;
 2: len 7; hex 010000015710f5; asc     W  ;;
 3: len 9; hex e99995553838333838; asc    U88388;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: len 6; hex e59381e7898c; asc       ;;
 9: len 6; hex e799bde889b2; asc       ;;
 10: SQL NULL;
 11: len 1; hex 31; asc 1;;
 12: len 8; hex 8000000000000001; asc         ;;
 13: len 5; hex 99ac910b44; asc     D;;
 14: len 5; hex 99ad7721e3; asc   w! ;;
 15: SQL NULL;
 16: len 10; hex 434f3030303030303138; asc CO00000018;;
 17: len 8; hex 8000000000000002; asc         ;;
 18: len 1; hex 30; asc 0;;
 19: len 1; hex 31; asc 1;;
 20: len 1; hex 31; asc 1;;
 21: SQL NULL;
 22: SQL NULL;
 23: len 8; hex 8000000000000007; asc         ;;
 24: SQL NULL;
 25: len 6; hex e8bda6e4bd8d; asc       ;;
 26: len 5; hex 99ac900000; asc      ;;
 27: len 5; hex 99acbc0000; asc      ;;
 28: len 2; hex 7063; asc pc;;
 29: SQL NULL;
 30: len 4; hex 80000000; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 8; hex 0000000000000006; asc         ;;
 1: len 6; hex 0000007fcea3; asc       ;;
 2: len 7; hex 01000001571126; asc     W &;;
 3: len 9; hex e99995553730363338; asc    U70638;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: len 6; hex e5ae9de9a9ac; asc       ;;
 9: len 6; hex e799bde889b2; asc       ;;
 10: len 12; hex e5a487e6b3a8e4bfa1e681af; asc             ;;
 11: len 1; hex 30; asc 0;;
 12: len 8; hex 8000000000000001; asc         ;;
 13: len 5; hex 99ad04b4b3; asc      ;;
 14: len 5; hex 99ad7721e3; asc   w! ;;
 15: SQL NULL;
 16: len 10; hex 434f3030303030303138; asc CO00000018;;
 17: len 8; hex 8000000000000002; asc         ;;
 18: len 1; hex 30; asc 0;;
 19: len 1; hex 31; asc 1;;
 20: len 1; hex 31; asc 1;;
 21: SQL NULL;
 22: SQL NULL;
 23: len 8; hex 8000000000000007; asc         ;;
 24: SQL NULL;
 25: len 12; hex e59cb0e4b88be8bda6e5ba93; asc             ;;
 26: len 5; hex 99ad040000; asc      ;;
 27: len 5; hex 99ad0f7efb; asc    ~ ;;
 28: len 5; hex 6170705f63; asc app_c;;
 29: len 30; hex 687474703a2f2f34372e3130332e3133382e38363a31383133392f736d61; asc http://47.103.138.86:18139/sma; (total 85 bytes);
 30: len 4; hex 80000000; asc     ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 547 page no 6 n bits 184 index PRIMARY of table `dcxz_sc_prod`.`info_car` trx id 19529038 lock mode S waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 8; hex 0000000000000007; asc         ;;
 1: len 6; hex 00000129fd49; asc    ) I;;
 2: len 7; hex 01000002b92c9c; asc      , ;;
 3: len 9; hex e6b2aa413939393939; asc    A99999;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: len 6; hex e5a594e9a9b0; asc       ;;
 9: len 6; hex e8939de889b2; asc       ;;
 10: len 0; hex ; asc ;;
 11: len 1; hex 31; asc 1;;
 12: len 8; hex 8000000000000001; asc         ;;
 13: len 5; hex 99ad75403b; asc   u@;;;
 14: len 5; hex 99ae64dbad; asc   d  ;;
 15: SQL NULL;
 16: len 10; hex 434f3030303030303037; asc CO00000007;;
 17: len 8; hex 80000000000002dd; asc         ;;
 18: len 1; hex 30; asc 0;;
 19: len 1; hex 31; asc 1;;
 20: SQL NULL;
 21: SQL NULL;
 22: SQL NULL;
 23: SQL NULL;
 24: SQL NULL;
 25: len 0; hex ; asc ;;
 26: SQL NULL;
 27: SQL NULL;
 28: len 2; hex 5043; asc PC;;
 29: len 0; hex ; asc ;;
 30: len 4; hex 80000000; asc     ;;


*** (2) TRANSACTION:
TRANSACTION 19529033, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 12 lock struct(s), heap size 1128, 60 row lock(s), undo log entries 5
MySQL thread id 102078, OS thread handle 140528423110400, query id 14694559 10.0.10.11 root updating
UPDATE info_car SET del_flag = '1' WHERE id = 6

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 547 page no 6 n bits 184 index PRIMARY of table `dcxz_sc_prod`.`info_car` trx id 19529033 lock_mode X locks rec but not gap
Record lock, heap no 8 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 8; hex 0000000000000007; asc         ;;
 1: len 6; hex 00000129fd49; asc    ) I;;
 2: len 7; hex 01000002b92c9c; asc      , ;;
 3: len 9; hex e6b2aa413939393939; asc    A99999;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: len 6; hex e5a594e9a9b0; asc       ;;
 9: len 6; hex e8939de889b2; asc       ;;
 10: len 0; hex ; asc ;;
 11: len 1; hex 31; asc 1;;
 12: len 8; hex 8000000000000001; asc         ;;
 13: len 5; hex 99ad75403b; asc   u@;;;
 14: len 5; hex 99ae64dbad; asc   d  ;;
 15: SQL NULL;
 16: len 10; hex 434f3030303030303037; asc CO00000007;;
 17: len 8; hex 80000000000002dd; asc         ;;
 18: len 1; hex 30; asc 0;;
 19: len 1; hex 31; asc 1;;
 20: SQL NULL;
 21: SQL NULL;
 22: SQL NULL;
 23: SQL NULL;
 24: SQL NULL;
 25: len 0; hex ; asc ;;
 26: SQL NULL;
 27: SQL NULL;
 28: len 2; hex 5043; asc PC;;
 29: len 0; hex ; asc ;;
 30: len 4; hex 80000000; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 547 page no 6 n bits 184 index PRIMARY of table `dcxz_sc_prod`.`info_car` trx id 19529033 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 8; hex 0000000000000006; asc         ;;
 1: len 6; hex 0000007fcea3; asc       ;;
 2: len 7; hex 01000001571126; asc     W &;;
 3: len 9; hex e99995553730363338; asc    U70638;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: len 6; hex e5ae9de9a9ac; asc       ;;
 9: len 6; hex e799bde889b2; asc       ;;
 10: len 12; hex e5a487e6b3a8e4bfa1e681af; asc             ;;
 11: len 1; hex 30; asc 0;;
 12: len 8; hex 8000000000000001; asc         ;;
 13: len 5; hex 99ad04b4b3; asc      ;;
 14: len 5; hex 99ad7721e3; asc   w! ;;
 15: SQL NULL;
 16: len 10; hex 434f3030303030303138; asc CO00000018;;
 17: len 8; hex 8000000000000002; asc         ;;
 18: len 1; hex 30; asc 0;;
 19: len 1; hex 31; asc 1;;
 20: len 1; hex 31; asc 1;;
 21: SQL NULL;
 22: SQL NULL;
 23: len 8; hex 8000000000000007; asc         ;;
 24: SQL NULL;
 25: len 12; hex e59cb0e4b88be8bda6e5ba93; asc             ;;
 26: len 5; hex 99ad040000; asc      ;;
 27: len 5; hex 99ad0f7efb; asc    ~ ;;
 28: len 5; hex 6170705f63; asc app_c;;
 29: len 30; hex 687474703a2f2f34372e3130332e3133382e38363a31383133392f736d61; asc http://47.103.138.86:18139/sma; (total 85 bytes);
 30: len 4; hex 80000000; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 19530845
Purge done for trx's n:o < 19530842 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422007319659080, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319679280, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319666352, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319651808, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319680896, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319680088, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319649384, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319663928, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319689784, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319688976, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319670392, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319643728, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319688168, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319687360, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319686552, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319684936, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319659888, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319654232, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319684128, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319646960, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319677664, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319675240, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319671200, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319662312, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319651000, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319645344, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319668776, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319656656, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319652616, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319646152, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319676856, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319667160, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319661504, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319657464, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319676048, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319667968, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319674432, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319673624, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319672816, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319648576, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319653424, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319647768, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319642112, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319655848, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319663120, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319642920, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319683320, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319678472, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319644536, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319669584, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319664736, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319660696, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319641304, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319665544, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319655040, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319658272, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319640496, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422007319639688, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 71
1151794 OS file reads, 7482189 OS file writes, 5555109 OS fsyncs
6.20 reads/s, 16384 avg bytes/read, 5.02 writes/s, 4.12 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 18, seg size 20, 5848 merges
merged operations:
 insert 8619, delete mark 24282, delete 374
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 237 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 7 buffer(s)
4810.54 hash searches/s, 217.20 non-hash searches/s
---
LOG
---
Log sequence number          5559939411
Log buffer assigned up to    5559939411
Log buffer completed up to   5559939411
Log written up to            5559939411
Log flushed up to            5559939411
Added dirty pages up to      5559939411
Pages flushed up to          5559937109
Last checkpoint at           5559937109
2816524 log i/o's done, 2.17 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 4523433
Buffer pool size   8192
Free buffers       1024
Database pages     6918
Old database pages 2533
Modified db pages  52
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 196086812, not young 50588523
207.84 youngs/s, 9.53 non-youngs/s
Pages read 1150570, created 58914, written 3376405
6.20 reads/s, 0.00 creates/s, 1.86 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 13 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6918, unzip_LRU len: 0
I/O sum[129]:cur[298], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=2340, Main thread ID=140531855390464 , state=sleeping
Number of rows inserted 35236029, updated 466521, deleted 68605, read 10517903506
0.09 inserts/s, 0.45 updates/s, 0.00 deletes/s, 5028.98 reads/s
Number of system rows inserted 89, updated 3784, deleted 44, read 3292174
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

关键点总结如下：

1.该库中最近一次死锁发生的时间是什么时候？

其中：

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

2022-11-18 14:04:51 140530272577280 INNODB MONITOR OUTPUT

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

这段记录的是查询死锁日志的时间

------------------------

LATEST DETECTED DEADLOCK

------------------------

这段后面记录的就是此次死锁的信息，分为几部分

1、事务1信息

也就是这一部分：

*** (1) TRANSACTION:
TRANSACTION 19529038, ACTIVE 0 sec fetching rows
mysql tables in use 15, locked 15
LOCK WAIT 44 lock struct(s), heap size 3488, 862 row lock(s), undo log entries 1
MySQL thread id 102080, OS thread handle 140528418883328, query id 14694528 10.244.0.216 root executing
insert into _multi_info select * from v_multi_info where id='Vehic9'

其中：

TRANSACTION 19529038，是此事务的id。

ACTIVE 0 sec，活跃时间0秒。

mysql tables in use 15, locked 15，表示此事务修改了十五个表，锁了十五行数据。

MySQL thread id 102080，这是线程id

query id 14694528，这是查询id

10.244.0.216 root executing，数据库ip地址，账号，执行语句。

insert into _multi_info select * from v_multi_info where id=‘Vehic9’，这是正在执行的sql，从视图获取数据插入表中。

重点 MySQL的insert into select 引发锁表

部分视图内容

create or replace view backend_for_frontend.v_multi_info as
(
......省略
select concat('Vehic', t.id), car_number, 'VEHICLE', t.community_code, 'SaaTe_R_S_Clouds', now(), t.id
from info_car as t
where t.del_flag = '0'
  and car_black = '0'
union all
select concat('BlaVe', t.id), car_number, 'BLACKLISTED_VEHICLE', t.community_code, 'SaaTe_R_S_Clouds', now(), t.id
from info_car as t
where t.del_flag = '0'
  and car_black = '1'
......省略
    );

2、事务1持有的锁

也就是这段：

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 547 page no 6 n bits 184 index PRIMARY of table `dcxz_sc_prod`.`info_car` trx id 19529038 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 8; hex 0000000000000001; asc         ;;
 1: len 6; hex 0000007fcea3; asc       ;;
 2: len 7; hex 01000001571031; asc     W 1;;
 3: len 9; hex e6b2aa313233343536; asc    123456;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: len 9; hex e6af94e4ba9ae8bfaa; asc          ;;
 9: len 6; hex e7baa2e889b2; asc       ;;
 10: SQL NULL;
 11: len 1; hex 30; asc 0;;

其中：

RECORD LOCKS，表示持有的是行级锁。

index PRIMARY，表示锁的是主键索引。

table dcxz_sc_prod.info_car，表示锁的具体是哪个表。

trx id 19529038，事务id，和上面的TRANSACTION相同。

lock mode S，锁模式：共享锁。（X：排他锁，S：共享锁）

3、事务1正在等待的锁

也就是这段：

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 547 page no 6 n bits 184 index PRIMARY of table `dcxz_sc_prod`.`info_car` trx id 19529038 lock mode S waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 8; hex 0000000000000007; asc         ;;
 1: len 6; hex 00000129fd49; asc    ) I;;
 2: len 7; hex 01000002b92c9c; asc      , ;;
 3: len 9; hex e6b2aa413939393939; asc    A99999;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: len 6; hex e5a594e9a9b0; asc       ;;
 9: len 6; hex e8939de889b2; asc       ;;
 10: len 0; hex ; asc ;;
 11: len 1; hex 31; asc 1;;
 12: len 8; hex 8000000000000001; asc         ;;
 13: len 5; hex 99ad75403b; asc   u@;;;
 14: len 5; hex 99ae64dbad; asc   d  ;;
 15: SQL NULL;
 16: len 10; hex 434f3030303030303037; asc CO00000007;;
 17: len 8; hex 80000000000002dd; asc         ;;
 18: len 1; hex 30; asc 0;;
 19: len 1; hex 31; asc 1;;
 20: SQL NULL;
 21: SQL NULL;
 22: SQL NULL;
 23: SQL NULL;
 24: SQL NULL;
 25: len 0; hex ; asc ;;
 26: SQL NULL;
 27: SQL NULL;
 28: len 2; hex 5043; asc PC;;
 29: len 0; hex ; asc ;;
 30: len 4; hex 80000000; asc     ;;

其中：

index PRIMARY，表示等待的是主键的锁。

table dcxz_sc_prod.info_car，表示等待的表。

trx id 19529038，当前事务1的id。注意这里不是持有目标锁的事务的id，而是当前事务id。

**lock mode S **，表示目标锁是排它锁。

waiting，表示当前事务正在等待。

后面的0至30，表示等待的行

4、事务2信息

也就是这一段：

*** (2) TRANSACTION:
TRANSACTION 19529033, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 12 lock struct(s), heap size 1128, 60 row lock(s), undo log entries 5
MySQL thread id 102078, OS thread handle 140528423110400, query id 14694559 10.0.10.11 root updating
UPDATE info_car SET del_flag = '1' WHERE id = 6

格式和事务1信息相同。
starting index read，事务当前正在根据索引读取数据。

这个描述还有其他情况：

  1. fetching rows 表示事务状态在row_search_for_mysql中被设置，表示正在查找记录。
  2. updating or deleting 表示事务已经真正进入了Update/delete的函数逻辑（row_update_for_mysql）
  3. thread declared inside InnoDB 说明事务已经进入innodb层。通常而言 不在innodb层的事务大部分是会被回滚的。

TRANSACTION 102078，表示事务id

UPDATE info_car SET del_flag = ‘1’ WHERE id = 6，表示事务2正在执行的sql。

5、事务2正在持有的锁

也就是这段：

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 547 page no 6 n bits 184 index PRIMARY of table `dcxz_sc_prod`.`info_car` trx id 19529033 lock_mode X locks rec but not gap
Record lock, heap no 8 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 8; hex 0000000000000007; asc         ;;
 1: len 6; hex 00000129fd49; asc    ) I;;
 2: len 7; hex 01000002b92c9c; asc      , ;;
 3: len 9; hex e6b2aa413939393939; asc    A99999;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: len 6; hex e5a594e9a9b0; asc       ;;
 9: len 6; hex e8939de889b2; asc       ;;
 10: len 0; hex ; asc ;;
 11: len 1; hex 31; asc 1;;
 12: len 8; hex 8000000000000001; asc         ;;
 13: len 5; hex 99ad75403b; asc   u@;;;
 14: len 5; hex 99ae64dbad; asc   d  ;;
 15: SQL NULL;
 16: len 10; hex 434f3030303030303037; asc CO00000007;;
 17: len 8; hex 80000000000002dd; asc         ;;
 18: len 1; hex 30; asc 0;;
 19: len 1; hex 31; asc 1;;
 20: SQL NULL;
 21: SQL NULL;
 22: SQL NULL;
 23: SQL NULL;
 24: SQL NULL;
 25: len 0; hex ; asc ;;
 26: SQL NULL;
 27: SQL NULL;
 28: len 2; hex 5043; asc PC;;
 29: len 0; hex ; asc ;;
 30: len 4; hex 80000000; asc     ;;

lock_mode X，锁模式：排他锁。（X：排他锁，S：共享锁）
but not gap，非间隙锁

6、事务2正在等待的锁

也就是这段：

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 547 page no 6 n bits 184 index PRIMARY of table `dcxz_sc_prod`.`info_car` trx id 19529033 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
 0: len 8; hex 0000000000000006; asc         ;;
 1: len 6; hex 0000007fcea3; asc       ;;
 2: len 7; hex 01000001571126; asc     W &;;
 3: len 9; hex e99995553730363338; asc    U70638;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: len 6; hex e5ae9de9a9ac; asc       ;;
 9: len 6; hex e799bde889b2; asc       ;;
 10: len 12; hex e5a487e6b3a8e4bfa1e681af; asc             ;;
 11: len 1; hex 30; asc 0;;
 12: len 8; hex 8000000000000001; asc         ;;
 13: len 5; hex 99ad04b4b3; asc      ;;
 14: len 5; hex 99ad7721e3; asc   w! ;;
 15: SQL NULL;
 16: len 10; hex 434f3030303030303138; asc CO00000018;;
 17: len 8; hex 8000000000000002; asc         ;;
 18: len 1; hex 30; asc 0;;
 19: len 1; hex 31; asc 1;;
 20: len 1; hex 31; asc 1;;
 21: SQL NULL;
 22: SQL NULL;
 23: len 8; hex 8000000000000007; asc         ;;
 24: SQL NULL;
 25: len 12; hex e59cb0e4b88be8bda6e5ba93; asc             ;;
 26: len 5; hex 99ad040000; asc      ;;
 27: len 5; hex 99ad0f7efb; asc    ~ ;;
 28: len 5; hex 6170705f63; asc app_c;;
 29: len 30; hex 687474703a2f2f34372e3130332e3133382e38363a31383133392f736d61; asc http://47.103.138.86:18139/sma; (total 85 bytes);
 30: len 4; hex 80000000; asc     ;;

7、死锁处理结果

也就是这段：

*** WE ROLL BACK TRANSACTION (2)

表示MySQL最终决定回滚事务2，也就是上面的事务B，这和上面事务B返回的死锁信息是一致的。

关于MySQL的死锁

MySQL的死锁指的是两个事务互相等待的场景，这种循环等待理论上不会有尽头。

比如事务A持有行1的锁，事务B持有行2的锁，

然后事务A试图获取行2的锁，事务B试图获取行1的锁，

这样事务A要等待事务B释放行2的锁，事务B要等待事务A释放行1的锁，

两个事务互相等待，谁也提交不了。

这种情况下MySQL会选择中断并回滚其中一个事务，使得另一个事务可以提交。

MySQL会记录死锁的日志。

模拟死锁的场景(和上面场景无关)

新建一个表，添加两条数据：

img

创建两个事务，事务执行的sql分别是：

事务A：

set autocommit=0;
update medicine_control set current_count=1 where id='1';

update medicine_control set current_count=1 where id='2';

COMMIT;

事务B：

set autocommit=0;

update medicine_control set current_count=2 where id='2';

update medicine_control set current_count=2 where id='1';

COMMIT;

可见，事务A先改id=1的数据再改id=2的数据，事务B相反，先改id=2的数据再改id=1的数据。

两个事务sql的执行顺序如下：

步骤事务A事务A
1set autocommit=0;
2update medicine_controlset current_count=1where id=‘1’;
3set autocommit=0;
4update medicine_controlset current_count=2where id=‘2’;
5update medicine_controlset current_count=1where id=‘2’;
6update medicine_controlset current_count=2where id=‘1’;

对每一步的说明：

1，事务A开始事务。

2，事务A修改id=1的数据，持有了该行的锁。

3，事务B开始事务。

4，事务B修改id=2的数据，持有了该行的锁。

5，事务A试图修改id=2的数据，此行的锁被事务B持有，于是事务A等待事务B释放锁。

事务B提交或回滚都能释放锁。

6，事务B试图修改id=1的数据，此行的锁被事务A持有，于是事务B等待事务A释放锁。

事务A提交或回滚都能释放锁。当执行到这一步时，MySQL会立即检测到死锁，并且中断并回滚其中一个事务。此次回滚的是事务B，执行SQL的返回信息是这样的：

[SQL]update medicine_control set current_count=2 where id=‘1’;

[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

关于mysql的八种锁

1，行锁（Record Locks）

行锁是作用在索引上的。

2，间隙锁（Gap Locks）

间隙锁是锁住一个区间的锁。

这个区间是一个开区间，范围是从某个存在的值向左直到比他小的第一个存在的值，所以间隙锁包含的内容就是在查询范围内，而又不存在的数据区间。

比如有id分别是1,10,20，要修改id<15的数据，那么生成的间隙锁有以下这些：(-∞,1)，(1,10)，(10,20)，此时若有其他事务想要插入id=11的数据，则需要等待。

间隙锁是不互斥的。

作用是防止其他事务在区间内添加记录，而本事务可以在区间内添加记录，从而防止幻读。

在可重复读这种隔离级别下会启用间隙锁，而在读未提交和读已提交两种隔离级别下，即使使用select … in share mode或select … for update，也不会有间隙锁，无法防止幻读。

3，临键锁（Next-key Locks）

临键锁=间隙锁+行锁，于是临键锁的区域是一个左开右闭的区间。

隔离级别是可重复读时，select … in share mode或select … for update会使用临键锁，防止幻读。普通select语句是快照读，不能防止幻读。

4，共享锁/排他锁（Shared and Exclusive Locks）

共享锁和排它锁都是行锁。共享锁用于事务并发读取，比如select … in share mode。排它锁用于事务并发更新或删除。比如select … for update

5，意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）

意向共享锁和意向排他锁都是表级锁。

官方文档中说，事务获得共享锁前要先获得意向共享锁，获得排它锁前要先获得意向排它锁。

意向排它锁互相之间是兼容的。

6，插入意向锁（Insert Intention Locks）

插入意向锁锁的是一个点，是一种特殊的间隙锁，用于并发插入。

插入意向锁和间隙锁互斥。插入意向锁互相不互斥。

7，自增锁（Auto-inc Locks）

自增锁用于事务中插入自增字段。5.1版本前是表锁，5.1及以后版本是互斥轻量锁。

自增所相关的变量有：

auto_increment_offset，初始值

auto_increment_increment，每次增加的数量

innodb_autoinc_lock_mode，自增锁模式

其中：

innodb_autoinc_lock_mode=0，传统方式，每次都产生表锁。此为5.1版本前的默认配置。

innodb_autoinc_lock_mode=1，连续方式。产生轻量锁，申请到自增锁就将锁释放，simple insert会获得批量的锁，保证连续插入。此为5.2版本后的默认配置。

innodb_autoinc_lock_mode=2，交错锁定方式。不锁表，并发速度最快。但最终产生的序列号和执行的先后顺序可能不一致，也可能断裂。

关于死锁的解锁

InnoDB存储引擎会选择回滚undo量最小的事务

伟大的大威
关注
  • 3
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
一次神奇的MySQL死锁排查记录
09-09
主要给大家介绍了一次神奇的MySQL死锁排查的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用Mysql具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧
MySQL redo死锁问题排查及解决过程分析
09-09
被告知在多实例场景下 MySQL Server hang 住，无法测试下去，原生版本不存在这个问题，而新版本上出现了这个问题，不禁心头一颤，心中不禁感到奇怪，还好现场环境还在，为排查问题提供了一个好的环境，随即便投入到紧张的问题排查过程当中
关于 Mysql 锁冲突错误 以及 MySql查询默认并不区分大小写
最新发布
IT技术领域深耕10年+，北京大厂闯荡5年+
03-16 596
最近在一套程序中遇到了 Mysql 报错：Deadlock found when trying to get lock;try restarting transaction，是发生在对一张 mysql表进行读取最新的一条记录并进行 for update 锁操作的时候，因为业务是多进程并发执行的，会出现基本同时执行这段逻辑，即事务开始-查询表记录-for update锁定行记录-操作数据表-提交事务。而在多进程执行时，导致死锁现象。
一次Mysql死锁排查过程的全纪录
09-09
在测试环境测试给用户并发发送卡券时，出现了死锁，通过查找相关的资料解决了这个，所以想着总结出来，所以下面这篇文章主要是关于一次Mysql死锁排查过程的全纪录，需要的朋友可以参考下，希望大家从中能有所帮助。
Mysql解除死锁状态排查
03-22
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; show status like ‘%lock%’等语句排查详解
一次mysql死锁的排查过程
12-14
背景 　　以前接触到的数据库死锁，都是批量更新时加锁顺序不一致而导致的死锁，但是上周却遇到了一个很难理解的死锁。借着这个机会又重新学习了一下mysql的死锁知识以及常见的死锁场景。在多方调研以及和同事们的讨论下终于发现了这个死锁问题的成因，收获颇多。虽然是后端程序员，我们不需要像DBA一样深入地去分析与锁相关的源码，但是如果我们能够掌握基本的死锁排查方法，对我们的日常开发还是大有裨益的。 　　PS：本文不会介绍死锁的基本知识，mysql的加锁原理可以参考本文的参考资料提供的链接。 　　死锁起因 　　先介绍一下数据库和表情况，因为涉及到公司内部真是的数据，所以以下都做了模拟，不会影响具体
mysql死锁排查及解决
sun13212715744的博客
09-18 1757
MySQL死锁是在多个并发事务同时请求相同资源时发生的一种情况，其中每个事务都在等待对方释放资源，从而导致数据库无法继续执行。在生产环境中，建议与数据库管理员一起处理死锁问题，并确保数据库的配置和性能优化是合适的。使用合适的事务隔离级别： 在开发时选择适当的事务隔离级别，以确保事务在并发执行时不会频繁地发生死锁。使用索引和合适的查询方式： 通过为数据库表添加合适的索引，可以降低锁的范围，从而减少死锁的可能性。优化查询： 通过优化查询，降低死锁发生的可能性，例如通过合理使用索引、减少事务中锁的范围等。
mysql死锁的排查和解决
u013452335的博客
08-09 2240
mysql的死锁
Mysql死锁问题排查和解决
hbhe0316的专栏
11-28 702
Mysql锁分析文档:https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html 1.查看Mysql隔离级别https://www.cndba.cn/hbhe0316/article/22636https://www.cndba.cn/hbhe0316/article/22636 mysql> select ...
Mysql死锁问题如何排查和解决
qq_43842093的博客
05-03 3764
发生死锁了，如何排查和解决呢？本文将跟你一起探讨这个问题准备好数据环境模拟死锁案发分析死锁日志分析死锁结果在分析死锁日志前，先做一下锁介绍，哈哈~最后，遇到死锁问题，我们应该怎么分析呢？模拟死锁场景查看死锁日志找出死锁SQLSQL加锁分析，这个可以去官网看哈分析死锁日志（持有什么锁，等待什么锁）熟悉锁模式兼容矩阵，InnoDB存储引擎中锁的兼容性矩阵。
mysql 数据库死锁原因及解决办法
09-10
主要介绍了mysql 数据库死锁原因及解决办法,需要的朋友可以参考下
Mysql 并发update死锁
weixin_38498337的博客
03-22 3284
更新update死锁问题MySQL## 标题 如何利用乐观锁来解决并发问题，但是项目推到线上后就报错了，如下 [ERROR] com.hong.coupon.app.CouponReceiveAppServiceImpl {CouponReceiveAppServiceImpl.java:50} -Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlo
Mysql频繁操作出现锁表问题
weixin_42972832的博客
06-30 2189
Mysql频繁操作出现锁表问题
MySQLTransactionRollbackException: Lock wait timeout exceeded； try restarting transaction
aayygg1234的专栏
07-07 591
运行应用程序出现以下报错,说明程序中之前的执行的语句有未提交的写操作，或者手动执行sql语句的时候没有commit,此时需要检查是否设置了自动提交，或者程序是否正常提交执行的sql. ### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction ; Lock wait timeout exceeded; try
mySql Translating SQLException with SQL state '42000', error code '1064'
猎人在吃肉
12-06 9289
今天创建表，在执行insert句语时，mySql 报错了 Translating SQLException with SQL state '42000', error code '1064' 。 建表语法： CREATE TABLE `app_sms_t` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `mobile` va...
MYSQL报错：MySQLTransactionRollbackException: Deadlock found when trying to get lock； try restarting tr
升级打怪
10-18 3034
高并发导致表死锁
Mysql 之 死锁问题如何排查和解决
enjoy.day
04-19 133
mysql死锁
Mysql死锁排查过程?
05-04
Mysql死锁的排查过程一般如下： 1. 确认是否是死锁 通过 Mysql 的错误日志或者 SHOW ENGINE INNODB STATUS 命令查看是否存在死锁，如果存在则可以进入下一步排查。 2. 定位死锁 可以通过 Mysql 的错误日志或者 SHOW ENGINE INNODB STATUS 命令查看死锁的详细信息，包括死锁的事务 ID、锁定的资源、等待的锁等信息。 3. 分析死锁原因 通过分析死锁的信息，找出出现死锁的原因，一般有以下几个可能： - 事务并发度过高 - 事务锁定资源的顺序不一致 - 索引不合理 - 数据库设计不合理 4. 解决死锁 根据分析出的死锁原因，可以采取相应的措施来解决死锁问题，比如： - 降低并发度 - 修改事务锁定资源的顺序 - 优化索引 - 优化数据库设计 5. 预防死锁 为了避免死锁的发生，可以采取以下措施： - 减少事务持有锁的时间 - 尽量避免跨表更新 - 优化事务锁定资源的顺序 - 优化数据库设计

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值