MySQL - 表“my_table”没有被锁定表锁定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36467298/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - Table 'my_table' was not locked with Lock Tables
提问by Kieran Headley
I try and load tables via MySQL and get the following error?
我尝试通过 MySQL 加载表并收到以下错误？
MySQL said: Table 'cms' was not locked with LOCK TABLES
MySQL 说：表 'cms' 没有被 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?
为什么需要锁定表？我以前没见过这个？有什么办法可以解锁吗？你甚至想要吗？
采纳答案by Naruto
http://dev.mysql.com/doc/refman/5.7/en/lock-tables.html
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.)
MySQL 使客户端会话能够明确获取表锁，以便与其他会话合作访问表，或者在会话需要独占访问表时防止其他会话修改表。会话只能为自己获取或释放锁。一个会话不能为另一个会话获取锁或释放另一个会话持有的锁。
锁可用于模拟事务或在更新表时获得更高的速度。本节稍后将对此进行更详细的解释。
LOCK TABLES 显式获取当前客户端会话的表锁。可以为基表或视图获取表锁。您必须拥有 LOCK TABLES 权限，以及每个要锁定的对象的 SELECT 权限。
对于视图锁定，LOCK TABLES 将视图中使用的所有基表添加到要锁定的表集中并自动锁定它们。如果您使用 LOCK TABLES 显式锁定表，则触发器中使用的任何表也会被隐式锁定，如第 13.3.5.2 节，“LOCK TABLES 和触发器”中所述。
UNLOCK TABLES 显式释放当前会话持有的任何表锁。LOCK TABLES 在获取新锁之前隐式释放当前会话持有的任何表锁。
UNLOCK TABLES 的另一个用途是释放使用 FLUSH TABLES WITH READ LOCK 语句获取的全局读锁，这使您能够锁定所有数据库中的所有表。见第 13.7.6.3 节，“FLUSH 语法”。（如果您的文件系统（例如 Veritas）可以及时拍摄快照，这是一种非常方便的获取备份的方法。）
Syntax for LOCK and UNLOCK
LOCK 和 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
回答by Yu Jiaao
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
回答by sashoalm
The solution for me was to unlock the tables. They had been locked by a previous queries which failed before reaching the unlock tablesstatement.
我的解决方案是解锁桌子。它们已被先前的查询锁定，该查询在到达unlock tables语句之前失败。
UNLOCK TABLES
SELECT ...
回答by Cosworth66
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:
MySQL 文档中与“Table 'my_table' was not locked with LOCK TABLES”消息相关的最重要的一行如下：
"While the locks thus obtained are held, the session can access onlythe locked tables" https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html
“虽然这样得到的持有锁，会话可以访问只锁定表” 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"
这意味着，如果您在 LOCK 就位时尝试访问数据库中的任何其他表，您将收到错误消息“表 'my_table' 未使用 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"
解决方法是将锁定应用于您希望在锁定期间访问的所有表，如下所示。“锁定表 table_1 写入，table_2 写入”
Where table_1 is the one you really want to lock but you also want to access table_2 during the same process.
其中 table_1 是您真正想要锁定但您还想在同一过程中访问 table_2 的那个。
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
这很令人困惑，因为我只锁定了 table_1，但错误消息告诉我 Table 'table_2' is 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.
我花了一段时间才弄清楚为什么甚至涉及 table_2。我希望这可以帮助其他有同样问题的人。
回答by Simin Jie
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.
然后我从，这引发了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.
这为我解决了问题。
READ Read lock, no writes allowed
WRITE Exclusive write lock. No other connections can read or write to this table
READ 读锁，不允许写
WRITE 独占写锁。没有其他连接可以读取或写入此表
回答by Arya
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?
就我而言，发生此错误是因为我试图将数据从 Windows 导入 Linux：Windows 不区分大小写，表名全部小写，但 Linux 区分大小写，表名相同，但使用大写字母。一旦我更改了源表名称的大小写以匹配目标表名称，就不再发生此错误。以下 SO 帖子解释了 Windows 和 Linux 之间关于 mysql 的区分大小写问题：MySQL 中的表名是否区分大小写？
