Description:
I've got an "invisible" transaction locking a table.
There was nothing in "SHOW PROCESSLIST". No running query.
Nothing in the error.log
Everything was fine in my "SHOW TABLE STATUS"
CHECK TABLE 'client'; said everything was OK
SHOW OPEN TABLES said my table wasn't "open" or name_locked
But every DDL or DML Statement did a "Lock timeout" error.
Show engine INNODB Status return there was an active transaction running:
mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
081230 13:33:43 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1174, signal count 1168
Mutex spin waits 0, rounds 18420, OS waits 13
RW-shared spins 4262, OS waits 1008; RW-excl spins 283, OS waits 153
------------
TRANSACTIONS
------------
Trx id counter 0 16838139
Purge done for trx's n:o < 0 16838113 undo n:o < 0 0
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 16838134, not started, process no 5810, OS thread id 1152063824
MySQL thread id 47383, query id 2176058 localhost root
show engine innodb status
---TRANSACTION 0 16836634, not started, process no 5810, OS thread id 1153128784
MySQL thread id 47414, query id 2167509 staging.dev.iweb.ca 192.168.3.109 devistr
---TRANSACTION 0 16836534, not started, process no 5810, OS thread id 1147005264
MySQL thread id 47407, query id 2164191 staging.dev.iweb.ca 192.168.3.109 devistr
---TRANSACTION 0 16836501, not started, process no 5810, OS thread id 1148602704
MySQL thread id 47386, query id 2163092 staging.dev.iweb.ca 192.168.3.109 devistr
---TRANSACTION 0 16836498, not started, process no 5810, OS thread id 1150732624
MySQL thread id 47381, query id 2162863 staging.dev.iweb.ca 192.168.3.109 devistr
---TRANSACTION 0 16836497, not started, process no 5810, OS thread id 1150998864
MySQL thread id 47379, query id 2162851 web.dev.iweb.ca 192.168.3.103 devistr
---TRANSACTION 0 16836529, not started, process no 5810, OS thread id 1151265104
MySQL thread id 46975, query id 2163864 staging.dev.iweb.ca 192.168.3.109 devistr
---TRANSACTION 0 16838038, not started, process no 5810, OS thread id 1149135184
MySQL thread id 46959, query id 2168895 staging.dev.iweb.ca 192.168.3.109 devistr
---TRANSACTION 0 16838082, not started, process no 5810, OS thread id 1147803984
MySQL thread id 46651, query id 2170780 staging.dev.iweb.ca 192.168.3.109 devistr
---TRANSACTION 0 16837980, not started, process no 5810, OS thread id 1145407824
MySQL thread id 46642, query id 2167824 staging.dev.iweb.ca 192.168.3.109 devistr
---TRANSACTION 0 16838016, not started, process no 5810, OS thread id 1152330064
MySQL thread id 46035, query id 2168883 staging.dev.iweb.ca 192.168.3.109 devistr
---TRANSACTION 0 16836430, ACTIVE 8547 sec, process no 5810, OS thread id 1147271504
617 lock struct(s), heap size 63472, 56340 row lock(s), undo log entries 48
MySQL thread id 46962, query id 2170767 staging.dev.iweb.ca 192.168.3.109 devistr
Trx read view will not see trx with id >= 0 16836496, sees < 0 16836496
--------
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
82016 OS file reads, 9592 OS file writes, 4215 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
791 inserts, 791 merged recs, 207 merges
Hash table size 17393, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 9 3935466812
Log flushed up to 9 3935466812
Last checkpoint at 9 3935466812
0 pending log writes, 0 pending chkp writes
3111 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 25477642; in additional pool allocated 1048576
Dictionary memory allocated 3113368
Buffer pool size 512
Free buffers 0
Database pages 508
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 185179, created 1716, written 8881
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 5810, id 1140881744, state: waiting for server activity
Number of rows inserted 115183, updated 21348, deleted 60, read 22805859
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
As you can see, there is an active transaction running for 8547 sec, locking my table.
How to repeat:
I was not able to reproduce....
