I am inserting a data into one of my tables, and I keep getting a lock.
insert into inventory_files(id, pro_id) values(30,6569);
I get the following error.
Error Code: 1205. Lock wait timeout exceeded; try restarting transaction 51.004 sec
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
Then inserting that data.
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
I have no issues inserting into other tables. Any other suggestions.
I also ran.
SHOW ENGINE INNODB STATUS;
No tables where locked.
'InnoDB', '', '
2017-08-04 13:57:34 0x7f0c44461700 INNODB MONITOR OUTPUT Per second averages calculated from the last 29 seconds
BACKGROUND THREAD
srv_master_thread loops: 93 srv_active, 0 srv_shutdown, 2985 srv_idle
srv_master_thread log flush and writes: 3076
SEMAPHORES
OS WAIT ARRAY INFO: reservation count 29
OS WAIT ARRAY INFO: signal count 29
RW-shared spins 0, rounds 52, OS waits 26
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 52.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
TRANSACTIONS
Trx id counter 2147575956\n Purge done for trx's n:o < 2147575378 undo n:o < 0 state: running but idle
History list length 91
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421165916588792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421165916587872, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2147575922, ACTIVE 104 sec
6 lock struct(s), heap size 1136, 52 row lock(s), undo log entries 5
MySQL thread id 32, OS thread handle 139690660574976, query id 43020 localhost 127.0.0.1 my_web_user
---TRANSACTION 2147575860, ACTIVE 211 sec
6 lock struct(s), heap size 1136, 52 row lock(s), undo log entries 5 MySQL thread id 30, OS thread handle 139690661377792, query id 40076 localhost 127.0.0.1 my_web_user
---TRANSACTION 2147575771, ACTIVE 413 sec 6 lock struct(s), heap size 1136, 52 row lock(s), undo log entries 5 MySQL thread id 23, OS thread handle 139690660976384, query id 35541 localhost 127.0.0.1 my_web_user
---TRANSACTION 2147575640, ACTIVE 540 sec 6 lock struct(s), heap size 1136, 52 row lock(s), undo log entries 28 MySQL thread id 16, OS thread handle 139690661177088, query id 29555 localhost 127.0.0.1 my_web_user
---TRANSACTION 2147575372, ACTIVE 990 sec 6 lock struct(s), heap size 1136, 52 row lock(s), undo log entries 28 MySQL thread id 11, OS thread handle 139690661578496, query id 4137 localhost 127.0.0.1 my_web_user
Trx read view will not see trx with id >= 2147575373, sees < 2147575373 FILE I/O I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I am assuming the my_web_user is locking the row. How do you unlock the row?
SHOW ENGINE INNODB STATUS;is actually a good place to look for it, so look again, you probably just didn't find it. Or add the output to your question, and we might be able to help you find it. - Solarflare------- TRX HAS BEEN WAITING 20 SEC FOR THIS LOCK TO BE GRANTED:. If you do not know when it happens, and if you cannot restart the transaction to repeat the situation (or it is resolved and you do not get the lock error again), you could e.g. save the status every 50 seconds (maybe get it in a cronjob and log it), so you can look at it after it happened. - Solarflare