MySQL Error 1165: ER_UNUSED3 - How to Fix and Prevent

What is MySQL Error 1165 (ER_UNUSED3)?

MySQL error 1165 appears with the message “INSERT DELAYED can't be used with table '%s' because it is locked with LOCK TABLES.” The server rejects the statement because the DELAYED queue cannot interact safely with an explicitly locked table.

The problem surfaces in write-heavy workloads that mix INSERT DELAYED for performance with manual LOCK TABLES for consistency.

Resolving it immediately restores normal insert throughput and avoids application stalls.

What Causes This Error?

Using INSERT DELAYED on any table that is under a WRITE or READ lock triggers the error. MySQL’s delayed-insert handler refuses to queue rows while another session holds the lock.

Mixing high-volume logging tables, bulk maintenance jobs, or backup scripts that call LOCK TABLES commonly produces the conflict.

Replication lag can amplify the impact if the error repeats in a loop.

How to Fix MySQL Error 1165

First, release the explicit lock with UNLOCK TABLES, then rerun the insert without the DELAYED modifier. If the workload requires queuing, replace INSERT DELAYED with INSERT LOW_PRIORITY or use a dedicated message queue.

Review application code or stored procedures to ensure they do not issue LOCK TABLES before performing delayed inserts.

Adjust the locking strategy or remove the DELAYED keyword to eliminate the race.

Common Scenarios and Solutions

Bulk data loads that lock several tables for referential integrity should switch to standard INSERT statements or load data in smaller batches. This keeps the lock time short and avoids the delayed-insert restriction.

Log collector services often use INSERT DELAYED.

Disable LOCK TABLES in maintenance scripts that rotate or truncate the log table, or schedule them during off-hours when no delayed inserts run.

Best Practices to Avoid This Error

Avoid combining INSERT DELAYED with manual table locks in the same schema. Use transaction-level locking with START TRANSACTION instead of LOCK TABLES when possible.

Galaxy’s context-aware SQL editor highlights LOCK TABLES and INSERT DELAYED in the same script, warning users before execution.

Running code reviews inside Galaxy Collections prevents the pattern from entering production.

Related Errors and Solutions

Error 1166 (ER_UNUSED4) arises when DELETE DELAYED meets LOCK TABLES, solved by the same unlock-or-remove approach. Error 1205 (Lock wait timeout) involves row-level locks and is fixed by tuning innodb_lock_wait_timeout.

.