Tutorial :MySQLdb Handle Row Lock



Question:

I'm using MySQLdb and when I perform an UPDATE to a table row I sometimes get an infinite process hang.

At first I thought, maybe its COMMIT since the table is Innodb, but even with autocommit(True) and db.commit() after each update I still get the hang.

Is it possible there is a row lock and the query just fails to carry out? Is there a way to handle potential row locks or maybe even handle slow queries?


Solution:1

Depending on your user privileges, you can execute SHOW PROCESSLIST or SELECT from information_schema.processlist while the UPDATE hangs to see if there is a contention issue with another query. Also do an EXPLAIN on a SELECT of the WHERE clause used in the UPDATE to see if you need to change the statement.

If it's a lock contention, then you should eventually encounter a Lock Wait Timeout (default = 50 sec, I believe). Otherwise, if you have timing constraints, you can make use of KILL QUERY and KILL CONNECTION to unblock the cursor execution.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »