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?

Answer 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.