MySQL Forums :: InnoDB :: Lock wait timeout exceeded; try restarting transaction


Advanced Search

Lock wait timeout exceeded; try restarting transaction
Posted by: Vector Thorn ()
Date: June 04, 2010 10:07PM

Ok, so i've got this database that, around the same time, two days in a row now, has locked tables (rows?) and needed restarted. Until it is restarted, any updates and creates are getting the error above (subject line). The tables are small (about 200 to 400 rows). tx_isolation=REPEATABLE-READ. innodb_lock_wait_timeout=50. During the last lockout, i ran "show innodb status" and got this:

=====================================
100604 17:24:56 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 52 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1195, signal count 1175
Mutex spin waits 0, rounds 23731, OS waits 917
RW-shared spins 516, OS waits 257; RW-excl spins 3, OS waits 0
------------
TRANSACTIONS
------------
Trx id counter 0 437912
Purge done for trx's n:o < 0 437687 undo n:o < 0 0
History list length 8
Total number of lock structs in row lock hash table 27
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 27479, OS thread id 1103251776
MySQL thread id 4925, query id 38911452 localhost giveoppc
show innodb status
---TRANSACTION 0 437911, ACTIVE 4 sec, process no 27479, OS thread id 1103518016 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368
MySQL thread id 4923, query id 38817205 localhost giveoppc_giveopp Updating
update UAMS_User set verified= NAME_CONST('sp_vote',1) where eid= NAME_CONST('sp_user',12756879170027931) limit 1
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 226 n bits 128 index `PRIMARY` of table `giveoppc_GiveOpp/UAMS_User` trx id 0 437911 lock_mode X locks rec but not gap waiting
Record lock, heap no 28 PHYSICAL RECORD: n_fields 47; compact format; info bits 0
0: len 8; hex 002d5250635f419b; asc  -RPc_A ;; 1: len 6; hex 00000006ad30; asc      0;; 2: len 7; hex 000000002d0454; asc     - T;; 3: len 30; hex 376134333938326664363334643633363466633837353535616235313965; asc 7a43982fd634d6364fc87555ab519e;...(truncated); 4: len 8; hex 002d2f2733bf460a; asc  -/'3 F ;; 5: len 1; hex 00; asc  ;; 6: len 1; hex 00; asc  ;; 7: len 1; hex 01; asc  ;; 8: len 1; hex 00; asc  ;; 9: len 1; hex 00; asc  ;; 10: len 1; hex 00; asc  ;; 11: len 1; hex 00; asc  ;; 12: len 1; hex 00; asc  ;; 13: len 1; hex 00; asc  ;; 14: len 1; hex 00; asc  ;; 15: SQL NULL; 16: SQL NULL; 17: SQL NULL; 18: len 12; hex 302020202020202020202020; asc 0           ;; 19: len 4; hex 30202020; asc 0   ;; 20: len 20; hex 636c6567676f313640626967706f6e642e636f6d; asc cleggo16@bigpond.com;; 21: len 30; hex 663035383634656633363465333263333333646364613663343932643733; asc f05864ef364e32c333dcda6c492d73;...(truncated); 22: len 11; hex 5768617420697320322b32; asc What is 2+2;; 23: len 1; hex 34; asc 4;; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27: len 8; hex 0000000000000000; asc         ;; 28: len 3; hex 5a5a5a; asc ZZZ;; 29: SQL NULL; 30: len 24; hex 6e6f2d70686f746f2e706e67202020202020202020202020; asc no-photo.png            ;; 31: len 11; hex 4372616967204c6567676f; asc Craig Leggo;; 32: len 8; hex 0000000000000000; asc         ;; 33: SQL NULL; 34: SQL NULL; 35: SQL NULL; 36: SQL NULL; 37: SQL NULL; 38: len 1; hex 7b; asc {;; 39: len 4; hex 7cb0b8b5; asc |   ;; 40: len 15; hex 70726f6a656374626f6e6f2e636f6d; asc projectbono.com;; 41: SQL NULL; 42: len 0; hex ; asc ;; 43: len 0; hex ; asc ;; 44: len 0; hex ; asc ;; 45: len 0; hex ; asc ;; 46: len 0; hex ; asc ;;

------------------
---TRANSACTION 0 437690, ACTIVE 1582 sec, process no 27479, OS thread id 1102559552 fetching rows, thread declared inside InnoDB 169
mysql tables in use 15, locked 15
30 lock struct(s), heap size 6752, undo log entries 1
MySQL thread id 4831, query id 38911450 localhost giveoppc_giveopp Sending data
insert into T2 select eid,ctime from UAMS_User where pid in (select eid from T1) and eid>666 and status>9
Trx read view will not see trx with id >= 0 437691, sees < 0 437691
--------
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
100 OS file reads, 1791 OS file writes, 1184 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 inserts, 0 merged recs, 0 merges
Hash table size 17393, used cells 1349, node heap has 3 buffer(s)
18863.06 hash searches/s, 36668.66 non-hash searches/s
---
LOG
---
Log sequence number 0 18099062
Log flushed up to   0 18099062
Last checkpoint at  0 18099062
0 pending log writes, 0 pending chkp writes
631 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 21569338; in additional pool allocated 1048576
Buffer pool size   512
Free buffers       373
Database pages     136
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 111, created 25, written 999
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 27479, id 1167264064, state: sleeping
Number of rows inserted 1077, updated 1183, deleted 16, read 395733713
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 238408.24 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
I've googled this for a while now, and even read this thread here:
http://forums.mysql.com/read.php?25,163152,163152

But i'm not sure that these apply entirely to me, as we do not do clustering/replication, and i don't use "transactions" as in "start transaction ... commit". All stored procedures just use inserts, selects, deletes, etc, without using a "transaction". So, i am going to try increasing the innodb_lock_wait_timeout, but i would love to hear if other --more experienced-- users believe that is the problem.

Thanks!

premium domain names that i'm selling



Edited 1 time(s). Last edit at 06/05/2010 10:09AM by Vector Thorn.

Options: ReplyQuote


Subject Views Written By Posted
Lock wait timeout exceeded; try restarting transaction 9216 Vector Thorn 06/04/2010 10:07PM
Re: Lock wait timeout exceeded; try restarting transaction 3932 Vector Thorn 06/05/2010 12:43AM
Re: Lock wait timeout exceeded; try restarting transaction 2663 Rick James 06/06/2010 09:01AM
Re: Lock wait timeout exceeded; try restarting transaction 3157 Vector Thorn 06/06/2010 10:39AM
Re: Lock wait timeout exceeded; try restarting transaction 2502 Rick James 06/06/2010 08:17PM
Re: Lock wait timeout exceeded; try restarting transaction 2089 Vector Thorn 06/06/2010 08:37PM
Re: Lock wait timeout exceeded; try restarting transaction 3132 Vector Thorn 06/07/2010 05:53PM
Re: Lock wait timeout exceeded; try restarting transaction 2432 Rick James 06/08/2010 01:23AM
Re: Lock wait timeout exceeded; try restarting transaction 2707 Vector Thorn 06/08/2010 01:52AM
Re: Lock wait timeout exceeded; try restarting transaction 1748 Rick James 06/08/2010 09:02AM
Re: Lock wait timeout exceeded; try restarting transaction 2472 Vector Thorn 06/08/2010 12:55PM
Re: Lock wait timeout exceeded; try restarting transaction 1514 Rick James 06/09/2010 12:49AM
Re: Lock wait timeout exceeded; try restarting transaction 2674 Vector Thorn 06/10/2010 11:22PM
Re: Lock wait timeout exceeded; try restarting transaction 2939 Rick James 06/11/2010 09:25AM
Re: Lock wait timeout exceeded; try restarting transaction 2347 Vector Thorn 06/11/2010 04:04PM
Re: Lock wait timeout exceeded; try restarting transaction 1466 Rick James 06/11/2010 08:02PM
Re: Lock wait timeout exceeded; try restarting transaction 2482 Vector Thorn 06/15/2010 07:27PM
Re: Lock wait timeout exceeded; try restarting transaction 4678 Vector Thorn 06/15/2010 08:13PM
Re: Lock wait timeout exceeded; try restarting transaction 2004 Rick James 06/15/2010 11:02PM
Re: Lock wait timeout exceeded; try restarting transaction 1970 Rick James 06/15/2010 11:04PM
Re: Lock wait timeout exceeded; try restarting transaction 2669 Vector Thorn 06/15/2010 11:17PM


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.