lock wait timeout on updating concept index

classic Classic list List threaded Threaded
1 message Options
Jonathan Galingan Jonathan Galingan
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

lock wait timeout on updating concept index

Hi, I tried updating the concept index upon uploading ICD-10 codes into the database. 
The concept index always fails to update. Upon checking the log files I always see this:

Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

When the exception occurred, I checked the innodb status and it showed this:

 InnoDB |      | 
=====================================
120515  3:00:04 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 30 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5846, signal count 5840
Mutex spin waits 0, rounds 23464, OS waits 247
RW-shared spins 11004, OS waits 5346; RW-excl spins 216, OS waits 176
------------
TRANSACTIONS
------------
Trx id counter 0 254683
Purge done for trx's n:o < 0 254410 undo n:o < 0 0
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 254682, not started, process no 9090, OS thread id 140277043943168
MySQL thread id 296, query id 250108 localhost 127.0.0.1 openmrs_user
---TRANSACTION 0 0, not started, process no 9090, OS thread id 140277042738944
MySQL thread id 295, query id 250160 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 254405, ACTIVE 1733 sec, process no 9090, OS thread id 140277042939648
448 lock struct(s), heap size 79856, 30655 row lock(s), undo log entries 19830
MySQL thread id 287, query id 250159 localhost 127.0.0.1 openmrs_user
Trx read view will not see trx with id >= 0 254406, sees < 0 254395
--------
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
25449 OS file reads, 28436 OS file writes, 9161 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 4.87 writes/s, 1.27 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
98 inserts, 98 merged recs, 46 merges
Hash table size 17393, node heap has 20 buffer(s)
25.67 hash searches/s, 32.73 non-hash searches/s
---
LOG
---
Log sequence number 3 1065488646
Log flushed up to   3 1065487755
Last checkpoint at  3 1065480138
0 pending log writes, 0 pending chkp writes
6633 log i/o's done, 1.07 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 22441784; in additional pool allocated 1048576
Dictionary memory allocated 1309280
Buffer pool size   512
Free buffers       1
Database pages     487
Modified db pages  50
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 55885, created 209, written 26066
0.00 reads/s, 0.03 creates/s, 5.40 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 9090, id 140277057033984, state: sleeping
Number of rows inserted 26370, updated 139, deleted 6567, read 37220456
5.63 inserts/s, 0.00 updates/s, 0.00 deletes/s, 3.20 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT


Does anybody know a workaround on this?

--
Jonathan D. Galingan, MD
Project Manager for Computerization
Philippine General Hospital


[hidden email] from OpenMRS Implementers' mailing list
Loading...