LOCK TABLES，鎖表（或者視圖）權限。
可以鎖住那些你擁有select權限的表，以防止其他session通路或者修改這個表。
如果鎖住視圖，那麼視圖的所有基表都被鎖住。
如果鎖住的表上有觸發器，那麼和這個觸發器所有相關的表都被鎖住。
很明顯，臨時表由于其固有特性，lock table 臨時表實際是沒有意義的，雖然不報錯，但實際也被忽略的。
要鎖住某張表的前提是要擁有表上select 權限。
lock table有如下兩種表達方式：
lock table xxx read，隻讀方式鎖住xxx，該表隻能被select，不能被修改。如果在lock時，該表上存在事務，則lock語句挂起，直到事務結束。多個會話可以同時對表執行該操作。
lock table xxx write，讀寫方式鎖住xxx，lock table的會話可以對表xxx做修改及查詢等操作，而其他會話不能對該表做任何操作，包括select也要被阻塞。
可以同時鎖住多個表。
授予[email protected]%權限：
D:\temp>mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.7.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop user if exists 'ut01'@'%';
Query OK, 0 rows affected (0.09 sec)
mysql> drop user if exists 'ut02'@'%';
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> show warnings;
+-------+------+---------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------+
| Note | 3162 | User 'ut02'@'%' does not exist. |
+-------+------+---------------------------------+
1 row in set (0.00 sec)
mysql> create user 'ut01'@'%' identified by '20127163';
Query OK, 0 rows affected (0.08 sec)
mysql> show grants for 'ut01'@'%';
+----------------------------------+
| Grants for [email protected]% |
+----------------------------------+
| GRANT USAGE ON *.* TO 'ut01'@'%' |
+----------------------------------+
1 row in set (0.00 sec)
mysql> grant lock tables on test.* to 'ut01'@'%';
Query OK, 0 rows affected (0.08 sec)
mysql> show grants for 'ut01'@'%';
+---------------------------------------------+
| Grants for [email protected]% |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'ut01'@'%' |
| GRANT LOCK TABLES ON `test`.* TO 'ut01'@'%' |
+---------------------------------------------+
2 rows in set (0.00 sec)
mysql> grant select on test.* to 'ut01'@'%';
Query OK, 0 rows affected (0.05 sec)
mysql> select * from sys_menu;
+--------+--------------+-----------+
| menuId | menuParentId | menuUrlId |
+--------+--------------+-----------+
| 201 | 0 | 9001 |
| 202 | 201 | 9002 |
| 203 | 201 | 9003 |
| 204 | 202 | 9004 |
| 205 | 203 | 9005 |
| 206 | 204 | 9006 |
+--------+--------------+-----------+
6 rows in set (0.03 sec)
mysql> delete from sys_menu where menuid=201;
<hang....>
ERROR 1317 (70100): Query execution was interrupted
mysql>
mysql> select * from sys_menu;
+--------+--------------+-----------+
| menuId | menuParentId | menuUrlId |
+--------+--------------+-----------+
| 201 | 0 | 9001 |
| 202 | 201 | 9002 |
| 203 | 201 | 9003 |
| 204 | 202 | 9004 |
| 205 | 203 | 9005 |
| 206 | 204 | 9006 |
+--------+--------------+-----------+
6 rows in set (0.00 sec)
mysql>
該使用者操作：
C:\Users\Administrator>mysql -u'ut01'
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.7.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sys_menu |
| sys_role |
| sys_role_menu |
+----------------+
3 rows in set (0.00 sec)
mysql> lock table sys_menu read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from sys_menu;
+--------+--------------+-----------+
| menuId | menuParentId | menuUrlId |
+--------+--------------+-----------+
| 201 | 0 | 9001 |
| 202 | 201 | 9002 |
| 203 | 201 | 9003 |
| 204 | 202 | 9004 |
| 205 | 203 | 9005 |
| 206 | 204 | 9006 |
+--------+--------------+-----------+
6 rows in set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql>
即使該使用者有delete權限：
mysql> use test
Database changed
mysql> lock table sys_menu read;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from sys_menu where menuId=201;
ERROR 1099 (HY000): Table 'sys_menu' was locked with a READ lock and can't be updated
mysql>
使用lock table xxx read鎖住表後，包括自己session在内的所有會話都不能修改表，隻能select表。
另外，會話使用lock table方式鎖住了某表之後，那麼該會話隻能操作（包括select）那些被鎖住的表，其他表不能操作，直到unlock tables結束。
mysql> lock table sys_menu read;
Query OK, 0 rows affected (6.43 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sys_menu |
| sys_role |
| sys_role_menu |
+----------------+
3 rows in set (0.00 sec)
mysql> select * from sys_role;
ERROR 1100 (HY000): Table 'sys_role' was not locked with LOCK TABLES
mysql> delete from sys_role where 1=1;
ERROR 1100 (HY000): Table 'sys_role' was not locked with LOCK TABLES
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> lock table sys_menu write;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from sys_role;
ERROR 1100 (HY000): Table 'sys_role' was not locked with LOCK TABLES
mysql> delete from sys_role where 1=1;
ERROR 1100 (HY000): Table 'sys_role' was not locked with LOCK TABLES
mysql>
在使用mysqldump導出資料時，預設會add-lcoks，比如：
C:\Users\Administrator>mysqldump --add-locks -B test > d:\temp\test.add-locks.sql
C:\Users\Administrator>
那麼在bulk insert之前和之後有如下的lock語句：
LOCK TABLES `sys_menu` WRITE;
;
#表示暫不維護索引，待插入完畢之後，再維護索引。但是由于InnoDB表的結構，是以該語句對于innodb表然并卵。
INSERT INTO `sys_menu` VALUES (201,0,9001),(202,201,9002),(203,201,9003),(204,202,9004),(205,203,9005),(206,204,9006),(207,201,9007),(208,202,9008);
;
UNLOCK TABLES;
也可以通過如下方式禁用lock語句：
C:\Users\Administrator>mysqldump --skip-add-locks -B test > d:\temp\test.add-locks.sql
C:\Users\Administrator>
此時：
;
INSERT INTO `sys_menu` VALUES (201,0,9001),(202,201,9002),(203,201,9003),(204,202,9004),(205,203,9005),(206,204,9006),(207,201,9007),(208,202,9008);
;
lock tables的語句就沒有了。
當然，如果有某個會話使用lock tables語句鎖住了某個表，那麼mysqldump在導出該表時也會hang住。
中繼資料鎖
為了保證事務的可串行化。在一個事務中被操作（查詢，修改等）的那些表，資料庫必須保證其他會話不能修改這些表的表結構。
比如，一個會話執行了如下操作：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from sys_menu;
+--------+--------------+-----------+
| menuId | menuParentId | menuUrlId |
+--------+--------------+-----------+
| 201 | 0 | 9001 |
| 202 | 201 | 9002 |
| 203 | 201 | 9003 |
| 204 | 202 | 9004 |
| 205 | 203 | 9005 |
| 206 | 204 | 9006 |
| 207 | 201 | 9007 |
| 208 | 202 | 9008 |
+--------+--------------+-----------+
8 rows in set (5.92 sec)
mysql>
該會話顯示開啟了一個事務，并且select了一個表sys_menu。那麼資料庫會在這個表上保持“中繼資料鎖”。以防止其他會話修改這個表的表結構。
比如，另一個會話執行：
mysql> use test
Database changed
mysql> ALTER TABLE `test`.`sys_menu`
-> DROP COLUMN `menuUrlId`;
<一直hang住，直到前面的會話結束事務>
這個會話不能執行的有ddl操作，以及lock table write操作。