EC
1220
MySQLERRORNotableLockingHIGH confidence

Table was locked with READ lock and cannot be updated

Production Risk

Low — write is blocked; fix the lock type.

What this means

ER_TABLE_NOT_LOCKED_FOR_WRITE (1220, SQLSTATE HY000) is raised when a session tries to perform a write operation (INSERT, UPDATE, DELETE) on a table that the same session has locked with a READ lock via LOCK TABLES.

Why it happens
  1. 1Using LOCK TABLES t1 READ and then attempting to write to t1 in the same session
  2. 2Logic error in application that acquires read locks but expects to write
How to reproduce
trigger — this will error
trigger — this will error
LOCK TABLES orders READ;
UPDATE orders SET status = 'done' WHERE id = 1;  -- ERROR 1220

expected output

ERROR 1220 (HY000): Table 'orders' was locked with a READ lock and can't be updated

Fix

Lock the table with WRITE lock

Lock the table with WRITE lock
UNLOCK TABLES;
LOCK TABLES orders WRITE;
UPDATE orders SET status = 'done' WHERE id = 1;
UNLOCK TABLES;

Why this works

A WRITE lock allows the holding session to read and write the table.

Sources
Official documentation ↗

MySQL 8.0 — 1220 ER_TABLE_NOT_LOCKED_FOR_WRITE

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors