How to fix ERROR 1100: Table was not locked with LOCK TABLES in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 22, 2025· 232 views

This error occurs when a query references a table that was not included in a preceding LOCK TABLES statement. Fix it by adding all required tables to your lock statement or by using UNLOCK TABLES to release locks and switch to transactions.

Quick fix

Fixes ERROR 1100: Table was not locked with LOCK TABLES

-- For example, this query accesses users AND orders tables:
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
  1. Identify which tables your query accesses
  2. Lock all required tables with correct lock types
  3. Handle table aliases correctly
  4. For mysqldump restore errors, use alternative flags
mysql — error
-- For example, this query accesses users AND orders tables:
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
ERROR 1100: Table was not locked with LOCK TABLES

What this error means

MySQL enforces strict table locking rules: once you issue LOCK TABLES to lock specific tables, your session can only access those locked tables. Any attempt to query, update, insert, or delete on a table that was not locked triggers error 1100. This is particularly common when using explicit table locks with MyISAM tables or when restoring database dumps that contain incomplete lock statements. The error prevents accidental queries on the wrong tables and maintains data integrity during locked operations.

Typical symptoms

  • Queries fail with "ERROR 1100 (HY000): Table was not locked with LOCK TABLES"
  • mysqldump restore fails at a specific line with error 1100
  • Database import operations halt with lock-related errors
  • Procedures or triggers that touch multiple tables fail unexpectedly
  • UNION queries on locked tables raise error 1100

Common causes

  1. Missing tables in LOCK TABLES statement: The most common cause—a query references a table that was forgotten in the lock list
  2. Table aliases not locked separately: Using aliases without locking both the original table and each alias
  3. mysqldump restore issues: Dump files with incomplete LOCK TABLES statements, especially during version migrations
  4. Procedures accessing unlocked tables: Stored procedures or triggers called after LOCK TABLES that touch additional tables
  5. UNION queries without proper aliasing: Using UNION on a locked table without creating and locking aliases

How to fix "ERROR 1100: Table was not locked with LOCK TABLES"

1Identify which tables your query accesses

Review your SQL statement and note every table it references, including tables in JOINs, subqueries, and procedure calls.

sql
-- For example, this query accesses users AND orders tables:
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
2Lock all required tables with correct lock types

Use LOCK TABLES to lock all tables from step 1. Use WRITE for tables you modify, READ for read-only access.

sql
-- Lock all tables before querying them:
LOCK TABLES users WRITE, orders READ;
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
UNLOCK TABLES;
3Handle table aliases correctly

If your query uses the same table multiple times with different aliases, lock each alias separately.

sql
-- Lock the original table AND each alias:
LOCK TABLES products WRITE, products AS p1 READ;
INSERT INTO products SELECT * FROM products AS p1 WHERE id > 100;
UNLOCK TABLES;
4For mysqldump restore errors, use alternative flags

If error 1100 occurs during database import, recreate the dump without locking or use single-transaction mode for InnoDB tables.

bash
# Create dump without locks (if all tables are InnoDB):
mysqldump --single-transaction --quick my_database > backup.sql

# Or skip locks entirely:
mysqldump --skip-lock-tables my_database > backup.sql

# Restore the dump:
mysql my_database < backup.sql
5Consider using transactions instead of explicit locks

For most modern applications, InnoDB transactions are safer and more flexible than explicit LOCK TABLES. Replace explicit locks with transactions.

sql
-- Instead of LOCK TABLES, use transactions:
START TRANSACTION;
UPDATE users SET status = "active" WHERE id = 1;
UPDATE accounts SET balance = balance - 50 WHERE user_id = 1;
COMMIT;
6Ensure ALTER TABLE operations are re-locked if needed

ALTER TABLE on a locked table can unlock it. If you need multiple ALTER operations, re-lock after each one.

sql
LOCK TABLES my_table WRITE;
ALTER TABLE my_table ADD COLUMN new_col INT;
UNLOCK TABLES;

-- Re-lock before the next ALTER:
LOCK TABLES my_table WRITE;
ALTER TABLE my_table ADD INDEX idx_new_col (new_col);
UNLOCK TABLES;

Advanced notes

Understanding MySQL locking semantics is critical for avoiding error 1100. LOCK TABLES is primarily useful for MyISAM tables or when you need advisory locks across multiple statements. Modern applications should prefer InnoDB transactions with row-level locking, which provide better concurrency and automatic deadlock detection. If you must use explicit locks, keep them as short as possible to minimize contention. When importing large databases, always verify that the dump was created with appropriate lock flags—mysqldump by default includes LOCK TABLES statements for consistency, but these can fail if table structure changes between MySQL versions. For complex procedures involving multiple tables, consider breaking the logic into smaller, focused transactions rather than holding a large LOCK TABLES for extended periods.

Frequently asked questions

What does "ERROR 1100: Table was not locked with LOCK TABLES" mean?
MySQL enforces strict table locking rules: once you issue LOCK TABLES to lock specific tables, your session can only access those locked tables. Any attempt to query, update, insert, or delete on a table that was not locked triggers error 1100. This is particularly common when using explicit table locks with MyISAM tables or when restoring database dumps that contain incomplete lock statements.
How do I fix "ERROR 1100: Table was not locked with LOCK TABLES"?
This error occurs when a query references a table that was not included in a preceding LOCK TABLES statement. Fix it by adding all required tables to your lock statement or by using UNLOCK TABLES to release locks and switch to transactions.
What causes "ERROR 1100: Table was not locked with LOCK TABLES"?
Missing tables in LOCK TABLES statement: The most common cause—a query references a table that was forgotten in the lock list Table aliases not locked separately: Using aliases without locking both the original table and each alias mysqldump restore issues: Dump files with incomplete LOCK TABLES statements, especially during version migrations
Is the "ERROR 1100: Table was not locked with LOCK TABLES" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading