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.