CODEWITHSUNDEEP
mysql
Сергей Соколов
Сергей Соколов

Reputation: 375

Fixing "Lock wait timeout exceeded; try restarting transaction" using single query

I have multiple users querying multiple databases in my app. When one of them receives an error "Lock wait timeout exceeded; try restarting transaction" I can do something LIKE this:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE `USER`='trial_505813';

then

KILL 25685;

But can I do something like:

KILL (SELECT `ID` FROM INFORMATION_SCHEMA.PROCESSLIST WHERE `USER`='trial_505813')

to kill all processes from specified user with one query?

Upvotes: 1

Views: 517

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562911

MariaDB has a KILL USER <name> command: https://mariadb.com/kb/en/kill/

MySQL does not implement this feature. You would have to fetch all the process id's for that user and write a loop to kill them one by one.

Example in Python:

import mysql.connector

cnx = mysql.connector.connect(user='root', password='xxxx', database='test')
cursor = cnx.cursor()

cursor.execute("SELECT id FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = %s", ["trial_505813"])
result = cursor.fetchall()
for row in result:
    cursor.execute(f"KILL {row[0]}")

I have to comment that killing queries or threads because of a lock wait timeout seems like a harsh solution. It's likely that you could optimize queries so they don't hold locks too long.

If the query is already optimized, you could be letting a transaction remain uncommitted too long. A transaction holds locks until the transaction is finished. So you need to finish transactions (COMMIT or ROLLBACK) promptly after the query is done.

Upvotes: 1

Related Questions