背景

2019 双十一前走查异常日志，发现有一个死锁日志，现将排查过程记录如下。

排查过程

  1. 查看业务死锁日志，业务代码

全部业务死锁日志如下：

  1. 2019-11-05 15:02:44,990 |0b51153515729373648846445e1bf6| [HSFBizProcessor-DEFAULT-8-thread-47] ERROR c.a.c.c.manager.SessionUserManager - quitSession
  2. org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:
  3. ### Error updating database.  Cause: ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL] Error occurs when execute on GROUP 'CCO_XSPACE_SESSION_0003_GROUP' ATOM 'db198205096_cloud_eu126_cco_xspace_session_0003_3417': Deadlock found when trying to get lock; try restarting transaction More: [http://middleware.alibaba-inc.com/faq/faqByFaqCode.html?faqCode=TDDL-4614]
  4. ### The error may exist in class path resource [mappers/session_users_sqlmap_mapping.xml]
  5. ### The error may involve com.alibaba.cxdc.connected.dao.OcsSessionUserMapper.delete-Inline
  6. ### The error occurred while setting parameters
  7. ### SQL: delete from           connected_session_users         where            sid = ?                                                                    and user_id = ?
  8. ### Cause: ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL] Error occurs when execute on GROUP 'CCO_XSPACE_SESSION_0003_GROUP' ATOM 'db198205096_cloud_eu126_cco_xspace_session_0003_3417': Deadlock found when trying to get lock; try restarting transaction More: [http://middleware.alibaba-inc.com/faq/faqByFaqCode.html?faqCode=TDDL-4614]
  9.     at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
  10.     at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
  11.     at com.sun.proxy.$Proxy170.delete(Unknown Source)
  12.     at org.mybatis.spring.SqlSessionTemplate.delete(SqlSessionTemplate.java:310)
  13.     at com.alibaba.cxdc.connected.dao.base.DBCrmDAOBase.switchDelete(DBCrmDAOBase.java:311)
  14.     at com.alibaba.cxdc.connected.dao.impl.SessionUserDAOImpl.delete(SessionUserDAOImpl.java:96)
  15.     at com.alibaba.cxdc.connected.dao.impl.SessionUserDAOImpl$$FastClassBySpringCGLIB$$24a8bda3.invoke(<generated>)
  16.     at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
  17.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:736)
  18.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
  19.     at com.taobao.csp.sentinel.entrypoint.proxy.TraceMethodInterceptor.invoke(TraceMethodInterceptor.java:81)
  20.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
  21.     at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:671)
  22.     at com.alibaba.cxdc.connected.dao.impl.SessionUserDAOImpl$$EnhancerBySpringCGLIB$$5a6023ad.delete(<generated>)
  23.     at sun.reflect.GeneratedMethodAccessor1029.invoke(Unknown Source)
  24.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  25.     at java.lang.reflect.Method.invoke(Method.java:497)
  26.     at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
  27.     at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
  28.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
  29.     at com.alibaba.cxdc.connected.util.TenantDetectionAop.invoke(TenantDetectionAop.java:52)
  30.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
  31.     at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
  32.     at com.sun.proxy.$Proxy178.delete(Unknown Source)
  33.     at com.alibaba.cxdc.connected.manager.ConnectedSessionUserManager.delete(ConnectedSessionUserManager.java:161)
  34.     at com.alibaba.cxdc.connected.manager.SessionUserManager.quitSession(SessionUserManager.java:84)
  35.     at com.alibaba.cxdc.connected.service.impl.SessionServiceImpl.quitSession(SessionServiceImpl.java:151)
  36.     at com.alibaba.cxdc.connected.service.impl.XSpaceSessionUserServiceImpl.updateXSpaceSesionUser(XSpaceSessionUserServiceImpl.java:93)
  37.     at com.alibaba.cxdc.connected.service.impl.XSpaceSessionServiceImpl.resumeXSpaceSessionUsersAndHeart(XSpaceSessionServiceImpl.java:1074)
  38.     at sun.reflect.GeneratedMethodAccessor1160.invoke(Unknown Source)
  39.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  40.     at java.lang.reflect.Method.invoke(Method.java:497)
  41.     at com.taobao.csp.sentinel.entrypoint.entrance.HsfProviderInvocationHandler.invoke(HsfProviderInvocationHandler.java:96)
  42.     at com.sun.proxy.$Proxy206.resumeXSpaceSessionUsersAndHeart(Unknown Source)
  43.     at sun.reflect.GeneratedMethodAccessor1160.invoke(Unknown Source)
  44.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  45.     at java.lang.reflect.Method.invoke(Method.java:497)
  46.     at com.taobao.hsf.remoting.provider.ReflectInvocationHandler.handleRequest0(ReflectInvocationHandler.java:83)
  47.     at com.taobao.hsf.remoting.provider.ReflectInvocationHandler.invoke(ReflectInvocationHandler.java:163)
  48.     at com.taobao.hsf2dubbo.DubboServerFilterAsyncInvocationHandlerInterceptor.invoke(DubboServerFilterAsyncInvocationHandlerInterceptor.java:54)
  49.     at com.taobao.hsf.filter.FilterInvocationHandler.invoke(FilterInvocationHandler.java:38)
  50.     at com.taobao.hsf.invocation.filter.RPCFilterBuilder$TailNode.invoke(RPCFilterBuilder.java:165)
  51.     at com.taobao.hsf.debug.DebugServerFilter.invoke(DebugServerFilter.java:25)
  52.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  53.     at com.taobao.hsf.common.filter.BidirectionServerResponseFilter.invoke(BidirectionServerResponseFilter.java:15)
  54.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  55.     at com.taobao.hsf.unit.service.impl.UnitServerFilter.invoke(UnitServerFilter.java:66)
  56.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  57.     at com.taobao.hsf.region.service.impl.RegionServerFilter.invoke(RegionServerFilter.java:42)
  58.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  59.     at com.taobao.hsf.plugins.octopus.OctopusServerFilter.invoke(OctopusServerFilter.java:72)
  60.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  61.     at com.taobao.hsf.plugins.spas.SpasServerFilter.invoke(SpasServerFilter.java:76)
  62.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  63.     at com.taobao.hsf.plugins.txc.TXCServerFilter.invoke(TXCServerFilter.java:35)
  64.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  65.     at com.taobao.hsf.tps.component.TPSServerFilter.invoke(TPSServerFilter.java:68)
  66.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  67.     at com.taobao.hsf.invocation.stats.InvocationStatsServerFilter.invoke(InvocationStatsServerFilter.java:47)
  68.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  69.     at com.taobao.hsf.monitor.log.filter.MonitorLogServerFilter.invoke(MonitorLogServerFilter.java:45)
  70.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  71.     at com.taobao.hsf.profiler.ProfilerServerFilter.invoke(ProfilerServerFilter.java:32)
  72.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  73.     at com.taobao.hsf.plugins.eagleeye.EagleEyeServerFilter.invoke(EagleEyeServerFilter.java:68)
  74.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  75.     at com.taobao.hsf.filter.QosServerFilter.invoke(QosServerFilter.java:60)
  76.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  77.     at com.taobao.hsf.rpc.server.MethodAbsenceFilter.invoke(MethodAbsenceFilter.java:48)
  78.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  79.     at com.taobao.hsf.rpc.server.EchoFilter.invoke(EchoFilter.java:45)
  80.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  81.     at com.taobao.hsf.rpc.generic.GenericInvocationServerFilter.invoke(GenericInvocationServerFilter.java:107)
  82.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  83.     at com.taobao.hsf2dubbo.DubboGenericServerFilter.invoke(DubboGenericServerFilter.java:37)
  84.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  85.     at com.taobao.hsf.top.TopServerFilter.invoke(TopServerFilter.java:87)
  86.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  87.     at com.taobao.hsf.rpc.server.ServiceAbsenceFilter.invoke(ServiceAbsenceFilter.java:47)
  88.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  89.     at com.taobao.hsf.common.filter.CommonServerFilter.invoke(CommonServerFilter.java:24)
  90.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  91.     at com.taobao.hsf.common.filter.BidirectionServerRequestFilter.invoke(BidirectionServerRequestFilter.java:19)
  92.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  93.     at com.taobao.hsf2dubbo.context.DubboRPCContextServerFilter.invoke(DubboRPCContextServerFilter.java:48)
  94.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  95.     at com.taobao.hsf.context.RPCContextServerFilter.invoke(RPCContextServerFilter.java:39)
  96.     at com.taobao.hsf.invocation.filter.RPCFilterNode.invoke(RPCFilterNode.java:71)
  97.     at com.taobao.hsf.invocation.filter.RPCFilterBuilder$HeadNode.invoke(RPCFilterBuilder.java:134)
  98.     at com.taobao.hsf.invocation.filter.FilterInvocationHandler.invoke(FilterInvocationHandler.java:28)
  99.     at com.taobao.hsf.remoting.provider.ServerContextInvocationHandler.invoke(ServerContextInvocationHandler.java:35)
  100.     at com.taobao.hsf.remoting.provider.ProviderProcessor.handleRequest(ProviderProcessor.java:70)
  101.     at com.taobao.hsf.io.remoting.hsf.message.HSFServerHandler$1.run(HSFServerHandler.java:189)
  102.     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
  103.     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
  104.     at java.lang.Thread.run(Thread.java:756)
  105. Caused by: org.apache.ibatis.exceptions.PersistenceException:
  106. ### Error updating database.  Cause: ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL] Error occurs when execute on GROUP 'CCO_XSPACE_SESSION_0003_GROUP' ATOM 'db198205096_cloud_eu126_cco_xspace_session_0003_3417': Deadlock found when trying to get lock; try restarting transaction More: [http://middleware.alibaba-inc.com/faq/faqByFaqCode.html?faqCode=TDDL-4614]
  107. ### The error may exist in class path resource [mappers/session_users_sqlmap_mapping.xml]
  108. ### The error may involve com.alibaba.cxdc.connected.dao.OcsSessionUserMapper.delete-Inline
  109. ### The error occurred while setting parameters
  110. ### SQL: delete from           connected_session_users         where            sid = ?                                                                    and user_id = ?
  111. ### Cause: ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL] Error occurs when execute on GROUP 'CCO_XSPACE_SESSION_0003_GROUP' ATOM 'db198205096_cloud_eu126_cco_xspace_session_0003_3417': Deadlock found when trying to get lock; try restarting transaction More: [http://middleware.alibaba-inc.com/faq/faqByFaqCode.html?faqCode=TDDL-4614]
  112.     at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
  113.     at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199)
  114.     at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:212)
  115.     at sun.reflect.GeneratedMethodAccessor985.invoke(Unknown Source)
  116.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  117.     at java.lang.reflect.Method.invoke(Method.java:497)
  118.     at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
  119.     ... 94 common frames omitted
  120. Caused by: com.taobao.tddl.common.exception.TddlRuntimeException: ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL] Error occurs when execute on GROUP 'CCO_XSPACE_SESSION_0003_GROUP' ATOM 'db198205096_cloud_eu126_cco_xspace_session_0003_3417': Deadlock found when trying to get lock; try restarting transaction More: [http://middleware.alibaba-inc.com/faq/faqByFaqCode.html?faqCode=TDDL-4614]
  121.     at com.taobao.tddl.repo.mysql.spi.My_JdbcHandler.handleException(My_JdbcHandler.java:1426)
  122.     at com.taobao.tddl.repo.mysql.spi.My_JdbcHandler.executeUpdate(My_JdbcHandler.java:1088)
  123.     at com.taobao.tddl.repo.mysql.spi.My_Cursor.batchDelete(My_Cursor.java:435)
  124.     at com.taobao.tddl.repo.mysql.handler.DeleteMyHandler.executePut(DeleteMyHandler.java:26)
  125.     at com.taobao.tddl.repo.mysql.handler.PutMyHandlerCommon.handle(PutMyHandlerCommon.java:27)
  126.     at com.taobao.tddl.executor.AbstractGroupExecutor.executeInner(AbstractGroupExecutor.java:47)
  127.     at com.taobao.tddl.executor.AbstractGroupExecutor.execByExecPlanNode(AbstractGroupExecutor.java:36)
  128.     at com.taobao.tddl.executor.TopologyExecutor.execByExecPlanNode(TopologyExecutor.java:74)
  129.     at com.taobao.tddl.executor.MatrixExecutor.execByExecPlanNodeByOne(MatrixExecutor.java:800)
  130.     at com.taobao.tddl.executor.MatrixExecutor.execByExecPlanNode(MatrixExecutor.java:789)
  131.     at com.taobao.tddl.executor.MatrixExecutor.execute(MatrixExecutor.java:145)
  132.     at com.taobao.tddl.matrix.jdbc.TConnection.executeSQL(TConnection.java:275)
  133.     at com.taobao.tddl.matrix.jdbc.TPreparedStatement.executeSQL(TPreparedStatement.java:64)
  134.     at com.taobao.tddl.matrix.jdbc.TStatement.executeInternal(TStatement.java:142)
  135.     at com.taobao.tddl.matrix.jdbc.TPreparedStatement.execute(TPreparedStatement.java:49)
  136.     at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
  137.     at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
  138.     at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:52)
  139.     at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
  140.     at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
  141.     ... 99 common frames omitted
  142. Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
  143.     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  144.     at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
  145.     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  146.     at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
  147.     at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
  148.     at com.mysql.jdbc.Util.getInstance(Util.java:408)
  149.     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:951)
  150.     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
  151.     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
  152.     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
  153.     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
  154.     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
  155.     at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
  156.     at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
  157.     at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009)
  158.     at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5098)
  159.     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994)
  160.     at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeUpdate(FilterChainImpl.java:2723)
  161.     at com.alibaba.druid.filter.FilterAdapter.preparedStatement_executeUpdate(FilterAdapter.java:1069)
  162.     at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeUpdate(FilterEventAdapter.java:491)
  163.     at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeUpdate(FilterChainImpl.java:2721)
  164.     at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeUpdate(PreparedStatementProxyImpl.java:158)
  165.     at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeUpdate(DruidPooledPreparedStatement.java:253)
  166.     at com.taobao.tddl.atom.jdbc.TPreparedStatementWrapper.executeUpdate(TPreparedStatementWrapper.java:137)
  167.     at com.taobao.tddl.group.jdbc.TGroupPreparedStatement.executeUpdateOnConnection(TGroupPreparedStatement.java:132)
  168.     at com.taobao.tddl.group.jdbc.TGroupStatement$1.tryOnDataSource(TGroupStatement.java:255)
  169.     at com.taobao.tddl.group.jdbc.TGroupStatement$1.tryOnDataSource(TGroupStatement.java:238)
  170.     at com.taobao.tddl.group.dbselector.EquityDbManager.tryExecuteInternal(EquityDbManager.java:280)
  171.     at com.taobao.tddl.group.dbselector.AbstractDBSelector.tryExecute(AbstractDBSelector.java:442)
  172.     at com.taobao.tddl.group.dbselector.AbstractDBSelector.tryExecute(AbstractDBSelector.java:449)
  173.     at com.taobao.tddl.group.jdbc.TGroupStatement.executeUpdateInternal(TGroupStatement.java:209)
  174.     at com.taobao.tddl.group.jdbc.TGroupStatement.executeUpdate(TGroupStatement.java:171)
  175.     at com.taobao.tddl.group.jdbc.TGroupPreparedStatement.executeUpdate(TGroupPreparedStatement.java:102)
  176.     at com.taobao.tddl.repo.mysql.spi.My_JdbcHandler.executeUpdate(My_JdbcHandler.java:1011)
  177.     ... 117 common frames omitted

可以看到是如下 sql导致了死锁。

  1.  delete from connected_session_users where sid = ?

关键调用链如下：

  1.     at com.sun.proxy.$Proxy178.delete(Unknown Source)
  2.     at com.alibaba.cxdc.connected.manager.ConnectedSessionUserManager.delete(ConnectedSessionUserManager.java:161)
  3.     at com.alibaba.cxdc.connected.manager.SessionUserManager.quitSession(SessionUserManager.java:84)
  4.     at com.alibaba.cxdc.connected.service.impl.SessionServiceImpl.quitSession(SessionServiceImpl.java:151)
  5.     at com.alibaba.cxdc.connected.service.impl.XSpaceSessionUserServiceImpl.updateXSpaceSesionUser(XSpaceSessionUserServiceImpl.java:93)
  6.     at com.alibaba.cxdc.connected.service.impl.XSpaceSessionServiceImpl.resumeXSpaceSessionUsersAndHeart(XSpaceSessionServiceImpl.java:1074)

查看业务代码发现并没有使用事务，所以是 一条 sql 和 另一条 sql ，排查难度较大，所以需要进一步获取 mysql 死锁日志进行排查。

  1. 获取 mysql死锁日志，使用
  1. show engine innodb status;

可以查看死锁日志。但是需要在主库上执行该语句，而从 idb 上查询是从备库查，经过与 dba，idb 同学，cloudDBA 同学沟通得到如下信息：

  • 只有 owner 有权限在 idb 上直接查询主库
  • cloudDBA 上的 死锁诊断 功能是无效的，且近期没有支持的打算。

最后在 idb 同学的帮助下获取了死锁日志，如下：

  2. =====================================
  3. 2019-11-07 10:15:20 0x7fccf10ef700 INNODB MONITOR OUTPUT
  4. =====================================
  5. Per second averages calculated from the last 34 seconds
  6. -----------------
  7. BACKGROUND THREAD
  8. -----------------
  9. srv_master_thread loops: 31988258 srv_active, 0 srv_shutdown, 5 srv_idle
  10. srv_master_thread log flush and writes: 31987638
  11. ----------
  12. SEMAPHORES
  13. ----------
  14. OS WAIT ARRAY INFO: reservation count 23203222
  15. OS WAIT ARRAY INFO: reservation count 0
  16. OS WAIT ARRAY INFO: reservation count 21341321
  17. OS WAIT ARRAY INFO: reservation count 0
  18. OS WAIT ARRAY INFO: reservation count 23210338
  19. OS WAIT ARRAY INFO: reservation count 0
  20. OS WAIT ARRAY INFO: reservation count 21337029
  21. OS WAIT ARRAY INFO: reservation count 0
  22. OS WAIT ARRAY INFO: reservation count 23188177
  23. OS WAIT ARRAY INFO: reservation count 0
  24. OS WAIT ARRAY INFO: reservation count 21338727
  25. OS WAIT ARRAY INFO: reservation count 0
  26. OS WAIT ARRAY INFO: reservation count 23198818
  27. OS WAIT ARRAY INFO: reservation count 0
  28. OS WAIT ARRAY INFO: reservation count 21337993
  29. OS WAIT ARRAY INFO: reservation count 0
  30. OS WAIT ARRAY INFO: signal count 231446746
  31. RW-shared spins 0, rounds 542191924, OS waits 142503633
  32. RW-excl spins 0, rounds 1167442614, OS waits 15206489
  33. RW-sx spins 33729319, rounds 379378706, OS waits 6842293
  34. Spin rounds per wait: 542191924.00 RW-shared, 1167442614.00 RW-excl, 11.25 RW-sx
  35. ------------------------
  36. LATEST DETECTED DEADLOCK
  37. ------------------------
  38. 2019-11-05 15:02:44 0x7fcd2caef700
  39. *** (1) TRANSACTION:
  40. TRANSACTION 10476863046, ACTIVE 0 sec starting index read
  41. mysql tables in use 1, locked 1
  42. LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
  43. MySQL thread id 3322429, OS thread handle 140493696530176, query id 5089036262 11.18.122.237 cco_xspace_sessi updating
  44. /* 0b51153515729373648846445e1bf6/0.1.1.4.3//e21a688f// */delete from `connected_session_users_0102`  where ((`sid` = '65c48fc3bcff47e1ac7a7ce787b2acde') AND (`user_id` = 417880447))
  45. *** (1) HOLDS THE LOCK(S):
  46. RECORD LOCKS space id 132 page no 15896 n bits 416 index idx_sid of table `cco_xspace_session_0003`.`connected_session_users_0102` trx id 10476863046 lock_mode X locks rec but not gap
  47. Record lock, heap no 262 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
  48.  0: len 30; hex 363563343866633362636666343765316163376137636537383762326163; asc 65c48fc3bcff47e1ac7a7ce787b2ac; (total 32 bytes);
  49.  1: len 8; hex 0000000139ee46da; asc     9 F ;;
  51. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  52. RECORD LOCKS space id 132 page no 16560 n bits 184 index PRIMARY of table `cco_xspace_session_0003`.`connected_session_users_0102` trx id 10476863046 lock_mode X locks rec but not gap waiting
  53. Record lock, heap no 117 PHYSICAL RECORD: n_fields 13; compact format; info bits 32
  54.  0: len 8; hex 0000000139ee46da; asc     9 F ;;
  55.  1: len 6; hex 000270783e47; asc   px>G;;
  56.  2: len 7; hex 37002180312260; asc 7 ! 1"`;;
  57.  3: len 5; hex 99a48af04c; asc     L;;
  58.  4: len 5; hex 99a48af04c; asc     L;;
  59.  5: len 30; hex 363563343866633362636666343765316163376137636537383762326163; asc 65c48fc3bcff47e1ac7a7ce787b2ac; (total 32 bytes);
  60.  6: len 8; hex 8000000018e8597f; asc       Y ;;
  61.  7: len 18; hex e6b3a1e6b2abe4b98be5a48fe5a48fe69cab; asc                   ;;
  62.  8: len 1; hex 81; asc  ;;
  63.  9: len 30; hex 303031326639303133636139336530653030303030303030313665363539; asc 0012f9013ca93e0e0000000016e659; (total 32 bytes);
  64.  10: len 8; hex 0000000000000003; asc         ;;
  65.  11: SQL NULL;
  66.  12: SQL NULL;
  68. *** (2) TRANSACTION:
  69. TRANSACTION 10476863047, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4999
  70. mysql tables in use 1, locked 1
  71. 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
  72. MySQL thread id 4294902021, OS thread handle 140519194687232, query id 5089036263 11.178.251.210 drcwriter_shunit updating
  73. DELETE FROM `cco_xspace_session_0003`.`connected_session_users_0102` WHERE `id`=5266884314
  74. *** (2) HOLDS THE LOCK(S):
  75. RECORD LOCKS space id 132 page no 16560 n bits 184 index PRIMARY of table `cco_xspace_session_0003`.`connected_session_users_0102` trx id 10476863047 lock_mode X locks rec but not gap
  76. Record lock, heap no 117 PHYSICAL RECORD: n_fields 13; compact format; info bits 32
  77.  0: len 8; hex 0000000139ee46da; asc     9 F ;;
  78.  1: len 6; hex 000270783e47; asc   px>G;;
  79.  2: len 7; hex 37002180312260; asc 7 ! 1"`;;
  80.  3: len 5; hex 99a48af04c; asc     L;;
  81.  4: len 5; hex 99a48af04c; asc     L;;
  82.  5: len 30; hex 363563343866633362636666343765316163376137636537383762326163; asc 65c48fc3bcff47e1ac7a7ce787b2ac; (total 32 bytes);
  83.  6: len 8; hex 8000000018e8597f; asc       Y ;;
  84.  7: len 18; hex e6b3a1e6b2abe4b98be5a48fe5a48fe69cab; asc                   ;;
  85.  8: len 1; hex 81; asc  ;;
  86.  9: len 30; hex 303031326639303133636139336530653030303030303030313665363539; asc 0012f9013ca93e0e0000000016e659; (total 32 bytes);
  87.  10: len 8; hex 0000000000000003; asc         ;;
  88.  11: SQL NULL;
  89.  12: SQL NULL;
  91. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  92. RECORD LOCKS space id 132 page no 15896 n bits 416 index idx_sid of table `cco_xspace_session_0003`.`connected_session_users_0102` trx id 10476863047 lock_mode X locks rec but not gap waiting
  93. Record lock, heap no 262 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
  94.  0: len 30; hex 363563343866633362636666343765316163376137636537383762326163; asc 65c48fc3bcff47e1ac7a7ce787b2ac; (total 32 bytes);
  95.  1: len 8; hex 0000000139ee46da; asc     9 F ;;
  97. *** WE ROLL BACK TRANSACTION (1)
  98. ------------
  99. TRANSACTIONS
  100. ------------
  101. Trx id counter 10717679978
  102. Purge done for trx's n:o < 10717679978 undo n:o < 0 state: running but idle
  103. History list length 1757
  104. LIST OF TRANSACTIONS FOR EACH SESSION:
  105. ---TRANSACTION 421994453671584, not started
  106. 0 lock struct(s), heap size 1136, 0 row lock(s)
  107. ---TRANSACTION 421994453669600, not started
  108. 0 lock struct(s), heap size 1136, 0 row lock(s)
  109. ---TRANSACTION 421994453665632, not started
  110. 0 lock struct(s), heap size 1136, 0 row lock(s)
  111. ---TRANSACTION 421994453664640, not started
  112. 0 lock struct(s), heap size 1136, 0 row lock(s)
  113. ---TRANSACTION 421994453660672, not started
  114. 0 lock struct(s), heap size 1136, 0 row lock(s)
  115. ---TRANSACTION 421994453656704, not started
  116. 0 lock struct(s), heap size 1136, 0 row lock(s)
  117. ---TRANSACTION 421994453655712, not started
  118. 0 lock struct(s), heap size 1136, 0 row lock(s)
  119. ---TRANSACTION 421994453589248, not started
  120. 0 lock struct(s), heap size 1136, 0 row lock(s)
  121. ---TRANSACTION 421994453587264, not started
  122. 0 lock struct(s), heap size 1136, 0 row lock(s)
  123. ---TRANSACTION 421994453584288, not started
  124. 0 lock struct(s), heap size 1136, 0 row lock(s)
  125. ---TRANSACTION 421994453583296, not started
  126. 0 lock struct(s), heap size 1136, 0 row lock(s)
  127. ---TRANSACTION 421994453580320, not started
  128. 0 lock struct(s), heap size 1136, 0 row lock(s)
  129. ---TRANSACTION 421994453579328, not started
  130. 0 lock struct(s), heap size 1136, 0 row lock(s)
  131. ---TRANSACTION 421994453578336, not started
  132. 0 lock struct(s), heap size 1136, 0 row lock(s)
  133. ---TRANSACTION 421994453572384, not started
  134. 0 lock struct(s), heap size 1136, 0 row lock(s)
  135. ---TRANSACTION 421994453571392, not started
  136. 0 lock struct(s), heap size 1136, 0 row lock(s)
  137. ---TRANSACTION 421994453564448, not started
  138. 0 lock struct(s), heap size 1136, 0 row lock(s)
  139. ---TRANSACTION 421994453563456, not started
  140. 0 lock struct(s), heap size 1136, 0 row lock(s)
  141. ---TRANSACTION 421994453558496, not started
  142. 0 lock struct(s), heap size 1136, 0 row lock(s)
  143. ---TRANSACTION 421994453557504, not started
  144. 0 lock struct(s), heap size 1136, 0 row lock(s)
  145. ---TRANSACTION 421994453552544, not started
  146. 0 lock struct(s), heap size 1136, 0 row lock(s)
  147. ---TRANSACTION 421994453551552, not started
  148. 0 lock struct(s), heap size 1136, 0 row lock(s)
  149. ---TRANSACTION 421994453550560, not started
  150. 0 lock struct(s), heap size 1136, 0 row lock(s)
  151. ---TRANSACTION 421994453545600, not started
  152. 0 lock struct(s), heap size 1136, 0 row lock(s)
  153. ---TRANSACTION 421994453539648, not started
  154. 0 lock struct(s), heap size 1136, 0 row lock(s)
  155. ---TRANSACTION 421994453538656, not started
  156. 0 lock struct(s), heap size 1136, 0 row lock(s)
  157. ---TRANSACTION 421994453537664, not started
  158. 0 lock struct(s), heap size 1136, 0 row lock(s)
  159. ---TRANSACTION 421994453582304, not started
  160. 0 lock struct(s), heap size 1136, 0 row lock(s)
  161. ---TRANSACTION 421994453663648, not started
  162. 0 lock struct(s), heap size 1136, 0 row lock(s)
  163. ---TRANSACTION 421994453704320, not started
  164. 0 lock struct(s), heap size 1136, 0 row lock(s)
  165. ---TRANSACTION 421994453798560, not started
  166. 0 lock struct(s), heap size 1136, 0 row lock(s)
  167. ---TRANSACTION 421994453585280, not started
  168. 0 lock struct(s), heap size 1136, 0 row lock(s)
  169. ---TRANSACTION 421994453792608, not started
  170. 0 lock struct(s), heap size 1136, 0 row lock(s)
  171. ---TRANSACTION 421994453784672, not started
  172. 0 lock struct(s), heap size 1136, 0 row lock(s)
  173. ---TRANSACTION 421994453783680, not started
  174. 0 lock struct(s), heap size 1136, 0 row lock(s)
  175. ---TRANSACTION 421994453780704, not started
  176. 0 lock struct(s), heap size 1136, 0 row lock(s)
  177. ---TRANSACTION 421994453777728, not started
  178. 0 lock struct(s), heap size 1136, 0 row lock(s)
  179. ---TRANSACTION 421994453765824, not started
  180. 0 lock struct(s), heap size 1136, 0 row lock(s)
  181. ---TRANSACTION 421994453757888, not started
  182. 0 lock struct(s), heap size 1136, 0 row lock(s)
  183. ---TRANSACTION 421994453753920, not started
  184. 0 lock struct(s), heap size 1136, 0 row lock(s)
  185. ---TRANSACTION 421994453751936, not started
  186. 0 lock struct(s), heap size 1136, 0 row lock(s)
  187. ---TRANSACTION 421994453746976, not started
  188. 0 lock struct(s), heap size 1136, 0 row lock(s)
  189. ---TRANSACTION 421994453731104, not started
  190. 0 lock struct(s), heap size 1136, 0 row lock(s)
  191. ---TRANSACTION 421994453725152, not started
  192. 0 lock struct(s), heap size 1136, 0 row lock(s)
  193. ---TRANSACTION 421994453720192, not started
  194. 0 lock struct(s), heap size 1136, 0 row lock(s)
  195. ---TRANSACTION 421994453719200, not started
  196. 0 lock struct(s), heap size 1136, 0 row lock(s)
  197. ---TRANSACTION 421994453718208, not started
  198. 0 lock struct(s), heap size 1136, 0 row lock(s)
  199. ---TRANSACTION 421994453717216, not started
  200. 0 lock struct(s), heap size 1136, 0 row lock(s)
  201. ---TRANSACTION 421994453713248, not started
  202. 0 lock struct(s), heap size 1136, 0 row lock(s)
  203. ---TRANSACTION 421994453710272, not started
  204. 0 lock struct(s), heap size 1136, 0 row lock(s)
  205. ---TRANSACTION 421994453707296, not started
  206. 0 lock struct(s), heap size 1136, 0 row lock(s)
  207. ---TRANSACTION 421994453706304, not started
  208. 0 lock struct(s), heap size 1136, 0 row lock(s)
  209. ---TRANSACTION 421994453694400, not started
  210. 0 lock struct(s), heap size 1136, 0 row lock(s)
  211. ---TRANSACTION 421994453693408, not started
  212. 0 lock struct(s), heap size 1136, 0 row lock(s)
  213. ---TRANSACTION 421994453689440, not started
  214. 0 lock struct(s), heap size 1136, 0 row lock(s)
  215. ---TRANSACTION 421994453684480, not started
  216. 0 lock struct(s), heap size 1136, 0 row lock(s)
  217. ---TRANSACTION 421994453680512, not started
  218. 0 lock struct(s), heap size 1136, 0 row lock(s)
  219. ---TRANSACTION 421994453679520, not started
  220. 0 lock struct(s), heap size 1136, 0 row lock(s)
  221. ---TRANSACTION 421994453678528, not started
  222. 0 lock struct(s), heap size 1136, 0 row lock(s)
  223. ---TRANSACTION 421994453674560, not started
  224. 0 lock struct(s), heap size 1136, 0 row lock(s)
  225. ---TRANSACTION 421994453667616, not started
  226. 0 lock struct(s), heap size 1136, 0 row lock(s)
  227. ---TRANSACTION 421994453666624, not started
  228. 0 lock struct(s), heap size 1136, 0 row lock(s)
  229. ---TRANSACTION 421994453662656, not started
  230. 0 lock struct(s), heap size 1136, 0 row lock(s)
  231. ---TRANSACTION 421994453661664, not started
  232. 0 lock struct(s), heap size 1136, 0 row lock(s)
  233. ---TRANSACTION 421994453592224, not started
  234. 0 lock struct(s), heap size 1136, 0 row lock(s)
  235. ---TRANSACTION 421994453591232, not started
  236. 0 lock struct(s), heap size 1136, 0 row lock(s)
  237. ---TRANSACTION 421994453590240, not started
  238. 0 lock struct(s), heap size 1136, 0 row lock(s)
  239. ---TRANSACTION 421994453575360, not started
  240. 0 lock struct(s), heap size 1136, 0 row lock(s)
  241. ---TRANSACTION 421994453573376, not started
  242. 0 lock struct(s), heap size 1136, 0 row lock(s)
  243. ---TRANSACTION 421994453569408, not started
  244. 0 lock struct(s), heap size 1136, 0 row lock(s)
  245. ---TRANSACTION 421994453566432, not started
  246. 0 lock struct(s), heap size 1136, 0 row lock(s)
  247. ---TRANSACTION 421994453560480, not started
  248. 0 lock struct(s), heap size 1136, 0 row lock(s)
  249. ---TRANSACTION 421994453556512, not started
  250. 0 lock struct(s), heap size 1136, 0 row lock(s)
  251. ---TRANSACTION 421994453555520, not started
  252. 0 lock struct(s), heap size 1136, 0 row lock(s)
  253. ---TRANSACTION 421994453547584, not started
  254. 0 lock struct(s), heap size 1136, 0 row lock(s)
  255. ---TRANSACTION 421994453544608, not started
  256. 0 lock struct(s), heap size 1136, 0 row lock(s)
  257. ---TRANSACTION 421994453540640, not started
  258. 0 lock struct(s), heap size 1136, 0 row lock(s)
  259. ---TRANSACTION 421994453536672, not started
  260. 0 lock struct(s), heap size 1136, 0 row lock(s)
  261. ---TRANSACTION 421994453673568, not started
  262. 0 lock struct(s), heap size 1136, 0 row lock(s)
  263. ---TRANSACTION 421994453670592, not started
  264. 0 lock struct(s), heap size 1136, 0 row lock(s)
  265. ---TRANSACTION 421994453781696, not started
  266. 0 lock struct(s), heap size 1136, 0 row lock(s)
  267. ---TRANSACTION 421994453775744, not started
  268. 0 lock struct(s), heap size 1136, 0 row lock(s)
  269. ---TRANSACTION 421994453771776, not started
  270. 0 lock struct(s), heap size 1136, 0 row lock(s)
  271. ---TRANSACTION 421994453769792, not started
  272. 0 lock struct(s), heap size 1136, 0 row lock(s)
  273. ---TRANSACTION 421994453761856, not started
  274. 0 lock struct(s), heap size 1136, 0 row lock(s)
  275. ---TRANSACTION 421994453758880, not started
  276. 0 lock struct(s), heap size 1136, 0 row lock(s)
  277. ---TRANSACTION 421994453730112, not started
  278. 0 lock struct(s), heap size 1136, 0 row lock(s)
  279. ---TRANSACTION 421994453722176, not started
  280. 0 lock struct(s), heap size 1136, 0 row lock(s)
  281. ---TRANSACTION 421994453714240, not started
  282. 0 lock struct(s), heap size 1136, 0 row lock(s)
  283. ---TRANSACTION 421994453697376, not started
  284. 0 lock struct(s), heap size 1136, 0 row lock(s)
  285. ---TRANSACTION 421994453695392, not started
  286. 0 lock struct(s), heap size 1136, 0 row lock(s)
  287. ---TRANSACTION 421994453686464, not started
  288. 0 lock struct(s), heap size 1136, 0 row lock(s)
  289. ---TRANSACTION 421994453682496, not started
  290. 0 lock struct(s), heap size 1136, 0 row lock(s)
  291. ---TRANSACTION 421994453677536, not started
  292. 0 lock struct(s), heap size 1136, 0 row lock(s)
  293. ---TRANSACTION 421994453676544, not started
  294. 0 lock struct(s), heap size 1136, 0 row lock(s)
  295. ---TRANSACTION 421994453594208, not started
  296. 0 lock struct(s), heap size 1136, 0 row lock(s)
  297. ---TRANSACTION 421994453593216, not started
  298. 0 lock struct(s), heap size 1136, 0 row lock(s)
  299. ---TRANSACTION 421994453588256, not started
  300. 0 lock struct(s), heap size 1136, 0 row lock(s)
  301. ---TRANSACTION 421994453577344, not started
  302. 0 lock struct(s), heap size 1136, 0 row lock(s)
  303. ---TRANSACTION 421994453574368, not started
  304. 0 lock struct(s), heap size 1136, 0 row lock(s)
  305. ---TRANSACTION 421994453565440, not started
  306. 0 lock struct(s), heap size 1136, 0 row lock(s)
  307. ---TRANSACTION 421994453561472, not started
  308. 0 lock struct(s), heap size 1136, 0 row lock(s)
  309. ---TRANSACTION 421994453554528, not started
  310. 0 lock struct(s), heap size 1136, 0 row lock(s)
  311. ---TRANSACTION 421994453543616, not started
  312. 0 lock struct(s), heap size 1136, 0 row lock(s)
  313. ---TRANSACTION 421994453534688, not started
  314. 0 lock struct(s), heap size 1136, 0 row lock(s)
  315. ---TRANSACTION 421994453721184, not started
  316. 0 lock struct(s), heap size 1136, 0 row lock(s)
  317. ---TRANSACTION 421994453794592, not started
  318. 0 lock struct(s), heap size 1136, 0 row lock(s)
  319. ---TRANSACTION 421994453790624, not started
  320. 0 lock struct(s), heap size 1136, 0 row lock(s)
  321. ---TRANSACTION 421994453787648, not started
  322. 0 lock struct(s), heap size 1136, 0 row lock(s)
  323. ---TRANSACTION 421994453776736, not started
  324. 0 lock struct(s), heap size 1136, 0 row lock(s)
  325. ---TRANSACTION 421994453770784, not started
  326. 0 lock struct(s), heap size 1136, 0 row lock(s)
  327. ---TRANSACTION 421994453764832, not started
  328. 0 lock struct(s), heap size 1136, 0 row lock(s)
  329. ---TRANSACTION 421994453750944, not started
  330. 0 lock struct(s), heap size 1136, 0 row lock(s)
  331. ---TRANSACTION 421994453748960, not started
  332. 0 lock struct(s), heap size 1136, 0 row lock(s)
  333. ---TRANSACTION 421994453747968, not started
  334. 0 lock struct(s), heap size 1136, 0 row lock(s)
  335. ---TRANSACTION 421994453743008, not started
  336. 0 lock struct(s), heap size 1136, 0 row lock(s)
  337. ---TRANSACTION 421994453715232, not started
  338. 0 lock struct(s), heap size 1136, 0 row lock(s)
  339. ---TRANSACTION 421994453702336, not started
  340. 0 lock struct(s), heap size 1136, 0 row lock(s)
  341. ---TRANSACTION 421994453700352, not started
  342. 0 lock struct(s), heap size 1136, 0 row lock(s)
  343. ---TRANSACTION 421994453672576, not started
  344. 0 lock struct(s), heap size 1136, 0 row lock(s)
  345. ---TRANSACTION 421994453654720, not started
  346. 0 lock struct(s), heap size 1136, 0 row lock(s)
  347. ---TRANSACTION 421994453581312, not started
  348. 0 lock struct(s), heap size 1136, 0 row lock(s)
  349. ---TRANSACTION 421994453535680, not started
  350. 0 lock struct(s), heap size 1136, 0 row lock(s)
  351. ---TRANSACTION 421994453548576, not started
  352. 0 lock struct(s), heap size 1136, 0 row lock(s)
  353. ---TRANSACTION 421994453675552, not started
  354. 0 lock struct(s), heap size 1136, 0 row lock(s)
  355. ---TRANSACTION 421994453546592, not started
  356. 0 lock struct(s), heap size 1136, 0 row lock(s)
  357. ---TRANSACTION 421994453755904, not started
  358. 0 lock struct(s), heap size 1136, 0 row lock(s)
  359. ---TRANSACTION 421994453739040, not started
  360. 0 lock struct(s), heap size 1136, 0 row lock(s)
  361. ---TRANSACTION 421994453692416, not started
  362. 0 lock struct(s), heap size 1136, 0 row lock(s)
  363. ---TRANSACTION 421994453586272, not started
  364. 0 lock struct(s), heap size 1136, 0 row lock(s)
  365. ---TRANSACTION 421994453549568, not started
  366. 0 lock struct(s), heap size 1136, 0 row lock(s)
  367. ---TRANSACTION 421994453779712, not started
  368. 0 lock struct(s), heap size 1136, 0 row lock(s)
  369. ---TRANSACTION 421994453773760, not started
  370. 0 lock struct(s), heap size 1136, 0 row lock(s)
  371. ---TRANSACTION 421994453709280, not started
  372. 0 lock struct(s), heap size 1136, 0 row lock(s)
  373. ---TRANSACTION 421994453701344, not started
  374. 0 lock struct(s), heap size 1136, 0 row lock(s)
  375. ---TRANSACTION 421994453657696, not started
  376. 0 lock struct(s), heap size 1136, 0 row lock(s)
  377. ---TRANSACTION 421994453782688, not started
  378. 0 lock struct(s), heap size 1136, 0 row lock(s)
  379. ---TRANSACTION 421994453658688, not started
  380. 0 lock struct(s), heap size 1136, 0 row lock(s)
  381. ---TRANSACTION 421994453559488, not started
  382. 0 lock struct(s), heap size 1136, 0 row lock(s)
  383. ---TRANSACTION 421994453756896, not started
  384. 0 lock struct(s), heap size 1136, 0 row lock(s)
  385. ---TRANSACTION 421994453752928, not started
  386. 0 lock struct(s), heap size 1136, 0 row lock(s)
  387. ---TRANSACTION 421994453727136, not started
  388. 0 lock struct(s), heap size 1136, 0 row lock(s)
  389. ---TRANSACTION 421994453699360, not started
  390. 0 lock struct(s), heap size 1136, 0 row lock(s)
  391. ---TRANSACTION 421994453690432, not started
  392. 0 lock struct(s), heap size 1136, 0 row lock(s)
  393. ---TRANSACTION 421994453688448, not started
  394. 0 lock struct(s), heap size 1136, 0 row lock(s)
  395. ---TRANSACTION 421994453681504, not started
  396. 0 lock struct(s), heap size 1136, 0 row lock(s)
  397. ---TRANSACTION 421994453668608, not started
  398. 0 lock struct(s), heap size 1136, 0 row lock(s)
  399. ---TRANSACTION 421994453659680, not started
  400. 0 lock struct(s), heap size 1136, 0 row lock(s)
  401. ---TRANSACTION 421994453568416, not started
  402. 0 lock struct(s), heap size 1136, 0 row lock(s)
  403. ---TRANSACTION 421994453763840, not started
  404. 0 lock struct(s), heap size 1136, 0 row lock(s)
  405. ---TRANSACTION 421994453576352, not started
  406. 0 lock struct(s), heap size 1136, 0 row lock(s)
  407. ---TRANSACTION 421994453542624, not started
  408. 0 lock struct(s), heap size 1136, 0 row lock(s)
  409. ---TRANSACTION 421994453541632, not started
  410. 0 lock struct(s), heap size 1136, 0 row lock(s)
  411. ---TRANSACTION 421994453653728, not started
  412. 0 lock struct(s), heap size 1136, 0 row lock(s)
  413. ---TRANSACTION 421994453652736, not started
  414. 0 lock struct(s), heap size 1136, 0 row lock(s)
  415. ---TRANSACTION 421994453651744, not started
  416. 0 lock struct(s), heap size 1136, 0 row lock(s)
  417. ---TRANSACTION 421994453650752, not started
  418. 0 lock struct(s), heap size 1136, 0 row lock(s)
  419. ---TRANSACTION 421994453649760, not started
  420. 0 lock struct(s), heap size 1136, 0 row lock(s)
  421. ---TRANSACTION 421994453648768, not started
  422. 0 lock struct(s), heap size 1136, 0 row lock(s)
  423. ---TRANSACTION 421994453647776, not started
  424. 0 lock struct(s), heap size 1136, 0 row lock(s)
  425. ---TRANSACTION 421994453646784, not started
  426. 0 lock struct(s), heap size 1136, 0 row lock(s)
  427. ---TRANSACTION 421994453645792, not started
  428. 0 lock struct(s), heap size 1136, 0 row lock(s)
  429. ---TRANSACTION 421994453644800, not started
  430. 0 lock struct(s), heap size 1136, 0 row lock(s)
  431. ---TRANSACTION 421994453643808, not started
  432. 0 lock struct(s), heap size 1136, 0 row lock(s)
  433. ---TRANSACTION 421994453642816, not started
  434. 0 lock struct(s), heap size 1136, 0 row lock(s)
  435. ---TRANSACTION 421994453641824, not started
  436. 0 lock struct(s), heap size 1136, 0 row lock(s)
  437. ---TRANSACTION 421994453640832, not started
  438. 0 lock struct(s), heap size 1136, 0 row lock(s)
  439. ---TRANSACTION 421994453639840, not started
  440. 0 lock struct(s), heap size 1136, 0 row lock(s)
  441. ---TRANSACTION 421994453638848, not started
  442. 0 lock struct(s), heap size 1136, 0 row lock(s)
  443. ---TRANSACTION 421994453637856, not started
  444. 0 lock struct(s), heap size 1136, 0 row lock(s)
  445. ---TRANSACTION 421994453636864, not started
  446. 0 lock struct(s), heap size 1136, 0 row lock(s)
  447. ---TRANSACTION 421994453635872, not started
  448. 0 lock struct(s), heap size 1136, 0 row lock(s)
  449. ---TRANSACTION 421994453634880, not started
  450. 0 lock struct(s), heap size 1136, 0 row lock(s)
  451. ---TRANSACTION 421994453633888, not started
  452. 0 lock struct(s), heap size 1136, 0 row lock(s)
  453. ---TRANSACTION 421994453632896, not started
  454. 0 lock struct(s), heap size 1136, 0 row lock(s)
  455. ---TRANSACTION 421994453631904, not started
  456. 0 lock struct(s), heap size 1136, 0 row lock(s)
  457. ---TRANSACTION 421994453630912, not started
  458. 0 lock struct(s), heap size 1136, 0 row lock(s)
  459. ---TRANSACTION 421994453629920, not started
  460. 0 lock struct(s), heap size 1136, 0 row lock(s)
  461. ---TRANSACTION 421994453628928, not started
  462. 0 lock struct(s), heap size 1136, 0 row lock(s)
  463. ---TRANSACTION 421994453627936, not started
  464. 0 lock struct(s), heap size 1136, 0 row lock(s)
  465. ---TRANSACTION 421994453626944, not started
  466. 0 lock struct(s), heap size 1136, 0 row lock(s)
  467. ---TRANSACTION 421994453625952, not started
  468. 0 lock struct(s), heap size 1136, 0 row lock(s)
  469. ---TRANSACTION 421994453624960, not started
  470. 0 lock struct(s), heap size 1136, 0 row lock(s)
  471. ---TRANSACTION 421994453623968, not started
  472. 0 lock struct(s), heap size 1136, 0 row lock(s)
  473. ---TRANSACTION 421994453621984, not started
  474. 0 lock struct(s), heap size 1136, 0 row lock(s)
  475. ---TRANSACTION 421994453620992, not started
  476. 0 lock struct(s), heap size 1136, 0 row lock(s)
  477. ---TRANSACTION 421994453620000, not started
  478. 0 lock struct(s), heap size 1136, 0 row lock(s)
  479. ---TRANSACTION 421994453610080, not started
  480. 0 lock struct(s), heap size 1136, 0 row lock(s)
  481. ---TRANSACTION 421994453609088, not started
  482. 0 lock struct(s), heap size 1136, 0 row lock(s)
  483. ---TRANSACTION 421994453608096, not started
  484. 0 lock struct(s), heap size 1136, 0 row lock(s)
  485. ---TRANSACTION 421994453607104, not started
  486. 0 lock struct(s), heap size 1136, 0 row lock(s)
  487. ---TRANSACTION 421994453606112, not started
  488. 0 lock struct(s), heap size 1136, 0 row lock(s)
  489. ---TRANSACTION 421994453605120, not started
  490. 0 lock struct(s), heap size 1136, 0 row lock(s)
  491. ---TRANSACTION 421994453604128, not started
  492. 0 lock struct(s), heap size 1136, 0 row lock(s)
  493. ---TRANSACTION 421994453603136, not started
  494. 0 lock struct(s), heap size 1136, 0 row lock(s)
  495. ---TRANSACTION 421994453602144, not started
  496. 0 lock struct(s), heap size 1136, 0 row lock(s)
  497. ---TRANSACTION 421994453601152, not started
  498. 0 lock struct(s), heap size 1136, 0 row lock(s)
  499. ---TRANSACTION 421994453600160, not started
  500. 0 lock struct(s), heap size 1136, 0 row lock(s)
  501. ---TRANSACTION 421994453599168, not started
  502. 0 lock struct(s), heap size 1136, 0 row lock(s)
  503. ---TRANSACTION 421994453598176, not started
  504. 0 lock struct(s), heap size 1136, 0 row lock(s)
  505. ---TRANSACTION 421994453597184, not started
  506. 0 lock struct(s), heap size 1136, 0 row lock(s)
  507. ---TRANSACTION 421994453596192, not started
  508. 0 lock struct(s), heap size 1136, 0 row lock(s)
  509. ---TRANSACTION 421994453595200, not started
  510. 0 lock struct(s), heap size 1136, 0 row lock(s)
  511. ---TRANSACTION 421994453570400, not started
  512. 0 lock struct(s), heap size 1136, 0 row lock(s)
  513. ---TRANSACTION 421994453567424, not started
  514. 0 lock struct(s), heap size 1136, 0 row lock(s)
  515. ---TRANSACTION 421994453562464, not started
  516. 0 lock struct(s), heap size 1136, 0 row lock(s)
  517. ---TRANSACTION 421994453553536, not started
  518. 0 lock struct(s), heap size 1136, 0 row lock(s)
  519. ---TRANSACTION 10717679987, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
  520. mysql tables in use 1, locked 1
  521. 0 lock struct(s), heap size 1136, 0 row lock(s)
  522. MySQL thread id 4294902044, OS thread handle 140493710145280, query id 5218226974 11.178.251.210 drcwriter_shunit updating
  523. UPDATE `cco_xspace_session_0002`.`xspace_session_heart_check_0095` SET `last_heart_time`='2019-11-07 10:15:20' , `gmt_modified`='2019-11-07 10:15:20' WHERE `id`=5498433091
  524. ---TRANSACTION 10717679986, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
  525. mysql tables in use 1, locked 1
  526. 0 lock struct(s), heap size 1136, 0 row lock(s)
  527. MySQL thread id 4294902042, OS thread handle 140514616604416, query id 5218226973 11.178.251.210 drcwriter_shunit updating
  528. UPDATE `cco_xspace_session_0003`.`xspace_chat_robot_client_0125` SET `gmt_modified`='2019-11-07 10:15:20' , `status`=0 WHERE `id`=1762275607
  529. ---TRANSACTION 10717679985, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
  530. mysql tables in use 1, locked 1
  531. 0 lock struct(s), heap size 1136, 0 row lock(s)
  532. MySQL thread id 4294902048, OS thread handle 140509053966080, query id 5218226972 11.178.251.210 drcwriter_shunit update
  533. INSERT IGNORE INTO `cco_xspace_session_0002`.`xspace_chat_robot_log_0083` SET `id`=4899842401 , `gmt_create`='2019-11-07 10:15:20' , `gmt_modified`='2019-11-07 10:15:20' , `robot_id`=2 , `operater`=0 , `event`=CONVERT('{\"sid\": \"542214075eec4e878ac34f353f9c7355\", \"action\": \"init\", \"speech\": \"\", \"status\": 0, \"robotId\": 2, \"traceId\": \"0b16f58c15730929197795684d08c5\", \"servicerId\": 16704135, \"changeSource\": \"xspace\", \"channel_type\": \"OCS\", \"robotSessionId\": 1767584842}',JSON) , `operation`=_utf8mb4'send_robot_status' , `sid`=_utf8mb4'542214075eec4e878ac34f353f9c7355
  534. ---TRANSACTION 10717679984, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
  535. mysql tables in use 1, locked 1
  536. 0 lock struct(s), heap size 1136, 0 row lock(s)
  537. MySQL thread id 4294902041, OS thread handle 140520123766528, query id 5218226971 11.178.251.210 drcwriter_shunit updating
  538. UPDATE `cco_xspace_session_0002`.`xspace_session_heart_check_0076` SET `last_heart_time`='2019-11-07 10:15:20' , `gmt_modified`='2019-11-07 10:15:20' WHERE `id`=5492066907
  539. ---TRANSACTION 10717679983, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
  540. mysql tables in use 1, locked 1
  541. 0 lock struct(s), heap size 1136, 0 row lock(s)
  542. MySQL thread id 4294902043, OS thread handle 140515195418368, query id 5218226970 11.178.251.210 drcwriter_shunit update
  543. INSERT IGNORE INTO `cco_xspace_session_0002`.`xspace_chat_robot_log_0083` SET `id`=4899842400 , `gmt_create`='2019-11-07 10:15:20' , `gmt_modified`='2019-11-07 10:15:20' , `robot_id`=2 , `operater`=0 , `event`=CONVERT('{\"sid\": \"542214075eec4e878ac34f353f9c7355\", \"action\": \"sys_end\", \"speech\": \"\", \"status\": -1, \"robotId\": 2, \"traceId\": \"0b16f58c15730929197795684d08c5\", \"servicerId\": 16704135, \"changeSource\": \"xspace\", \"channel_type\": \"OCS\", \"robotSessionId\": 1767584841}',JSON) , `operation`=_utf8mb4'send_robot_status' , `sid`=_utf8mb4'542214075eec4e878ac34f353f9c
  544. ---TRANSACTION 10717679982, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
  545. mysql tables in use 1, locked 1
  546. 0 lock struct(s), heap size 1136, 0 row lock(s)
  547. MySQL thread id 4294902040, OS thread handle 140518339049216, query id 5218226969 11.178.251.210 drcwriter_shunit update
  548. INSERT IGNORE INTO `cco_xspace_session_0002`.`xspace_chat_robot_log_0083` SET `id`=4899842399 , `gmt_create`='2019-11-07 10:15:20' , `gmt_modified`='2019-11-07 10:15:20' , `robot_id`=2 , `operater`=0 , `event`=CONVERT('{\"sid\": \"542214075eec4e878ac34f353f9c7355\", \"action\": \"init\", \"speech\": \"\", \"status\": 0, \"robotId\": 2, \"traceId\": \"0b16f58c15730929197795684d08c5\", \"servicerId\": 16704135, \"changeSource\": \"xspace\", \"channel_type\": \"OCS\", \"robotSessionId\": 1767584841}',JSON) , `operation`=_utf8mb4'send_robot_status' , `sid`=_utf8mb4'542214075eec4e878ac34f353f9c7355
  549. ---TRANSACTION 10717679981, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
  550. mysql tables in use 1, locked 1
  551. 0 lock struct(s), heap size 1136, 0 row lock(s)
  552. MySQL thread id 4294902037, OS thread handle 140510619432704, query id 5218226967 11.178.251.210 drcwriter_shunit updating
  553. DELETE FROM `cco_xspace_session_0003`.`xspace_session_heart_check_0127` WHERE `id`=5498081814
  554. ---TRANSACTION 10717679980, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
  555. mysql tables in use 1, locked 1
  556. 0 lock struct(s), heap size 1136, 0 row lock(s)
  557. MySQL thread id 4294902039, OS thread handle 140509237450496, query id 5218226968 11.178.251.210 drcwriter_shunit update
  558. INSERT IGNORE INTO `cco_xspace_session_0002`.`xspace_chat_robot_client_0083` SET `id`=1767584841 , `gmt_create`='2019-11-07 10:15:20' , `gmt_modified`='2019-11-07 10:15:20' , `robot_id`=2 , `sid`=_utf8mb4'542214075eec4e878ac34f353f9c7355' , `status`=0 , `start_time`='2019-11-07 10:15:20' , `end_time`=NULL , `service_id`=16704135 , `service_buid`=NULL , `tenant_id`=3 , `member_id`=2206653888825 , `department_id`=250 , `group_id`=56038
  559. ---TRANSACTION 10717679979, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
  560. mysql tables in use 1, locked 1
  561. 0 lock struct(s), heap size 1136, 0 row lock(s)
  562. MySQL thread id 4294902036, OS thread handle 140520070309632, query id 5218226966 11.178.251.210 drcwriter_shunit updating
  563. UPDATE `cco_xspace_session_0003`.`xspace_session_heart_check_0114` SET `last_heart_time`='2019-11-07 10:15:20' , `gmt_modified`='2019-11-07 10:15:20' WHERE `id`=5492354321
  564. ---TRANSACTION 10717679978, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
  565. mysql tables in use 1, locked 1
  566. 0 lock struct(s), heap size 1136, 0 row lock(s)
  567. MySQL thread id 4294902038, OS thread handle 140519669733120, query id 5218226965 11.178.251.210 drcwriter_shunit updating
  568. UPDATE `cco_xspace_session_0003`.`xspace_session_heart_check_0100` SET `last_heart_time`='2019-11-07 10:15:20' , `gmt_modified`='2019-11-07 10:15:20' WHERE `id`=5498113695
  569. --------
  570. FILE I/O
  571. --------
  572. I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
  573. I/O thread 1 state: waiting for completed aio requests (log thread)
  574. I/O thread 2 state: waiting for completed aio requests (read thread)
  575. I/O thread 3 state: waiting for completed aio requests (read thread)
  576. I/O thread 4 state: waiting for completed aio requests (read thread)
  577. I/O thread 5 state: waiting for completed aio requests (read thread)
  578. I/O thread 6 state: waiting for completed aio requests (read thread)
  579. I/O thread 7 state: waiting for completed aio requests (read thread)
  580. I/O thread 8 state: waiting for completed aio requests (read thread)
  581. I/O thread 9 state: waiting for completed aio requests (read thread)
  582. I/O thread 10 state: waiting for completed aio requests (write thread)
  583. I/O thread 11 state: waiting for completed aio requests (write thread)
  584. I/O thread 12 state: waiting for completed aio requests (write thread)
  585. I/O thread 13 state: waiting for completed aio requests (write thread)
  586. I/O thread 14 state: waiting for completed aio requests (write thread)
  587. I/O thread 15 state: waiting for completed aio requests (write thread)
  588. I/O thread 16 state: waiting for completed aio requests (write thread)
  589. I/O thread 17 state: waiting for completed aio requests (write thread)
  590. Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0] ,
  591.  ibuf aio reads:, log i/o's:, sync i/o's:
  592. Pending flushes (fsync) log: 0; buffer pool: 0
  593. 422118905 OS file reads, 5266550419 OS file writes, 671873711 OS fsyncs
  594. 88.35 reads/s, 16384 avg bytes/read, 786.57 writes/s, 208.96 fsyncs/s
  595. -------------------------------------
  596. INSERT BUFFER AND ADAPTIVE HASH INDEX
  597. -------------------------------------
  598. Ibuf: size 1, free list len 0, seg size 2, 0 merges
  599. merged operations:
  600.  insert 0, delete mark 0, delete 0
  601. discarded operations:
  602.  insert 0, delete mark 0, delete 0
  603. Hash table size 2212699, node heap has 114 buffer(s)
  604. Hash table size 2212699, node heap has 63 buffer(s)
  605. Hash table size 2212699, node heap has 46 buffer(s)
  606. Hash table size 2212699, node heap has 13 buffer(s)
  607. Hash table size 2212699, node heap has 34 buffer(s)
  608. Hash table size 2212699, node heap has 81 buffer(s)
  609. Hash table size 2212699, node heap has 79 buffer(s)
  610. Hash table size 2212699, node heap has 98 buffer(s)
  611. 266.85 hash searches/s, 2031.85 non-hash searches/s
  612. ---
  613. LOG
  614. ---
  615. Log sequence number 5238953570974
  616. Log flushed up to   5238953192067
  617. Pages flushed up to 5238895690211
  618. Last checkpoint at  5238893392118
  619. 0 pending log flushes, 0 pending chkp writes
  620. 4119025225 log i/o's done, 397.76 log i/o's/second
  621. ----------------------
  622. BUFFER POOL AND MEMORY
  623. ----------------------
  624. Total large memory allocated 8795455488
  625. Dictionary memory allocated 639935871
  626. Buffer pool size   524288
  627. Free buffers       8099
  628. Database pages     515661
  629. Old database pages 190270
  630. Modified db pages  52049
  631. Pending reads      1
  632. Pending writes: LRU 0, flush list 0, single page 0
  633. Pages made young 550672254, not young 3566705046
  634. 79.97 youngs/s, 153.88 non-youngs/s
  635. Pages read 422105577, created 44129950, written 1146979024
  636. 88.35 reads/s, 5.97 creates/s, 388.81 writes/s
  637. Buffer pool hit rate 998 / 1000, young-making rate 2 / 1000 not 4 / 1000
  638. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  639. LRU len: 515661, unzip_LRU len: 0
  640. I/O sum[96400]:cur[188], unzip sum[0]:cur[0]
  641. ----------------------
  642. INDIVIDUAL BUFFER POOL INFO
  643. ----------------------
  644. ---BUFFER POOL 0
  645. Buffer pool size   131072
  646. Free buffers       2024
  647. Database pages     128912
  648. Old database pages 47567
  649. Modified db pages  15418
  650. Pending reads      1
  651. Pending writes: LRU 0, flush list 0, single page 0
  652. Pages made young 151501569, not young 916988519
  653. 25.47 youngs/s, 39.85 non-youngs/s
  654. Pages read 110844585, created 12359044, written 286949498
  655. 22.88 reads/s, 1.44 creates/s, 97.20 writes/s
  656. Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 2 / 1000
  657. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  658. LRU len: 128912, unzip_LRU len: 0
  659. I/O sum[24100]:cur[47], unzip sum[0]:cur[0]
  660. ---BUFFER POOL 1
  661. Buffer pool size   131072
  662. Free buffers       2024
  663. Database pages     128912
  664. Old database pages 47566
  665. Modified db pages  12382
  666. Pending reads      0
  667. Pending writes: LRU 0, flush list 0, single page 0
  668. Pages made young 133859239, not young 882471366
  669. 17.79 youngs/s, 36.62 non-youngs/s
  670. Pages read 103654470, created 10585375, written 286673730
  671. 21.85 reads/s, 1.79 creates/s, 97.20 writes/s
  672. Buffer pool hit rate 988 / 1000, young-making rate 10 / 1000 not 20 / 1000
  673. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  674. LRU len: 128912, unzip_LRU len: 0
  675. I/O sum[24100]:cur[47], unzip sum[0]:cur[0]
  676. ---BUFFER POOL 2
  677. Buffer pool size   131072
  678. Free buffers       2032
  679. Database pages     128912
  680. Old database pages 47566
  681. Modified db pages  11913
  682. Pending reads      0
  683. Pending writes: LRU 0, flush list 0, single page 0
  684. Pages made young 129400012, not young 884558406
  685. 17.50 youngs/s, 39.94 non-youngs/s
  686. Pages read 102960280, created 10586551, written 286660470
  687. 21.03 reads/s, 1.15 creates/s, 97.20 writes/s
  688. Buffer pool hit rate 987 / 1000, young-making rate 11 / 1000 not 25 / 1000
  689. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  690. LRU len: 128912, unzip_LRU len: 0
  691. I/O sum[24100]:cur[47], unzip sum[0]:cur[0]
  692. ---BUFFER POOL 3
  693. Buffer pool size   131072
  694. Free buffers       2019
  695. Database pages     128925
  696. Old database pages 47571
  697. Modified db pages  12336
  698. Pending reads      0
  699. Pending writes: LRU 0, flush list 0, single page 0
  700. Pages made young 135911434, not young 882686755
  701. 19.21 youngs/s, 37.47 non-youngs/s
  702. Pages read 104646242, created 10598980, written 286695326
  703. 22.59 reads/s, 1.59 creates/s, 97.20 writes/s
  704. Buffer pool hit rate 998 / 1000, young-making rate 2 / 1000 not 4 / 1000
  705. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  706. LRU len: 128925, unzip_LRU len: 0
  707. I/O sum[24100]:cur[47], unzip sum[0]:cur[0]
  708. --------------
  709. ROW OPERATIONS
  710. --------------
  711. 10 queries inside InnoDB, 0 queries in queue
  712. 0 read views open inside InnoDB
  713. Process ID=55859, Main thread ID=140509127366400, state: sleeping
  714. Number of rows inserted 1165447625, updated 2799376983, deleted 600012204, read 15629490844
  715. 143.35 inserts/s, 281.84 updates/s, 28.12 deletes/s, 330.49 reads/s
  716. ----------------------------
  717. END OF INNODB MONITOR OUTPUT
  718. ============================

关键日志如下：

  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2019-11-05 15:02:44 0x7fcd2caef700
  5. *** (1) TRANSACTION:
  6. TRANSACTION 10476863046, ACTIVE 0 sec starting index read
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
  9. MySQL thread id 3322429, OS thread handle 140493696530176, query id 5089036262 11.18.122.237 cco_xspace_sessi updating
  10. /* 0b51153515729373648846445e1bf6/0.1.1.4.3//e21a688f// */delete from `connected_session_users_0102`  where ((`sid` = '65c48fc3bcff47e1ac7a7ce787b2acde') AND (`user_id` = 417880447))
  11. *** (1) HOLDS THE LOCK(S):
  12. RECORD LOCKS space id 132 page no 15896 n bits 416 index idx_sid of table `cco_xspace_session_0003`.`connected_session_users_0102` trx id 10476863046 lock_mode X locks rec but not gap
  13. Record lock, heap no 262 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
  14.  0: len 30; hex 363563343866633362636666343765316163376137636537383762326163; asc 65c48fc3bcff47e1ac7a7ce787b2ac; (total 32 bytes);
  15.  1: len 8; hex 0000000139ee46da; asc     9 F ;;
  17. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  18. RECORD LOCKS space id 132 page no 16560 n bits 184 index PRIMARY of table `cco_xspace_session_0003`.`connected_session_users_0102` trx id 10476863046 lock_mode X locks rec but not gap waiting
  19. Record lock, heap no 117 PHYSICAL RECORD: n_fields 13; compact format; info bits 32
  20.  0: len 8; hex 0000000139ee46da; asc     9 F ;;
  21.  1: len 6; hex 000270783e47; asc   px>G;;
  22.  2: len 7; hex 37002180312260; asc 7 ! 1"`;;
  23.  3: len 5; hex 99a48af04c; asc     L;;
  24.  4: len 5; hex 99a48af04c; asc     L;;
  25.  5: len 30; hex 363563343866633362636666343765316163376137636537383762326163; asc 65c48fc3bcff47e1ac7a7ce787b2ac; (total 32 bytes);
  26.  6: len 8; hex 8000000018e8597f; asc       Y ;;
  27.  7: len 18; hex e6b3a1e6b2abe4b98be5a48fe5a48fe69cab; asc                   ;;
  28.  8: len 1; hex 81; asc  ;;
  29.  9: len 30; hex 303031326639303133636139336530653030303030303030313665363539; asc 0012f9013ca93e0e0000000016e659; (total 32 bytes);
  30.  10: len 8; hex 0000000000000003; asc         ;;
  31.  11: SQL NULL;
  32.  12: SQL NULL;
  34. *** (2) TRANSACTION:
  35. TRANSACTION 10476863047, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4999
  36. mysql tables in use 1, locked 1
  37. 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
  38. MySQL thread id 4294902021, OS thread handle 140519194687232, query id 5089036263 11.178.251.210 drcwriter_shunit updating
  39. DELETE FROM `cco_xspace_session_0003`.`connected_session_users_0102` WHERE `id`=5266884314
  40. *** (2) HOLDS THE LOCK(S):
  41. RECORD LOCKS space id 132 page no 16560 n bits 184 index PRIMARY of table `cco_xspace_session_0003`.`connected_session_users_0102` trx id 10476863047 lock_mode X locks rec but not gap
  42. Record lock, heap no 117 PHYSICAL RECORD: n_fields 13; compact format; info bits 32
  43.  0: len 8; hex 0000000139ee46da; asc     9 F ;;
  44.  1: len 6; hex 000270783e47; asc   px>G;;
  45.  2: len 7; hex 37002180312260; asc 7 ! 1"`;;
  46.  3: len 5; hex 99a48af04c; asc     L;;
  47.  4: len 5; hex 99a48af04c; asc     L;;
  48.  5: len 30; hex 363563343866633362636666343765316163376137636537383762326163; asc 65c48fc3bcff47e1ac7a7ce787b2ac; (total 32 bytes);
  49.  6: len 8; hex 8000000018e8597f; asc       Y ;;
  50.  7: len 18; hex e6b3a1e6b2abe4b98be5a48fe5a48fe69cab; asc                   ;;
  51.  8: len 1; hex 81; asc  ;;
  52.  9: len 30; hex 303031326639303133636139336530653030303030303030313665363539; asc 0012f9013ca93e0e0000000016e659; (total 32 bytes);
  53.  10: len 8; hex 0000000000000003; asc         ;;
  54.  11: SQL NULL;
  55.  12: SQL NULL;
  57. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  58. RECORD LOCKS space id 132 page no 15896 n bits 416 index idx_sid of table `cco_xspace_session_0003`.`connected_session_users_0102` trx id 10476863047 lock_mode X locks rec but not gap waiting
  59. Record lock, heap no 262 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
  60.  0: len 30; hex 363563343866633362636666343765316163376137636537383762326163; asc 65c48fc3bcff47e1ac7a7ce787b2ac; (total 32 bytes);
  61.  1: len 8; hex 0000000139ee46da; asc     9 F ;;
  63. *** WE ROLL BACK TRANSACTION (1)

死锁日志分析：

  1. 获取隔离级别（备库）

    1. # sql
    2. select @@tx_isolation,@@global.tx_isolation;
    3. # 结果
    4. @@tx_isolation    @@global.tx_isolation
    5. READ-COMMITTED    READ-COMMITTED
    6. # 可以看到是 rc 隔离级别

  2. 查看表结构，获取表上索引信息（主备一致）

    1. id 为 primary
    2. sid 为 normal 索引（非唯一索引）
  3. 获取 mysql 版本（备库）
  1.  # sql
  2.  select version();
  3.  # 结果
  4.  5.7.14.5-AliSQL-X-Cluster-1.4.1.12-20190912-log

事务 A：delete from connected_session_users_0102 where ((sid = ‘65c48fc3bcff47e1ac7a7ce787b2acde’) AND (user_id = 417880447))
事务 B：DELETE FROM cco_xspace_session_0003.connected_session_users_0102 WHERE id=5266884314

序号 事务 A 事务 B
1 获取 sid 上的 写 锁
2 获取 id 上的写锁
3 尝试获取 id 上的写锁（失败）
4 尝试获取 sid 上的 写 锁

其中第 3 步和第 4 步形成死锁。
疑惑：

  1. 事务 A 持有 sid 锁，获取 id 锁我理解，但是事务 B 持有 id 锁，去获取 sid 锁我不理解。

死锁重现

  1. docker 安装 mysql
  1. # 下载镜像
  2. docker pull mysql:5.7
  3. # 启动 mysql
  4. docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456  -d mysql:5.7
  5. # 进入 mysql 容器内部，使其可以被外网查询
  6. docker exec -it mysql bash
  9. # 以下为在 mysql 容器内部执行的命令
  10. # 登陆 mysql
  11. mysql -uroot -p123456
  12. # 设置远程连接
  13. grant all privileges on *.* to root@'%'identified by '123456';
  14. flush privileges;
  15. exit
  1. 创建测试表
  1. CREATE DATABASE if not EXISTS pfjia; 
  2. use pfjia; 
  3. DROP TABLE IF EXISTS `test`; 
  4. CREATE TABLE IF NOT EXISTS `test`(
  5. `id` int(11) DEFAULT NULL, 
  6. `sid` varchar(50) DEFAULT NULL, 
  7. KEY `idx_sid`(`sid`)
  8. ) ENGINE= InnoDB DEFAULT CHARSET= utf8mb4;
  1. 插入测试数据(通过脚本插入 1000 条数据)
  1. insert into  test values(?,?);
  2. # 示例 sql
  3. insert into  test values(1,'1');
  1. 开启 innodb_lock_monitor （用来查看一条语句执行时的加锁信息）
  1. # 5.6 及以前的方式
  2. use pfjia;
  3. create table innodb_lock_monitor(x int) engine=innodb;
  5. # 5.7 及以后的方式
  6. use pfjia;
  7. SET GLOBAL innodb_status_output_locks=ON;
  9. # 详情在 google 搜索关键字“innodb_lock_monitor”，选择不同版本的 mysql Reference Manual
  1. 修改事务隔离级别（因为此版本的 mysql 的默认隔离级别是 rr，需修改为 rc）
  1. SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  2. SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
  1. 重建连接，check 事务隔离级别是否被修改
  1.  # sql
  2.  select @@tx_isolation,@@global.tx_isolation;
  3.  # 结果
  4. @@tx_isolation    @@global.tx_isolation
  5. READ-COMMITTED    READ-COMMITTED
  6. # 可以看到是事务隔离级别已被修改为 rc
  1. 根据 sid 删除数据
  1. begin;
  2. delete from test where sid='55';
  1. 查看 innodb status
  1. # sql
  2.  show engine innodb status;
  3. # 关键结果
  5. ---TRANSACTION 2843, ACTIVE 1161 sec
  6. 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
  7. MySQL thread id 8, OS thread handle 140293095675648, query id 2251 42.120.74.114 root
  8. TABLE LOCK table `pfjia`.`test` trx id 2843 lock mode IX
  9. RECORD LOCKS space id 25 page no 8 n bits 1064 index idx_sid of table `pfjia`.`test` trx id 2843 lock_mode X locks rec but not gap
  10. Record lock, heap no 291 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
  11.  0: len 2; hex 3336; asc 36;;
  12.  1: len 6; hex 000000000223; asc      #;;
  14. RECORD LOCKS space id 25 page no 5 n bits 528 index GEN_CLUST_INDEX of table `pfjia`.`test` trx id 2843 lock_mode X locks rec but not gap
  15. Record lock, heap no 37 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
  16.  0: len 6; hex 000000000223; asc      #;;
  17.  1: len 6; hex 000000000b1b; asc       ;;
  18.  2: len 7; hex 29000001cc0110; asc )      ;;
  19.  3: len 4; hex 80000024; asc    $;;
  20.  4: len 2; hex 3336; asc 36;;