📋

Description

This error indicates that a requested `ALTER TABLE` operation cannot be performed using an in-place algorithm and instead requires a full table copy. This copy operation necessitates acquiring an exclusive lock on the table, which the MariaDB server is currently unable to obtain.

💬

Error Message

COPY algorithm requires a lock

🔍

Known Causes

3 known causes

⚠️

Incompatible ALTER TABLE Operation

Certain `ALTER TABLE` statements cannot be executed using an `INPLACE` algorithm and inherently require a full table `COPY` operation. This `COPY` process demands an exclusive lock on the table.

⚠️

Concurrent Table Access or Existing Locks

The presence of active transactions, open connections, or existing locks on the target table prevents the server from acquiring the necessary exclusive lock for the `COPY` algorithm to proceed.

⚠️

Explicit `LOCK=NONE` or `ALGORITHM=INPLACE` Failure

You may have explicitly specified `LOCK=NONE` or `ALGORITHM=INPLACE` in your `ALTER TABLE` statement, but the nature of the alteration makes it impossible for the server to comply without a full table copy and an exclusive lock.

🛠️

Solutions

4 solutions available

1. Retry the Operation easy

The error is often transient and a simple retry can resolve it.

1

Wait for a few moments and re-execute the command that triggered the error (e.g., `ALTER TABLE ... ALGORITHM=COPY`).

2. Increase Lock Timeout medium

Extend the time MariaDB waits for locks to be released, allowing the COPY algorithm to proceed.

1

Connect to your MariaDB server using a client like `mariadb` or `mysql`.

2

Set the `lock_wait_timeout` system variable to a higher value. A common starting point is 600 seconds (10 minutes). This is a session-level setting, so it only affects the current connection.

SET SESSION lock_wait_timeout = 600;

3

Re-execute the operation that caused the error within the same session.

4

(Optional) To make this change permanent for all new connections, you can set `lock_wait_timeout` in the MariaDB configuration file (`my.cnf` or `my.ini`). Restart the MariaDB server for this change to take effect.

[mariadb]
lock_wait_timeout = 600

3. Use INPLACE Algorithm medium

Switch to the INPLACE algorithm for table modifications, which generally requires fewer and shorter-lived locks.

1

When performing operations like `ALTER TABLE`, explicitly specify `ALGORITHM=INPLACE` instead of `ALGORITHM=COPY`.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name VARCHAR(255) ALGORITHM=INPLACE;

2

If `INPLACE` is not supported for your specific operation (e.g., certain column type changes), MariaDB will automatically fall back to `COPY` or `MERGE`. You can check the documentation for your specific MariaDB version and operation.

4. Reduce Concurrent Activity medium

Minimize other database operations that might be holding locks on the target table or related resources.

1

Identify and pause or defer any long-running or lock-intensive operations (e.g., large `SELECT` queries, other `ALTER TABLE` statements, bulk data loads) that are running concurrently.

2

Monitor the `SHOW PROCESSLIST;` output in MariaDB to identify potential lock contention.

SHOW PROCESSLIST;

3

Once activity is reduced, re-attempt the operation that was failing.

🔗

Related Errors

5 related errors

MariaDB Error 1847: COPY algorithm needs lock