The storage engine refused to grant a lock requested by the current session, halting the statement.
MySQL ER_LOCK_REFUSED_BY_ENGINE (error 3177) appears when the storage engine cannot give your statement the lock it needs because another transaction already owns a conflicting lock. End or kill the blocking transaction, then retry or redesign the query to use smaller lock scopes.
ER_LOCK_REFUSED_BY_ENGINE
Error 3177 signals that the storage engine, usually InnoDB, could not supply a requested metadata or row lock. MySQL cancels the statement immediately rather than waiting.
This error was introduced in MySQL 5.7.11 to surface cases where internal lock tables refuse a lock request instead of timing out.
The message appears during DDL or DML that needs a lock already held by another transaction with a conflicting mode. Typical triggers are ALTER TABLE, CREATE INDEX, or long UPDATE statements inside explicit transactions.
Unlike a deadlock, MySQL does not retry or roll back another session. It simply refuses the lock and returns error 3177.
Failed locks leave business logic incomplete and can halt migrations. Repeated failures indicate design problems in transaction boundaries or workload spikes that threaten uptime.
Resolving lock conflicts improves throughput, shortens maintenance windows, and reduces user-visible errors.
Lock refusal happens when the internal InnoDB lock table is full, or when a higher-priority system thread prevents new locks to avoid starvation. Blocking sessions that hold metadata locks too long are another root cause.
Large transactions that lock many rows or perform ALTER TABLE on hot tables often exhaust lock table space, triggering the refusal.
Identify the blocking or heavy session, end it, then rerun the statement. Shrink transaction size, add suitable indexes, and schedule DDL during low-traffic windows.
Increasing innodb_buffer_pool_size and innodb_lock_wait_timeout is not sufficient because MySQL refuses the lock instantly. You must resolve the conflict.
High-throughput OLTP systems see the error when long ALTER TABLE statements compete with heavy write loads. Put the table in ALGORITHM=INPLACE or lock=none mode to minimise conflicts.
Batch ETL jobs in READ COMMITTED isolation can collide with interactive sessions. Moving the batch to off-hours or chunking rows prevents oversize lock sets.
Keep transactions short, commit promptly, and avoid open transactions in clients that idle. Use smaller, online DDL operations where possible.
Monitor information_schema.innodb_trx and performance_schema.metadata_locks to detect long-running locks before they block others.
Error 1205 Lock wait timeout exceeded occurs when MySQL waits too long rather than refusing. Compare blocking sessions to identify root causes.
Error 1213 Deadlock found indicates circular locking rather than outright refusal. Resolve by changing statement order or adding indexes.
Sessions that keep ALTER TABLE or CREATE INDEX locks open for minutes block every other DDL and cause the storage engine to refuse new ones.
Mass DELETE, UPDATE, or INSERT operations can request millions of row locks that fill the InnoDB lock table, triggering a refusal.
If innodb_buffer_pool_size is too small for the workload, the internal lock table may lack space for additional entries.
Purge or recovery threads may temporarily hold locks that take priority over user transactions, forcing new locks to be refused.
MySQL waited innodb_lock_wait_timeout seconds but could not obtain the lock. Increase the timeout or remove the blocker.
InnoDB detected a cycle of transactions waiting on each other and rolled one back. Reorder statements or add indexes to avoid conflict.
A DDL statement tried to grab a metadata lock incompatible with an existing one. End the blocking session or use online DDL.
No. The storage engine refuses the lock immediately without the circular wait that defines a deadlock.
No. The lock is declined instantly, so the timeout setting is irrelevant.
Yes, after the conflicting session commits or ends. Automatic retry logic can mask transient conflicts.
Galaxy tracks live sessions and highlights long transactions, enabling engineers to spot and end blockers before locks are refused.
.avif)
.avif){kind=logo}
