• Guest, before posting your code please take these rules into consideration:
    • It is required to use our BBCode feature to display your code. While within the editor click < / > or >_ and place your code within the BB Code prompt. This helps others with finding a solution by making it easier to read and easier to copy.
    • Don't share a wall of code. All we want is the problem area, the code related to your issue.


    To learn more about how to use our BBCode feature, please click here.

    Thank you, Code Forum.

Python Dealing with 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

simong1993

Active Coder
Staff Team
Guardian
Guys and girls i am pulling my hair out. I know this is because you have two querys needing the same info and they time each other out.

Is there a way of checking if a database is in use then waiting for it or if they come across a lock cancel what its doing and try again.

Thanks all :D
 

HadASpook

Coder
Hello there,

I'm not experienced with databases or networking, therefore, I cannot necessarily help you with this, unless there is some code or error messages I can examine.

I'll look further into it though when I have the chance and if you feel it's necessary, explain further - there may be something I didn't catch.

Sorry.
 

Ghost

Active Coder
Run your queries and then run this code separately :
SHOW ENGINE INNODB STATUS

You can also try to show tables where it's "in use" > 0, which will show you table locks.

Basically you are running a transaction, which is fine, but table locking is causing your table to be inaccessible (most likely to something that executes after another query is executed as part of the transaction) ..

Can you show us your original code? We need to figure out what is actually locking the table in the first place and why it is taking so long as well - or if it's not taking long at all, there's an issue with the amount of time allowed to wait for a lock to drop off.
You can try to increase the wait time and see what happens, but that all depends on how fast it runs now I suppose.
 

nncyjones1345

New Coder
Error
Transaction is aborted and we need to restart the transaction. Some other transaction is blocking the transaction.
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Solution
We have two solutions for this:
1. Increase the database parameter value “innodb_lock_wait_timeout”.
2. Check the transaction which caused blocking and kill that session.

Increase the database parameter value
1. Check the database parameter value.

mysql> show variables like 'innodb_lock_wait_timeout';

+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.02 sec)



2. Increase the value of parameter.

-- Set at database level, effect seen during making new connection.
SET GLOBAL innodb_lock_wait_timeout = 100;

-- Set at session level
SET innodb_lock_wait_timeout = 100;

Check the transaction which caused blocking and kill that session
 

simong1993

Active Coder
Staff Team
Guardian
Done both of them, the strange thing was when i checked i was getting the lock but nothing was on the Main Database as a connection, it appears my PI4b was my issue. I have now moved to a 8GB 4 Core external server and i do get the lock from time to time but its usually when i over do it, i also streamlined my scripts a bit better and i think the better internet is helping too :D
 

Ghost

Active Coder
Done both of them, the strange thing was when i checked i was getting the lock but nothing was on the Main Database as a connection, it appears my PI4b was my issue. I have now moved to a 8GB 4 Core external server and i do get the lock from time to time but its usually when i over do it, i also streamlined my scripts a bit better and i think the better internet is helping too :D
Well, updating the specs is great, but it may end up having similar issues if you scale up. It really depends on your future plans.
I always like to think that there's always 2 options (usually to pitch to a client) - Quick, easier fix that doesn't cost as much money (but may have issues scaling up), OR another solution that takes time upfront to implement, but is easy to scale up later. I always try to convince the clients of the latter, but that is my business of creating software / apps... For most smaller projects there is no need to go all out with a solution that is only really needed if there are much larger datasets and users connecting. You should be A okay for a while
 

simong1993

Active Coder
Staff Team
Guardian
I will admit, i would rather solve the issue then pay more but i cant seem to find the issue :( I think my best bet is to change from mysql.connect to something that can handle the errors and deal with them, this is the current path i am looking at :)
 

Top