单据提交报update sa_task错误

xuxu

jishuang 发表于 2018-4-2 17:41
这个错误就是mysql错误信息抛出的标准的mysql错误信息，设置了索引后数据库重启了吗？

x5具体用的什么版本 ...

是的 这个问题很多  nohup里的log显示很多这种错误信息X5版本是3.7
添加过索引 但是数据库是否重启过 我要去问下DB管理员

jishuang

用指令show engine innodb status \G 查看是那一个查询进程锁表

xuxu

jishuang 发表于 2018-4-3 09:07
用指令show engine innodb status \G 查看是那一个查询进程锁表

貌似lock的很厉害  都可以称得上严重了
我一段一段发给你 帮忙看下吧

xuxu

jishuang 发表于 2018-4-3 09:07
用指令show engine innodb status \G 查看是那一个查询进程锁表

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-04-03 10:10:28 0x7f2c4ad13700
*** (1) TRANSACTION:
TRANSACTION 421306668243392, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 1814 lock struct(s), heap size 172240, 19832 row lock(s)
MySQL thread id 42165065, OS thread handle 139828209981184, query id 313875081 172.17.250.26 root Sending data
INsert into temTable_Sdata1
                select sID from sa_task a where  sStatusID in ('tesReady','tesExecuting') and a.sExecutorPersonName = CONCAT(loginName)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 521 page no 33119 n bits 88 index PRIMARY of table `expense_sys`.`sa_task` trx id 421306668243392 lock mode S waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 130; compact format; info bits 0
0: len 30; hex 304230343735413431373244343230463931303535413739393131393037; asc 0B0475A4172D420F91055A79911907; (total 32 bytes);
1: len 6; hex 000001243253; asc    $2S;;
2: len 7; hex 7f000001f30e3e; asc       >;;

xuxu

jishuang 发表于 2018-4-3 09:07
用指令show engine innodb status \G 查看是那一个查询进程锁表

*** (2) TRANSACTION:
TRANSACTION 19149395, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 42158833, OS thread handle 139828210513664, query id 313875099 172.17.250.26 root update
INSERT INTO SA_Task (SA_Task.sID, SA_Task.SPROCESS, SA_Task.SSTATUSID, SA_Task.SVARIABLE, SA_Task.SEXECUTEMODE, SA_Task.SCREATORPERSONNAME, SA_Task.SCREATETIME, SA_Task.SFLOWID, SA_Task.SSTATUSNAME, SA_Task.SEXECUTORPOSNAME, SA_Task.SCREATORFNAME, SA_Task.SCREATOROGNNAME, SA_Task.SEURL, SA_Task.SCREATORPOSID, SA_Task.SLASTMODIFYTIME, SA_Task.SEXECUTORPOSID, SA_Task.SCREATORDEPTID, SA_Task.SEXECUTORPERSONNAME, SA_Task.SEXECUTORPERSONID, SA_Task.STYPENAME, SA_Task.SDATA1, SA_Task.SEXECUTOROGNNAME, SA_Task.SCREATORDEPTNAME, SA_Task.SCREATORPOSNAME, SA_Task.SAIID, SA_Task.SLOCK, SA_Task.SACTIVITY, SA_Task.SEXECUTORFNAME, SA_Task.SEXPECTSTARTTIME, SA_Task.SCREATORFID, SA_Task.SPROCESSTEMPLATEID2, SA_Task.SAISTATUSID, SA_Task.SACTUALSTARTTIME, SA_Task.SEXECUTORFID, SA_Task.SACTIVITYINTEMPLATE, SA_Task.SRESPONSIBLE, SA_Task.SPROCESSNAME, SA_Task.SKINDID, SA_Task.SPREEMPTMODE, SA_Task.SEXECUTORDEPTNAME, SA_Task.S
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 521 page no 33119 n bits 88 index PRIMARY of table `expense_sys`.`sa_task` trx id 19149395 lock_mode X locks rec but not gap
Record lock, heap no 18 PHYSICAL RECORD: n_fields 130; compact format; info bits 0
0: len 30; hex 304230343735413431373244343230463931303535413739393131393037; asc 0B0475A4172D420F91055A79911907; (total 32 bytes);
1: len 6; hex 000001243253; asc    $2S;;
2: len 7; hex 7f000001f30e3e; asc       >;;
3: SQL NULL;

xuxu

jishuang 发表于 2018-4-3 09:07
用指令show engine innodb status \G 查看是那一个查询进程锁表

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 521 page no 6283 n bits 80 index PRIMARY of table `expense_sys`.`sa_task` trx id 19149395 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 130; compact format; info bits 0
0: len 30; hex 303438333935314438433841344337434231333030313642383133373438; asc 0483951D8C8A4C7CB130016B813748; (total 32 bytes);
1: len 6; hex 0000011af0a0; asc       ;;
2: len 7; hex df000002190271; asc       q;;
3: SQL NULL;

xuxu

jishuang 发表于 2018-4-3 09:07
用指令show engine innodb status \G 查看是那一个查询进程锁表

LOCK WAIT 1814 lock struct(s), heap size 172240, 19832 row lock(s)
居然锁了这么多
我们这边的DB服务器 我记得只重启过一次  不知道是否和这个有关系

jishuang

temTable_Sdata1 这个是自己项目的表吧？

xuxu

jishuang 发表于 2018-4-3 14:02
temTable_Sdata1 这个是自己项目的表吧？

是的 是搜索代办任务的  也就是找sa_task表
但只是select 没有Update inserti delete等操作
这个会有影响？

jishuang

有可能
https://www.cnblogs.com/zhoujinyi/archive/2013/04/28/3049382.html

https://blog.csdn.net/muzizhuben/article/details/46533799