CODEWITHSUNDEEP
mysql
user3525290
user3525290

Reputation: 1617

MySQL Error Code: 1205. Lock wait timeout exceeded; try restarting transaction on insert

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?

Upvotes: 4

Views: 41723

Answers (2)

Kumar
Kumar

Reputation: 41

Show full processlist; kill x;

worked for me while using long query from workbench;

Upvotes: 0

Vyas Adhikari
Vyas Adhikari

Reputation: 86

The main reason I have this error is when an existing process is running, which prevents a new process being executed on the same tables.

Try 

Show full processlist; kill x;

replace x with the process number that you think is causing the issue

Upvotes: 5

Related Questions