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.
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.
Review your SQL statement and note every table it references, including tables in JOINs, subqueries, and procedure calls.
-- For example, this query accesses users AND orders tables:
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;Use LOCK TABLES to lock all tables from step 1. Use WRITE for tables you modify, READ for read-only access.
-- 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;If your query uses the same table multiple times with different aliases, lock each alias separately.
-- 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;If error 1100 occurs during database import, recreate the dump without locking or use single-transaction mode for InnoDB tables.
# 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.sqlFor most modern applications, InnoDB transactions are safer and more flexible than explicit LOCK TABLES. Replace explicit locks with transactions.
-- 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;ALTER TABLE on a locked table can unlock it. If you need multiple ALTER operations, re-lock after each one.
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;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.
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions