CODEWITHSUNDEEP
mysql
Kieran Headley
Kieran Headley

Reputation: 993

MySQL - Table 'my_table' was not locked with Lock Tables

I try and load tables via MySQL and get the following error?

MySQL said: Table 'cms' was not locked with LOCK TABLES

Why does the table need to be Locked? I haven't seen this before? is there any way to unlock? do you even want to?

Upvotes: 63

Views: 129822

Answers (9)

violetflare
violetflare

Reputation: 93

I managed to import the database with the --force option.

mysql -p database < /home/x/new_dump.sql --force

Although I had to run it twice because the first time it didn't import all tables after throwing some errors.

Upvotes: 0

MrYutz
MrYutz

Reputation: 438

I had what appears a forked process in my stored procedure. It was causing the error:

#ERROR 1100: Table 'cached_sales_data' was not locked with LOCK TABLES

on the following SQL Statements:

LOCK TABLES cached_sales_data WRITE, v_sales_data_2 READ;
call refresh_sales_data_now ();
UNLOCK TABLES;

It was in fact being caused by a where clause sub-query in the refresh_sales_data_now() stored procedure.

...
where
  orderdatetime > (select max(orderdatetime)
from
  cached_sales_data)

According to the docs, I would need to create a second READ lock with an alias during the initial lock, then I can write and use the alias for the secondary select.

LOCK TABLES cached_sales_data WRITE, cached_sales_data as csd READ, v_sales_data_2 READ;

...
where
  orderdatetime > (select max(orderdatetime)
from
  csd)

Upvotes: 0

sashoalm
sashoalm

Reputation: 79457

The solution for me was to unlock the tables. They had been locked by a previous query which failed before reaching the unlock tables statement.

UNLOCK TABLES
SELECT ...

Upvotes: 56

Stalinko
Stalinko

Reputation: 3646

In my case the problem was the aliases.

From the docs:

If your statements refer to a table by means of an alias, you must lock the table using that same alias. It does not work to lock the table without specifying the alias.

Conversely, if you lock a table using an alias, you must refer to it in your statements using that alias.

Wrong:

LOCK TABLE my_table READ;
SELECT * FROM my_table t;
#ERROR 1100: Table 't' was not locked with LOCK TABLES

Correct:

LOCK TABLE my_table t READ;
SELECT * FROM my_table t;

Upvotes: 8

Arya
Arya

Reputation: 734

In my case, this error occurred because I was attempting to import data from Windows to Linux: Windows is case-insensitive and had all lowercase table names, but Linux is case-sensitive and had the same table names, but with uppercase letters. Once I changed the case of the source table names to match the destination table names, this error no longer occurred. The following SO post explains the case sensitivity issue between Windows and Linux in regards to mysql: Are table names in MySQL case sensitive?

Upvotes: 0

Yu Jiaao
Yu Jiaao

Reputation: 4714

If in one session, you locked one table but want to select from another table, you must either lock that table too or unlock all tables.

mysql> LOCK TABLES t1 READ;
mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
mysql> SELECT COUNT(*) FROM t2;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

Upvotes: 94

Cosworth66
Cosworth66

Reputation: 637

One of the most important lines in the MySQL docs relating to the "Table 'my_table' was not locked with LOCK TABLES" message is as follows:

"While the locks thus obtained are held, the session can access only the locked tables" https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

This means that if you are trying to access any other table in the database while the LOCK is in place you will get the error message "Table 'my_table' was not locked with LOCK TABLES"

The fix is to apply the lock to all of the tables you want to have access to during the lock like this. "LOCK TABLES table_1 WRITE, table_2 WRITE"

Where table_1 is the one you really want to lock but you also want to access table_2 during the same process.

This was confusing because I was locking only table_1 but the error message was telling me Table 'table_2' was not locked with LOCK TABLES

Took me a while to figure out why table_2 was even involved. I hope that this helps someone else with the same issue.

Upvotes: 20

Cloud
Cloud

Reputation: 3219

I encountered this problem:

LOCK TABLE <table_a> READ;
LOCK TABLE <table_b> READ;
LOCK TABLE <table_a> WRITE;
LOCK TABLE <table_b> WRITE;

then I read from , this raises Table 'table_a' was not locked with Lock Tables.

After reading documentation, I fix the lock code to :

LOCK TABLE <table_a> WRITE, <table_b> WRITE

This solve the problem for me.

lock type

READ Read lock, no writes allowed

WRITE Exclusive write lock. No other connections can read or write to this table

Upvotes: 2

Naruto
Naruto

Reputation: 4329

http://dev.mysql.com/doc/refman/5.7/en/lock-tables.html

MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

Locks may be used to emulate transactions or to get more speed when updating tables. This is explained in more detail later in this section.

LOCK TABLES explicitly acquires table locks for the current client session. Table locks can be acquired for base tables or views. You must have the LOCK TABLES privilege, and the SELECT privilege for each object to be locked.

For view locking, LOCK TABLES adds all base tables used in the view to the set of tables to be locked and locks them automatically. If you lock a table explicitly with LOCK TABLES, any tables used in triggers are also locked implicitly, as described in Section 13.3.5.2, “LOCK TABLES and Triggers”.

UNLOCK TABLES explicitly releases any table locks held by the current session. LOCK TABLES implicitly releases any table locks held by the current session before acquiring new locks.

Another use for UNLOCK TABLES is to release the global read lock acquired with the FLUSH TABLES WITH READ LOCK statement, which enables you to lock all tables in all databases. See Section 13.7.6.3, “FLUSH Syntax”. (This is a very convenient way to get backups if you have a file system such as Veritas that can take snapshots in time.)

Syntax for LOCK and UNLOCK

 LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE

Eg:-

LOCK TABLE t WRITE, t AS t1 READ;

Unlock tables

 UNLOCK TABLES

Upvotes: 31

Related Questions