Error
Error Code:
3193
MySQL Error 3193: Table Referenced by Another Connection
Description
This error occurs when you attempt to perform an operation (like ALTER TABLE, DROP TABLE, or TRUNCATE TABLE) on a table, but MySQL cannot complete it because another active connection or transaction is currently referencing or holding a lock on that table. It indicates a concurrency conflict where an exclusive lock is required but cannot be acquired.
Error Message
Cannot complete the operation because table is referenced by another connection.
Known Causes
4 known causesActive Transactions
Another client connection has an active, uncommitted transaction that involves the table, holding a lock that prevents your operation.
Uncommitted Changes
A session has made `INSERT`, `UPDATE`, or `DELETE` statements on the table but has not yet committed or rolled back these changes.
Open Idle Connections
One or more database connections are open and idle, but are still implicitly referencing the table, preventing exclusive modification.
Application Connection Pools
An application's connection pool might be holding connections that reference the table, even if the application is not actively using them.
Solutions
3 solutions available1. Identify and Terminate Blocking Connections medium
Find and kill the specific connections holding locks on the table.
1
Connect to your MySQL server with a user that has sufficient privileges (e.g., root).
2
Query the `information_schema.processlist` table to identify active connections and their associated queries. Look for queries that are actively using the table you are trying to modify. Pay attention to the `Id` column for the connection ID and the `Info` column for the query being executed.
SELECT Id, User, Host, Db, Command, Time, State, Info FROM information_schema.processlist WHERE Info IS NOT NULL AND Info LIKE '%your_table_name%';3
Once you have identified the connection(s) that are blocking your operation, use the `KILL` command to terminate them. Replace `connection_id` with the actual ID found in the previous step.
KILL connection_id;4
After terminating the blocking connections, retry your original operation.
2. Wait for Transaction Completion or Lock Release easy
Allow existing transactions to finish naturally or release their locks.
1
Understand that this error often occurs when another connection is in the middle of a long-running transaction or has acquired an exclusive lock on the table. The simplest solution is often to wait for that operation to complete.
2
If possible, communicate with other users or applications that might be performing operations on the table to inform them of potential delays or to ask them to complete their tasks.
3
Retry your operation after a reasonable waiting period. The duration will depend on the typical workload and transaction times of your application.
3. Review Application Logic for Long-Running Queries or Transactions advanced
Proactively address application code that might cause this issue.
1
Analyze your application's code to identify any queries or transactions that might be holding locks for extended periods. This is particularly important for operations that modify schema or large datasets.
2
Consider implementing shorter transactions or breaking down large operations into smaller, more manageable steps. This reduces the window of opportunity for lock contention.
3
Implement proper error handling and retry mechanisms within your application. If a `3193` error occurs, your application should be able to gracefully handle it, potentially by retrying the operation after a short delay or informing the user.
4
If using InnoDB, ensure that your `innodb_lock_wait_timeout` and `innodb_rollback_on_timeout` settings are configured appropriately to manage lock waits and potential deadlocks.