最近線上的服務器偶爾會出現卡的現象，通過show engine innodb status 發現LATEST DETECTED DEADLOCK
------------------------
170814 11:27:05
*** (1) TRANSACTION:
TRANSACTION 19FF19CA, ACTIVE 0.315 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
LOCK BLOCKING MySQL thread id: 51016674 block 51259706
MySQL thread id 51259706, OS thread handle 0x2b7e3a70e700, query id 2089979089 10.165.223.110:60058 hdypt Updating
update bpm_ProcessInstanceRuntime set tenantId=null, actionStatus=null, archived=0, bingAn=0, bpmServer='bpmServer1', businessKey='_QG9KgICUEeeNHIFbQ7T3Eg', created='2017-08-14 10:29:48', createrDN='cn=李斯,ou=海淀區人民政府,o=海淀區機關', createrName='李斯', createrUID='_iB60UDsPEeW1dsJRU-8AWA', debug=1, deleted=0, description=null, fromId=null, instanceExpireDate=null, instanceRemindDate=null, masterId='_QG9KgICUEeeNHIFbQ7T3Eg', name=null, parentId=null, priority=0, processId='_0rQw4dG8EeaQ_ZrvF2Kt-g', processName='政府收文', processVersion=-1, routeType=null, running=1, serialNumber=null, slaveIndex=0, state='RUNNING', step=2, title='關于以區政府名義印發《海淀區推進生活垃圾分類作業方案》的請示', updated='2017-08-14 11:26:37', updaterDN='cn=王振宇,ou=政府辦資訊公開科,ou=政府辦,ou=政府系統,o=海淀區機關', updaterName='王?
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3760 page no 3291 n bits 104 index `PRIMARY` of table `hdyptdb`.`bpm_processinstanceruntime` trx id 19FF19CA lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 19FF19CC, ACTIVE 0.314 sec starting index read
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 51016674, OS thread handle 0x2b7f04a14700, query id 2089979118 10.165.223.110:34566 hdypt Updating
update bpm_ActivityInstanceRuntime set tenantId=null, activityId='_87aCING8EeaQ_ZrvF2Kt-g', activityInstanceExpireDate=null, activityInstanceId='_QG9KgYCUEeeNHIFbQ7T3Eg', activityInstanceRemindDate=null, bpmServer='bpmServer1', businessKey='_QG9KgYCUEeeNHIFbQ7T3Eg', created='2017-08-14 10:29:48', createrDN='cn=李斯,ou=海淀區人民政府,o=海淀區機關', createrName='李斯', createrUID='_iB60UDsPEeW1dsJRU-8AWA', deleted=0, description=null, explanation=null, fromActivityId='_9pSb0NG8EeaQ_ZrvF2Kt-g', fromId=null, fromTransitionId='_Qys8sNG9EeaQ_ZrvF2Kt-g', fromTransitionName='退回', maxStep=1, name='收文員收文', parentId=null, parentStep=0, priority=0, processId='_0rQw4dG8EeaQ_ZrvF2Kt-g', processVersion=-1, routeType=null, running=1, state='RUNNING', step=2, systemRoute=1, tabIndex=0, updated='2017-08-14 11:26:37', updaterDN='cn=王振宇,ou=政府辦資訊公開科,ou=政府辦,ou
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3760 page no 3291 n bits 104 index `PRIMARY` of table `hdyptdb`.`bpm_processinstanceruntime` trx id 19FF19CC lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1183 page no 12106 n bits 88 index `PRIMARY` of table `hdyptdb`.`bpm_activityinstanceruntime` trx id 19FF19CC lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
這是死鎖部分
還有部分是鎖等待的
--TRANSACTION 19FF496E, ACTIVE 2.051 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
LOCK BLOCKING MySQL thread id: 17063906 block 50646154
MySQL thread id 50646154, OS thread handle 0x2b7f061e6700, query id 2090444176 10.165.223.109:43597 hdypt statistics
select ticketgran0_.ID as ID34_0_, ticketgran0_.NUMBER_OF_TIMES_USED as NUMBER2_34_0_, ticketgran0_.CREATION_TIME as CREATION3_34_0_, ticketgran0_.EXPIRATION_POLICY as EXPIRATION4_34_0_, ticketgran0_.LAST_TIME_USED as LAST5_34_0_, ticketgran0_.PREVIOUS_LAST_TIME_USED as PREVIOUS6_34_0_, ticketgran0_.ticketGrantingTicket_ID as ticketG10_34_0_, ticketgran0_.AUTHENTICATION as AUTHENTI7_34_0_, ticketgran0_.EXPIRED as EXPIRED34_0_, ticketgran0_.SERVICES_GRANTED_ACCESS_TO as SERVICES9_34_0_ from CAS_TICKET_GRANTING_TICKET ticketgran0_ where ticketgran0_.ID='TGT-698-n52AJIetSrdTYyTk3HIWTlQTgp5nizwko7
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2766 page no 211413 n bits 72 index `PRIMARY` of table `hdyptdb`.`cas_ticket_granting_ticket` trx id 19FF496E lock_mode X locks rec but not gap waiting
------------------
TABLE LOCK table `hdyptdb`.`cas_ticket_granting_ticket` trx id 19FF496E lock mode IX
RECORD LOCKS space id 2766 page no 211413 n bits 72 index `PRIMARY` of table `hdyptdb`.`cas_ticket_granting_ticket` trx id 19FF496E lock_mode X locks rec but not gap waiting
各位大神有什么辦法剖析或者解決一下嗎 ？
uj5u.com熱心網友回復：
鎖多半要靠應用端來解決的吧，服務端檢查通知應用端哪些陳述句出了問題轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/115308.html
標籤：MySQL
上一篇：mysql 百萬級資料分頁 優化group by問題
下一篇：如何抽取資料？求大神幫助在線等