How to Unlock Locked MySQL Tables Without Restarting the Server?

Resolving MySQL Table Locks Without a Server Restart

The error "Lock wait timeout exceeded; try restarting transaction" is often misinterpreted as requiring a server restart. However, this lock issue can occur even without explicit transactions. Let's examine the root cause and offer a solution to release the affected tables.

The error indicates a lock preventing a MySQL UPDATE statement. Even without initiating a transaction, MySQL might retain locks due to implicit transactions or background processes.

To resolve this, a direct (though potentially risky) method is to forcefully unlock the table:

1. MySQL Login:

<code class="language-sql">mysql -u your_user -p</code>

2. Identify Locked Tables:

<code class="language-sql">mysql> show open tables where in_use > 0;</code>

This displays currently locked tables.

3. Examine Active Processes:

<code class="language-sql">mysql> show processlist;</code>

This shows active processes. Identify the process(es) locking the target table(s).

4. Terminate the Process:

<code class="language-sql">mysql> kill <process_id>;</code>

Replace <process_id> with the ID of the locking process.

This forcefully unlocks the tables, enabling the UPDATE statement. Crucially, this method carries a risk of data corruption and should only be a temporary fix while addressing the underlying application problem.

The above is the detailed content of How to Unlock Locked MySQL Tables Without Restarting the Server?. For more information, please follow other related articles on the PHP Chinese website!