Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

On a development database with no other users other than myself, I'm getting the following error #1205 - Lock wait timeout exceeded; try restarting transaction when trying to run a simple alter statement.

ALTER TABLE proposals ADD STATUS INT( 1 ) NULL AFTER propnum ;

if I run SHOW ENGINE INNODB STATUS I get the following.

=====================================
2013-08-17 20:51:54 fa8 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 46 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 935 srv_active, 0 srv_shutdown, 104952 srv_idle
srv_master_thread log flush and writes: 105884
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 399
OS WAIT ARRAY INFO: signal count 408
Mutex spin waits 267, rounds 3238, OS waits 94
RW-shared spins 281, rounds 8430, OS waits 281
RW-excl spins 17, rounds 770, OS waits 23
Spin rounds per wait: 12.13 mutex, 30.00 RW-shared, 45.29 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 29698
Purge done for trx's n:o < 29691 undo n:o < 0 state: running but idle
History list length 1091
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 1278, OS thread handle 0xfa8, query id 9178 localhost ::1 david init
SHOW ENGINE INNODB STATUS
--------
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
501 OS file reads, 3733 OS file writes, 1948 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 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 591553, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 13235364
Log flushed up to   13235364
Pages flushed up to 13235364
Last checkpoint at  13235364
0 pending log writes, 0 pending chkp writes
1045 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 305856512; in additional pool allocated 0
Dictionary memory allocated 113054
Buffer pool size   18240
Free buffers       17691
Database pages     547
Old database pages 209
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 450, created 97, written 2355
0.00 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: 547, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread id 1404, state: sleeping
Number of rows inserted 298, updated 336, deleted 9, read 136560
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

To my limited knowledge, I don't see any pending transactions. If I stop\restart the MySql service, I can run the statement, but this is the 3rd time such an error has occurred and I would like to get to the bottom of it so I can learn what is going wrong, if that's possible. Is this a known issue or should I just restart the service every time this happens and move on?

share|improve this question
    
Is there anything in the MySQL error log? What does SHOW FULL PROCESSLIST; say the thread is doing before the error occurs? –  Michael - sqlbot Aug 18 '13 at 18:37
    
nothing in the error log since startup. SHOW FULL PROCESSLIST just says 173|david|localhost:54978|office|Sleep|1 –  dangel Aug 24 '13 at 20:44
    
It doesn't add up that a thread that is currently blocking on an ALTER TABLE would be sleeping. Can you catch it after issuing the alter but before the "Lock wait timeout?" Also, restarting the server to "fix" should pretty much off the table as an option, ever -- there's almost no reason other than a static global config change or serious corruption to make a restart the right thing. Something else is going on here. –  Michael - sqlbot Aug 24 '13 at 22:25
    
the "lock wait timeout" is pretty much instantaneous, not sure how catch it between the alter and error message. Did another alter this morning to add a column and all was fine, but yesterday had to restart the service again. Thanks for following up though, appreciate it. –  dangel Aug 25 '13 at 19:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.