Several "Lock wait timeout exceeded; try restarting the transcation"
Posted by: James Otigo
Date: May 05, 2016 02:08AM
Date: May 05, 2016 02:08AM
Hi all,
I'm very new to Mysql, though very comfortable with Oracle rdbms.
Users are reporting several errors regarding this message:
"Lock wait timeout exceeded; try restarting the transcation"
I've checked Oracle support note 1296631.1 I have dumped the output of the command
SHOW ENGINE INNODB STATUS
I understand this to be a locking/deadlock issue, but I'm unable to completely 'digest' the output. Which transaction is holding the lock, why is it holding the lock?
"
mysql> SHOW ENGINE INNODB STATUS;
| InnoDB | |
=====================================
160505 10:43:56 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 21 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3981807 1_second, 3981806 sleeps, 395758 10_second, 81732 background, 81718 flush
srv_master_thread log flush and writes: 3985942
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3818846, signal count 4140169
Mutex spin waits 13375930, rounds 131369400, OS waits 3426831
RW-shared spins 385575, rounds 7850101, OS waits 258490
RW-excl spins 39998, rounds 1312364, OS waits 24535
Spin rounds per wait: 9.82 mutex, 20.36 RW-shared, 32.81 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
160402 18:37:53
*** (1) TRANSACTION:
TRANSACTION 1DC5A39F, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 247778, OS thread handle 0x30e4, query id 7646360 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud update
INSERT INTO transaction (Oid,SourceMSISDN,AccountNumber, Amount,ReceiptNo,MpgTimestamp,DestMSISDN, CapturedBy,SenderName ,TransactionDate) VALUES('9fe5de58-a2c2-4a54-9487-d9acb8363238','0720278785','122-27187',10000.0,'KD25EV6B71','2016-04-02 17:04:01','254726387096','Britam','CYPRIAN OMARI', Date(now()))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 109596 n bits 232 index `receiptno` of table `mpesac2b`.`transaction` trx id 1DC5A39F lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 154 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 10; hex 4b443235455654375935; asc KD25EVT7Y5;;
1: len 30; hex 65383734323138332d376239642d346338622d396635322d306662656635; asc e8742183-7b9d-4c8b-9f52-0fbef5; (total 38 bytes);
*** (2) TRANSACTION:
TRANSACTION 1DC5A371, ACTIVE 21 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 247722, OS thread handle 0x33f0, query id 7645978 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud update
INSERT INTO transaction (Oid,SourceMSISDN,AccountNumber, Amount,ReceiptNo,MpgTimestamp,DestMSISDN, CapturedBy,SenderName ,TransactionDate) VALUES('aa55e73b-5c7f-4c21-bea2-ec8784d7ce0c','0722318124','122-39647',3800.0,'KD25EUX2PL','2016-04-02 16:45:00','254726387096','Britam','RICHARD MOGAKA', Date(now()))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 109596 n bits 232 index `receiptno` of table `mpesac2b`.`transaction` trx id 1DC5A371 lock mode S locks gap before rec
Record lock, heap no 154 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 10; hex 4b443235455654375935; asc KD25EVT7Y5;;
1: len 30; hex 65383734323138332d376239642d346338622d396635322d306662656635; asc e8742183-7b9d-4c8b-9f52-0fbef5; (total 38 bytes);
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 109596 n bits 232 index `receiptno` of table `mpesac2b`.`transaction` trx id 1DC5A371 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 154 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 10; hex 4b443235455654375935; asc KD25EVT7Y5;;
1: len 30; hex 65383734323138332d376239642d346338622d396635322d306662656635; asc e8742183-7b9d-4c8b-9f52-0fbef5; (total 38 bytes);
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 1E1752BA
Purge done for trx's n:o < 1E17522B undo n:o < 0
History list length 546
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1E1752B9, not started
MySQL thread id 643163, OS thread handle 0x2088, query id 19280246 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E1752B7, not started
MySQL thread id 643161, OS thread handle 0x277c, query id 19280231 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175212, not started
MySQL thread id 643157, OS thread handle 0x2598, query id 19280043 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175211, not started
MySQL thread id 643156, OS thread handle 0xcd8, query id 19280036 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 0, not started
MySQL thread id 643153, OS thread handle 0x204c, query id 19280247 localhost 127.0.0.1 root
SHOW ENGINE INNODB STATUS
---TRANSACTION 1E1752B4, not started
MySQL thread id 643155, OS thread handle 0x3eb4, query id 19280214 kelphqlpd008.COMPANY.com 10.10.5.124 mmakau
---TRANSACTION 1E175205, not started
MySQL thread id 643154, OS thread handle 0x404c, query id 19280014 kelphqlpd008.COMPANY.com 10.10.5.124 mmakau
---TRANSACTION 1E1751F1, not started
MySQL thread id 643124, OS thread handle 0x25e0, query id 19279926 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E1751A8, not started
MySQL thread id 643111, OS thread handle 0x3230, query id 19279656 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175128, not started
MySQL thread id 643107, OS thread handle 0x31c0, query id 19279525 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E1750FD, not started
MySQL thread id 643066, OS thread handle 0x4624, query id 19279222 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E1750B5, not started
MySQL thread id 643062, OS thread handle 0x9d0, query id 19279124 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175239, not started
MySQL thread id 643056, OS thread handle 0x4020, query id 19280083 kelphqlpd008.COMPANY.com 10.10.5.124 mmakau
---TRANSACTION 1E175047, not started
MySQL thread id 643055, OS thread handle 0x25a4, query id 19278976 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175045, not started
MySQL thread id 643053, OS thread handle 0x2bf0, query id 19278959 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175007, not started
MySQL thread id 643047, OS thread handle 0xf24, query id 19278860 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174F5C, not started
MySQL thread id 643002, OS thread handle 0x46fc, query id 19278429 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174F58, not started
MySQL thread id 643001, OS thread handle 0x2c20, query id 19278422 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174F57, not started
MySQL thread id 643000, OS thread handle 0x45cc, query id 19278412 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174F3F, not started
MySQL thread id 642998, OS thread handle 0x21d4, query id 19278385 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174EAF, not started
MySQL thread id 642996, OS thread handle 0x14a8, query id 19278091 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174E9F, not started
MySQL thread id 642994, OS thread handle 0xd8c, query id 19278064 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174E1B, not started
MySQL thread id 642956, OS thread handle 0x9b0, query id 19277583 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174E16, not started
MySQL thread id 642955, OS thread handle 0x2298, query id 19277556 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174CA9, not started
MySQL thread id 642951, OS thread handle 0xc64, query id 19277077 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174CA2, not started
MySQL thread id 642948, OS thread handle 0x478c, query id 19277036 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E1747EC, not started
MySQL thread id 642757, OS thread handle 0x2fc8, query id 19274724 10.10.5.23 mmakau
---TRANSACTION 1E1747E5, not started
MySQL thread id 642756, OS thread handle 0x1db8, query id 19274712 10.10.5.23 mmakau
---TRANSACTION 1E1747CF, not started
MySQL thread id 642754, OS thread handle 0x33e8, query id 19274679 10.10.5.23 mmakau
---TRANSACTION 1E17478D, not started
MySQL thread id 642753, OS thread handle 0x3860, query id 19274606 10.10.5.23 mmakau
---TRANSACTION 1E173E9A, not started
MySQL thread id 642296, OS thread handle 0x1d1c, query id 19269443 kelphqlpd008.COMPANY.com 10.10.5.124 mmakau
---TRANSACTION 1E174A5B, not started
MySQL thread id 642293, OS thread handle 0x2fb8, query id 19275861 KELPHQITD030 10.11.31.110 mmakau
---TRANSACTION 1E174FF5, not started
MySQL thread id 641180, OS thread handle 0x4620, query id 19278605 10.10.5.23 mmakau
---TRANSACTION 1E1752B6, ACTIVE 7 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 291 lock struct(s), heap size 31160, 14215 row lock(s)
MySQL thread id 16435, OS thread handle 0x974, query id 19280217 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud Sending data
SELECT oid,SourceMSISDN,AccountNumber,amount,ReceiptNo,MpgTimestamp,Status, ProcessingCode, TransactionDate, SenderName FROM transaction WHERE processingCode IS NULL OR processingCode=0 or ProcessingCode=1 FOR UPDATE
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 132982 n bits 136 index `PRIMARY` of table `mpesac2b`.`transaction` trx id 1E1752B6 lock_mode X waiting
Record lock, heap no 63 PHYSICAL RECORD: n_fields 30; compact format; info bits 0
0: len 30; hex 31353464386661642d303831342d346263652d386133312d346138323233; asc 154d8fad-0814-4bce-8a31-4a8223; (total 38 bytes);
1: len 6; hex 00001e175126; asc Q&;;
2: len 7; hex b3000003880110; asc ;;
3: len 4; hex 8004d15e; asc ^;;
4: SQL NULL;
5: SQL NULL;
6: len 10; hex 30373234343635393936; asc 0724465996;;
7: len 9; hex 3132312d3131363033; asc 121-11603;;
8: len 8; hex 0000000000c0b240; asc @;;
9: len 6; hex 42726974616d; asc Britam;;
10: SQL NULL;
11: SQL NULL;
12: SQL NULL;
13: SQL NULL;
14: len 10; hex 4b4535394f4153574635; asc KE59OASWF5;;
15: len 19; hex 323031362d30352d30352031303a33313a3133; asc 2016-05-05 10:31:13;;
16: SQL NULL;
17: len 4; hex 572af7f6; asc W* ;;
18: len 10; hex 323031362d30352d3035; asc 2016-05-05;;
19: SQL NULL;
20: SQL NULL;
21: len 15; hex 56495247494e4941204d57414e4749; asc VIRGINIA MWANGI;;
22: len 12; hex 323534373236333837303936; asc 254726387096;;
23: SQL NULL;
24: SQL NULL;
25: SQL NULL;
26: SQL NULL;
27: SQL NULL;
28: SQL NULL;
29: SQL NULL;
------------------
---TRANSACTION 1E175126, ACTIVE 454 sec
3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 643105, OS thread handle 0x2e58, query id 19279495 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175044, ACTIVE 703 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 643052, OS thread handle 0x2ea8, query id 19278949 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175042, ACTIVE 706 sec
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 643051, OS thread handle 0x41bc, query id 19278942 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174F4E, ACTIVE 1034 sec
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 642999, OS thread handle 0x3534, query id 19278391 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174EA7, ACTIVE 1142 sec
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 642995, OS thread handle 0x2d64, query id 19278070 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174CA3, ACTIVE 1349 sec
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 642949, OS thread handle 0x44e4, query id 19277043 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
--------
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
1707166 OS file reads, 73825788 OS file writes, 3027008 OS fsyncs
0.48 reads/s, 16384 avg bytes/read, 0.29 writes/s, 0.29 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7, 66406 merges
merged operations:
insert 66939, delete mark 282, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 1423691, node heap has 275 buffer(s)
0.00 hash searches/s, 2.00 non-hash searches/s
---
LOG
---
Log sequence number 8754963145829
Log flushed up to 8754963145569
Last checkpoint at 8754963143207
0 pending log writes, 0 pending chkp writes
983818 log i/o's done, 0.29 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 736182272; in additional pool allocated 0
Dictionary memory allocated 2436820
Buffer pool size 43904
Free buffers 1
Database pages 43627
Old database pages 16084
Modified db pages 20
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2027827, not young 0
0.33 youngs/s, 0.00 non-youngs/s
Pages read 1707027, created 29740252, written 71085043
0.48 reads/s, 0.00 creates/s, 0.00 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: 43627, unzip_LRU len: 0
I/O sum[12]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 2552, state: sleeping
Number of rows inserted 65050, updated 7279410804, deleted 21271, read 1093441080424
0.05 inserts/s, 0.05 updates/s, 0.00 deletes/s, 663.16 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
mysql> notee;
"
I'm very new to Mysql, though very comfortable with Oracle rdbms.
Users are reporting several errors regarding this message:
"Lock wait timeout exceeded; try restarting the transcation"
I've checked Oracle support note 1296631.1 I have dumped the output of the command
SHOW ENGINE INNODB STATUS
I understand this to be a locking/deadlock issue, but I'm unable to completely 'digest' the output. Which transaction is holding the lock, why is it holding the lock?
"
mysql> SHOW ENGINE INNODB STATUS;
| InnoDB | |
=====================================
160505 10:43:56 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 21 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3981807 1_second, 3981806 sleeps, 395758 10_second, 81732 background, 81718 flush
srv_master_thread log flush and writes: 3985942
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3818846, signal count 4140169
Mutex spin waits 13375930, rounds 131369400, OS waits 3426831
RW-shared spins 385575, rounds 7850101, OS waits 258490
RW-excl spins 39998, rounds 1312364, OS waits 24535
Spin rounds per wait: 9.82 mutex, 20.36 RW-shared, 32.81 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
160402 18:37:53
*** (1) TRANSACTION:
TRANSACTION 1DC5A39F, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 247778, OS thread handle 0x30e4, query id 7646360 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud update
INSERT INTO transaction (Oid,SourceMSISDN,AccountNumber, Amount,ReceiptNo,MpgTimestamp,DestMSISDN, CapturedBy,SenderName ,TransactionDate) VALUES('9fe5de58-a2c2-4a54-9487-d9acb8363238','0720278785','122-27187',10000.0,'KD25EV6B71','2016-04-02 17:04:01','254726387096','Britam','CYPRIAN OMARI', Date(now()))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 109596 n bits 232 index `receiptno` of table `mpesac2b`.`transaction` trx id 1DC5A39F lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 154 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 10; hex 4b443235455654375935; asc KD25EVT7Y5;;
1: len 30; hex 65383734323138332d376239642d346338622d396635322d306662656635; asc e8742183-7b9d-4c8b-9f52-0fbef5; (total 38 bytes);
*** (2) TRANSACTION:
TRANSACTION 1DC5A371, ACTIVE 21 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 247722, OS thread handle 0x33f0, query id 7645978 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud update
INSERT INTO transaction (Oid,SourceMSISDN,AccountNumber, Amount,ReceiptNo,MpgTimestamp,DestMSISDN, CapturedBy,SenderName ,TransactionDate) VALUES('aa55e73b-5c7f-4c21-bea2-ec8784d7ce0c','0722318124','122-39647',3800.0,'KD25EUX2PL','2016-04-02 16:45:00','254726387096','Britam','RICHARD MOGAKA', Date(now()))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 109596 n bits 232 index `receiptno` of table `mpesac2b`.`transaction` trx id 1DC5A371 lock mode S locks gap before rec
Record lock, heap no 154 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 10; hex 4b443235455654375935; asc KD25EVT7Y5;;
1: len 30; hex 65383734323138332d376239642d346338622d396635322d306662656635; asc e8742183-7b9d-4c8b-9f52-0fbef5; (total 38 bytes);
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 109596 n bits 232 index `receiptno` of table `mpesac2b`.`transaction` trx id 1DC5A371 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 154 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 10; hex 4b443235455654375935; asc KD25EVT7Y5;;
1: len 30; hex 65383734323138332d376239642d346338622d396635322d306662656635; asc e8742183-7b9d-4c8b-9f52-0fbef5; (total 38 bytes);
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 1E1752BA
Purge done for trx's n:o < 1E17522B undo n:o < 0
History list length 546
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1E1752B9, not started
MySQL thread id 643163, OS thread handle 0x2088, query id 19280246 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E1752B7, not started
MySQL thread id 643161, OS thread handle 0x277c, query id 19280231 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175212, not started
MySQL thread id 643157, OS thread handle 0x2598, query id 19280043 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175211, not started
MySQL thread id 643156, OS thread handle 0xcd8, query id 19280036 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 0, not started
MySQL thread id 643153, OS thread handle 0x204c, query id 19280247 localhost 127.0.0.1 root
SHOW ENGINE INNODB STATUS
---TRANSACTION 1E1752B4, not started
MySQL thread id 643155, OS thread handle 0x3eb4, query id 19280214 kelphqlpd008.COMPANY.com 10.10.5.124 mmakau
---TRANSACTION 1E175205, not started
MySQL thread id 643154, OS thread handle 0x404c, query id 19280014 kelphqlpd008.COMPANY.com 10.10.5.124 mmakau
---TRANSACTION 1E1751F1, not started
MySQL thread id 643124, OS thread handle 0x25e0, query id 19279926 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E1751A8, not started
MySQL thread id 643111, OS thread handle 0x3230, query id 19279656 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175128, not started
MySQL thread id 643107, OS thread handle 0x31c0, query id 19279525 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E1750FD, not started
MySQL thread id 643066, OS thread handle 0x4624, query id 19279222 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E1750B5, not started
MySQL thread id 643062, OS thread handle 0x9d0, query id 19279124 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175239, not started
MySQL thread id 643056, OS thread handle 0x4020, query id 19280083 kelphqlpd008.COMPANY.com 10.10.5.124 mmakau
---TRANSACTION 1E175047, not started
MySQL thread id 643055, OS thread handle 0x25a4, query id 19278976 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175045, not started
MySQL thread id 643053, OS thread handle 0x2bf0, query id 19278959 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175007, not started
MySQL thread id 643047, OS thread handle 0xf24, query id 19278860 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174F5C, not started
MySQL thread id 643002, OS thread handle 0x46fc, query id 19278429 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174F58, not started
MySQL thread id 643001, OS thread handle 0x2c20, query id 19278422 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174F57, not started
MySQL thread id 643000, OS thread handle 0x45cc, query id 19278412 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174F3F, not started
MySQL thread id 642998, OS thread handle 0x21d4, query id 19278385 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174EAF, not started
MySQL thread id 642996, OS thread handle 0x14a8, query id 19278091 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174E9F, not started
MySQL thread id 642994, OS thread handle 0xd8c, query id 19278064 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174E1B, not started
MySQL thread id 642956, OS thread handle 0x9b0, query id 19277583 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174E16, not started
MySQL thread id 642955, OS thread handle 0x2298, query id 19277556 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174CA9, not started
MySQL thread id 642951, OS thread handle 0xc64, query id 19277077 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174CA2, not started
MySQL thread id 642948, OS thread handle 0x478c, query id 19277036 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E1747EC, not started
MySQL thread id 642757, OS thread handle 0x2fc8, query id 19274724 10.10.5.23 mmakau
---TRANSACTION 1E1747E5, not started
MySQL thread id 642756, OS thread handle 0x1db8, query id 19274712 10.10.5.23 mmakau
---TRANSACTION 1E1747CF, not started
MySQL thread id 642754, OS thread handle 0x33e8, query id 19274679 10.10.5.23 mmakau
---TRANSACTION 1E17478D, not started
MySQL thread id 642753, OS thread handle 0x3860, query id 19274606 10.10.5.23 mmakau
---TRANSACTION 1E173E9A, not started
MySQL thread id 642296, OS thread handle 0x1d1c, query id 19269443 kelphqlpd008.COMPANY.com 10.10.5.124 mmakau
---TRANSACTION 1E174A5B, not started
MySQL thread id 642293, OS thread handle 0x2fb8, query id 19275861 KELPHQITD030 10.11.31.110 mmakau
---TRANSACTION 1E174FF5, not started
MySQL thread id 641180, OS thread handle 0x4620, query id 19278605 10.10.5.23 mmakau
---TRANSACTION 1E1752B6, ACTIVE 7 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 291 lock struct(s), heap size 31160, 14215 row lock(s)
MySQL thread id 16435, OS thread handle 0x974, query id 19280217 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud Sending data
SELECT oid,SourceMSISDN,AccountNumber,amount,ReceiptNo,MpgTimestamp,Status, ProcessingCode, TransactionDate, SenderName FROM transaction WHERE processingCode IS NULL OR processingCode=0 or ProcessingCode=1 FOR UPDATE
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 132982 n bits 136 index `PRIMARY` of table `mpesac2b`.`transaction` trx id 1E1752B6 lock_mode X waiting
Record lock, heap no 63 PHYSICAL RECORD: n_fields 30; compact format; info bits 0
0: len 30; hex 31353464386661642d303831342d346263652d386133312d346138323233; asc 154d8fad-0814-4bce-8a31-4a8223; (total 38 bytes);
1: len 6; hex 00001e175126; asc Q&;;
2: len 7; hex b3000003880110; asc ;;
3: len 4; hex 8004d15e; asc ^;;
4: SQL NULL;
5: SQL NULL;
6: len 10; hex 30373234343635393936; asc 0724465996;;
7: len 9; hex 3132312d3131363033; asc 121-11603;;
8: len 8; hex 0000000000c0b240; asc @;;
9: len 6; hex 42726974616d; asc Britam;;
10: SQL NULL;
11: SQL NULL;
12: SQL NULL;
13: SQL NULL;
14: len 10; hex 4b4535394f4153574635; asc KE59OASWF5;;
15: len 19; hex 323031362d30352d30352031303a33313a3133; asc 2016-05-05 10:31:13;;
16: SQL NULL;
17: len 4; hex 572af7f6; asc W* ;;
18: len 10; hex 323031362d30352d3035; asc 2016-05-05;;
19: SQL NULL;
20: SQL NULL;
21: len 15; hex 56495247494e4941204d57414e4749; asc VIRGINIA MWANGI;;
22: len 12; hex 323534373236333837303936; asc 254726387096;;
23: SQL NULL;
24: SQL NULL;
25: SQL NULL;
26: SQL NULL;
27: SQL NULL;
28: SQL NULL;
29: SQL NULL;
------------------
---TRANSACTION 1E175126, ACTIVE 454 sec
3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 643105, OS thread handle 0x2e58, query id 19279495 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175044, ACTIVE 703 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 643052, OS thread handle 0x2ea8, query id 19278949 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E175042, ACTIVE 706 sec
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 643051, OS thread handle 0x41bc, query id 19278942 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174F4E, ACTIVE 1034 sec
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 642999, OS thread handle 0x3534, query id 19278391 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174EA7, ACTIVE 1142 sec
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 642995, OS thread handle 0x2d64, query id 19278070 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
---TRANSACTION 1E174CA3, ACTIVE 1349 sec
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 642949, OS thread handle 0x44e4, query id 19277043 DEVELSERVER.COMPANY.COM 10.10.3.117 abiud
--------
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
1707166 OS file reads, 73825788 OS file writes, 3027008 OS fsyncs
0.48 reads/s, 16384 avg bytes/read, 0.29 writes/s, 0.29 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7, 66406 merges
merged operations:
insert 66939, delete mark 282, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 1423691, node heap has 275 buffer(s)
0.00 hash searches/s, 2.00 non-hash searches/s
---
LOG
---
Log sequence number 8754963145829
Log flushed up to 8754963145569
Last checkpoint at 8754963143207
0 pending log writes, 0 pending chkp writes
983818 log i/o's done, 0.29 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 736182272; in additional pool allocated 0
Dictionary memory allocated 2436820
Buffer pool size 43904
Free buffers 1
Database pages 43627
Old database pages 16084
Modified db pages 20
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2027827, not young 0
0.33 youngs/s, 0.00 non-youngs/s
Pages read 1707027, created 29740252, written 71085043
0.48 reads/s, 0.00 creates/s, 0.00 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: 43627, unzip_LRU len: 0
I/O sum[12]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 2552, state: sleeping
Number of rows inserted 65050, updated 7279410804, deleted 21271, read 1093441080424
0.05 inserts/s, 0.05 updates/s, 0.00 deletes/s, 663.16 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
mysql> notee;
"
Subject
Views
Written By
Posted
Several "Lock wait timeout exceeded; try restarting the transcation"
3046
May 05, 2016 02:08AM
1056
May 05, 2016 09:48PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.