超过mysql锁等待超时,尝试重新启动事务

我在尝试执行具有一个更新语句的存储过程时遇到以下错误.

下面是错误发生后的INNODB STATUS OUTPUT：

=====================================
090828 12:54:36 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 33 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1796,signal count 1794
Mutex spin waits 0,rounds 3036,OS waits 85
RW-shared spins 3538,OS waits 1653; RW-excl spins 56,OS waits 55
------------
TRANSACTIONS
------------
Trx id counter 0 1289747
Purge done for trx's n:o < 0 1289739 undo n:o < 0 0
History list length 85
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 1289744,not started,process no 2986,OS thread id 140445151164752
MysqL thread id 462,query id 64252 Terminator 192.168.1.1 MysqLuser
---TRANSACTION 0 0,OS thread id 140445155424592
MysqL thread id 522,query id 64273 192.168.1.2 sysuser
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 1289710,OS thread id 140445132179792
MysqL thread id 490,query id 63941 Fembot 192.168.1.5 MysqLuser
---TRANSACTION 0 1289743,OS thread id 140445141580112
MysqL thread id 496,query id 64247 192.168.1.2 sysuser
---TRANSACTION 0 1289639,OS thread id 140445132978512
MysqL thread id 488,query id 63512 Fembot 192.168.1.5 MysqLuser
---TRANSACTION 0 1289742,OS thread id 140445134043472
MysqL thread id 461,query id 64242 Terminator 192.168.1.1 MysqLuser
---TRANSACTION 0 1289340,OS thread id 140445131114832
MysqL thread id 404,query id 59793 192.168.1.4 MysqLuser
---TRANSACTION 0 1289678,OS thread id 140445139450192
MysqL thread id 179,query id 63763 192.168.1.4 MysqLuser
---TRANSACTION 0 1289672,OS thread id 140445143976272
MysqL thread id 150,query id 63745 192.168.1.4 MysqLuser
---TRANSACTION 0 1289651,OS thread id 140445138385232
MysqL thread id 183,query id 63563 192.168.1.4 MysqLuser
---TRANSACTION 0 1289706,OS thread id 140445149833552
MysqL thread id 173,query id 63929 192.168.1.4 MysqLuser
---TRANSACTION 0 1289676,OS thread id 140445149034832
MysqL thread id 171,query id 63757 192.168.1.4 MysqLuser
---TRANSACTION 0 1289708,OS thread id 140445138917712
MysqL thread id 181,query id 63935 192.168.1.4 MysqLuser
---TRANSACTION 0 1289732,OS thread id 140445145307472
MysqL thread id 169,query id 64159 192.168.1.4 MysqLuser
---TRANSACTION 0 1289488,OS thread id 140445143710032
MysqL thread id 152,query id 62259 192.168.1.4 MysqLuser
---TRANSACTION 0 1289492,OS thread id 140445139982672
MysqL thread id 177,query id 62276 192.168.1.4 MysqLuser
---TRANSACTION 0 1287321,OS thread id 140445137586512
MysqL thread id 186,query id 11487 192.168.1.4 MysqLuser
---TRANSACTION 0 1287320,OS thread id 140445137852752
MysqL thread id 185,query id 11475 192.168.1.4 MysqLuser
---TRANSACTION 0 1289653,OS thread id 140445142911312
MysqL thread id 167,query id 63576 192.168.1.4 MysqLuser
---TRANSACTION 0 1289696,OS thread id 140445144508752
MysqL thread id 147,query id 63900 192.168.1.4 MysqLuser
---TRANSACTION 0 1287290,OS thread id 140445140248912
MysqL thread id 176,query id 11326 192.168.1.4 MysqLuser
---TRANSACTION 0 1287289,OS thread id 140445149301072
MysqL thread id 175,query id 11314 192.168.1.4 MysqLuser
---TRANSACTION 0 1289643,OS thread id 140445150632272
MysqL thread id 145,query id 63536 192.168.1.4 MysqLuser
---TRANSACTION 0 1287255,OS thread id 140445140515152
MysqL thread id 156,query id 11079 192.168.1.4 MysqLuser
---TRANSACTION 0 1289694,OS thread id 140445145839952
MysqL thread id 65,query id 63894 192.168.1.3 MysqLuser
---TRANSACTION 0 1286467,OS thread id 140445146106192
MysqL thread id 64,query id 3652 192.168.1.3 MysqLuser
---TRANSACTION 0 1286466,OS thread id 140445146372432
MysqL thread id 63,query id 3640 192.168.1.3 MysqLuser
---TRANSACTION 0 1289731,OS thread id 140445157288272
MysqL thread id 20,query id 64155 Fembot 192.168.1.5 MysqLuser
---TRANSACTION 0 1289746,ACTIVE 0 sec,OS thread id 140445151697232 starting index read
MysqL tables in use 1,locked 1
LOCK WAIT 4 lock struct(s),heap size 1216,6 row lock(s)
MysqL thread id 464,query id 64270 Terminator 192.168.1.1 MysqLuser Updating
Update test
               Set email = 1
               Where testid =  NAME_CONST('InputtestID',5)
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2952 n bits 176 index `PRIMARY` of table `DB`.`test` trx id 0 1289746 lock_mode X locks rec but not gap waiting
Record lock,heap no 6 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 8; hex 0000000000000005; asc         ;; 1: len 6; hex 00000013ae09; asc       ;; 2: len 7; hex 000000066629ae; asc     f) ;; 3: len 27; hex 796f67696e692e6b756c6b61726e69407961686f6f2e636f2e696e; asc yog.123@yahoo.co.in;; 4: len 6; hex 6950686f6e65; asc iPhone;; 5: len 28; hex 4e644a4b676d5661637068666b4868476c4761525a502b385a44303d; asc NdJKgmVacphfkHhGlGaRZP+8ZD0=;; 6: len 28; hex 577a786b6f4956532f735a374b4559732b4b6b794c39307a4a35513d; asc WzxkoIVS/sZ7KEYs+KkyL90zJ5Q=;; 7: len 1; hex 81; asc  ;; 8: len 1; hex 80; asc  ;; 9: len 8; hex 80001245b60a4c53; asc    E  LS;; 10: sql NULL; 11: len 30; hex 626635336237613736306633353462653665383834666666303936626233; asc bf53b7a760f354be6e884fff096bb3;...(truncated);

------------------
---TRANSACTION 0 1289745,OS thread id 140445136972112
10 lock struct(s),heap size 3024,6 row lock(s)
MysqL thread id 463,query id 64255 Terminator 192.168.1.1 MysqLuser
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0,aio writes: 0,ibuf aio reads: 0,log i/o's: 0,sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
20795 OS file reads,2556 OS file writes,1210 OS fsyncs
3.12 reads/s,37221 avg bytes/read,0.18 writes/s,0.12 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1,free list len 0,seg size 2,0 inserts,0 merged recs,0 merges
Hash table size 17393,node heap has 1 buffer(s)
0.00 hash searches/s,0.61 non-hash searches/s
---
LOG
---
Log sequence number 0 287498463
Log flushed up to   0 287498463
Last checkpoint at  0 287498463
0 pending log writes,0 pending chkp writes
913 log i/o's done,0.06 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 31312994; in additional pool allocated 1048576
Dictionary memory allocated 8764592
Buffer pool size   512
Free buffers       0
Database pages     511
Modified db pages  0
Pending reads 0
Pending writes: LRU 0,flush list 0,single page 0
Pages read 45969,created 76,written 1658
7.09 reads/s,0.00 creates/s,0.09 writes/s
Buffer pool hit rate 977 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB,0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2986,id 140445169375568,state: sleeping
Number of rows inserted 2367,updated 662,deleted 2330,read 30234862
0.00 inserts/s,0.03 updates/s,0.00 deletes/s,1828.97 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

这也是slow_query_logs：

# Query_time: 51.156942  Lock_time: 0.000008 Rows_sent: 0  Rows_examined: 5
SET timestamp=1251444327;
CALL SP_test(5,'test@test.com');

这是过程的更新语句：

Update test
set testflag = 1
where testid = 5;

请告诉我

最佳答案

我确实遇到了同样的问题.

这是与更改lower_case_table_names属性有关的问题.

您是否更改过上述财产？

如果在0、1、2之间切换,则数据库文件和索引将被破坏.
解决此问题的最佳方法：

>删除架构
>设置lower_case_table_names满足您的要求
>重新创建架构

超过mysql锁等待超时,尝试重新启动事务

猜你在找的MySQL相关文章