MySQL UPDATE挂起,没有错误
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL UPDATE挂起,没有错误，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8413字，纯文字阅读大概需要13分钟。
内容图文
这是我正在尝试运行的查询：
UPDATE tblmatters SET
subMatterName = null,
matterName = 'Travelers v. Allstate',
dateOpen = '2011-04-13',
dateClosed = null,
orgAttorney_fk = 129,
billingAttorney_fk = 129,
responsibleAttorney_fk = 129,
typeOfLawCode_fk = 050,
updatedBy = 'RAINMAKR',
updatedOn = NOW()
WHERE parentID = 275 AND matterID = '00968'
每次我尝试执行时,它只会挂起一个看起来像是试图运行查询的状态.我没有错误,没有停止,它将无限期地继续下去.我离开了一个多小时,它仍然坐在那里,试图执行.
关于这个问题是什么想法？如果您需要更多信息,请告诉我们！
编辑
这是SHOW CREATE TABLE tblmatters的输出：
CREATE TABLE `tblmatters` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parentID` int(11) NOT NULL,
`matterID` varchar(10) NOT NULL,
`subMatterID` varchar(7) NOT NULL DEFAULT '0000000',
`matterName` varchar(80) NOT NULL,
`akaMatterName` varchar(80) DEFAULT NULL,
`subMatterName` varchar(80) DEFAULT NULL,
`orgAttorney_fk` int(11) DEFAULT NULL,
`billingAttorney_fk` int(11) DEFAULT NULL,
`associateAttorney_fk` int(11) DEFAULT NULL,
`responsibleAttorney_fk` int(11) DEFAULT NULL,
`typeOfLawCode_fk` int(11) DEFAULT NULL,
`departmentCode_fk` int(11) DEFAULT NULL,
`dateOpen` date DEFAULT NULL,
`dateClosed` date DEFAULT NULL,
`dateReview` date DEFAULT NULL,
`notes` varchar(200) DEFAULT NULL,
`tags` varchar(200) DEFAULT NULL,
`updatedBy` varchar(12) NOT NULL,
`updatedOn` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniqueCompositeKey` (`parentID`,`matterID`,`subMatterID`),
KEY `parentID` (`parentID`),
KEY `orgAttorney` (`orgAttorney_fk`),
KEY `billingAttorney` (`billingAttorney_fk`),
KEY `associateAttorney` (`associateAttorney_fk`),
KEY `responsibleAttorney` (`responsibleAttorney_fk`),
KEY `typeOfLawCode` (`typeOfLawCode_fk`),
KEY `departmentCode` (`departmentCode_fk`),
CONSTRAINT `tblmatters_ibfk_1` FOREIGN KEY (`parentID`) REFERENCES `tblclients` (`id`),
CONSTRAINT `tblmatters_ibfk_2` FOREIGN KEY (`orgAttorney_fk`) REFERENCES `tblrequesters` (`id`),
CONSTRAINT `tblmatters_ibfk_3` FOREIGN KEY (`billingAttorney_fk`) REFERENCES `tblrequesters` (`id`),
CONSTRAINT `tblmatters_ibfk_4` FOREIGN KEY (`associateAttorney_fk`) REFERENCES `tblrequesters` (`id`),
CONSTRAINT `tblmatters_ibfk_5` FOREIGN KEY (`responsibleAttorney_fk`) REFERENCES `tblrequesters` (`id`),
CONSTRAINT `tblmatters_ibfk_6` FOREIGN KEY (`typeOfLawCode_fk`) REFERENCES `tbltypeoflaw` (`id`),
CONSTRAINT `tblmatters_ibfk_7` FOREIGN KEY (`departmentCode_fk`) REFERENCES `tbldepartments` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=68909 DEFAULT CHARSET=latin1
这是INNODB状态输出：
InnoDB
=====================================
131014 15:18:38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 60 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 916 1_second, 915 sleeps, 91 10_second, 6 background, 6 flush
srv_master_thread log flush and writes: 937
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 157, signal count 164
Mutex spin waits 40, rounds 562, OS waits 8
RW-shared spins 253, rounds 5347, OS waits 128
RW-excl spins 3, rounds 652, OS waits 21
Spin rounds per wait: 14.05 mutex, 21.13 RW-shared, 217.33 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 20042
Purge done for trx's n:o < 20024 undo n:o < 0
History list length 2031
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 29, OS thread handle 0xdc8, query id 7576476 localhost 127.0.0.1 root
SHOW ENGINE INNODB STATUS
---TRANSACTION 20041, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 2, OS thread handle 0x106c, query id 7556956 localhost 127.0.0.1 root Updating
UPDATE tblmatters SET
subMatterName = null,
matterName = 'Travelers v. Allstate',
dateOpen = '2011-04-13',
dateClosed = null,
orgAttorney_fk = 129,
billingAttorney_fk = 129,
responsibleAttorney_fk = 129,
typeOfLawCode_fk = 050,
updatedBy = 'RAINMAKR',
updatedOn = NOW()
WHERE
parentID = 275
AND
matterID = '00968'
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5806 n bits 464 index `uniqueCompositeKey` of table `ft_test`.`tblmatters` trx id 20041 lock_mode X waiting
Record lock, heap no 391 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000113; asc ;;
1: len 5; hex 3030393638; asc 00968;;
2: len 7; hex 30303030303030; asc 0000000;;
3: len 4; hex 8000ff27; asc ';;
------------------
---TRANSACTION 2001E, ACTIVE 898 sec
243 lock struct(s), heap size 47544, 6342 row lock(s), undo log entries 4922
MySQL thread id 10, OS thread handle 0x11f8, query id 7576479 localhost 127.0.0.1 root statistics
SELECT id FROM tblfolders WHERE parentID = 66633 AND folderNo = '1'
Trx read view will not see trx with id >= 2001F, sees < 2001F
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
3230 OS file reads, 4063 OS file writes, 1305 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 2.72 writes/s, 1.42 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 1 merges
merged operations:
insert 1, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 71 buffer(s)
8984.30 hash searches/s, 37.93 non-hash searches/s
---
LOG
---
Log sequence number 297152094
Log flushed up to 297152094
Last checkpoint at 297152094
0 pending log writes, 0 pending chkp writes
1003 log i/o's done, 1.12 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 197452
Buffer pool size 8192
Free buffers 5020
Database pages 3099
Old database pages 1163
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3091, created 8, written 2924
0.00 reads/s, 0.00 creates/s, 1.47 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3099, unzip_LRU len: 0
I/O sum[0]:cur[5], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread id 4144, state: sleeping
Number of rows inserted 2656, updated 2270, deleted 0, read 8876597
1.43 inserts/s, 1.33 updates/s, 0.00 deletes/s, 8987.88 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
这是PROCESSLIST：
1 root localhost:49283 ft_test Sleep 90
2 root localhost:49284 ft_test Query 5 Updating UPDATE tblmatters SET
subMatterName = null,
matterName = 'Travelers v. Allstate',
dateOpen = '2011-04-13',
dateClosed = null,
orgAttorney_fk = 129,
billingAttorney_fk = 129,
responsibleAttorney_fk = 129,
typeOfLawCode_fk = 050,
updatedBy = 'RAINMAKR',
updatedOn = NOW()
WHERE
parentID = 275
AND
matterID = '00968'
10 root localhost:49513 ft_test Sleep 0
22 root localhost:49701 Sleep 243
24 root localhost:49733 ft_test Sleep 68
26 root localhost:49735 Sleep 52
28 root localhost:49737 ft_test Sleep 35
29 root localhost:49738 ft_test Sleep 3
30 root localhost:49739 ft_test Sleep 33
31 root localhost:49740 ft_test Query 0 SHOW FULL PROCESSLIST
解决方法:
弄清楚了.我的问题与一个从未提交或回滚的正在进行的事务有关 – 因此,我只是在这个过程正在进行时被卡住了.
如果MySQL会让你知道而不是只是拖延,那会很好,但是,我的问题已经避免了.
内容总结
以上是互联网集市为您收集整理的MySQL UPDATE挂起,没有错误全部内容，希望文章能够帮你解决MySQL UPDATE挂起,没有错误所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】