MySQL中lock tables和unlock tables淺析 在MySQL中提供了鎖定表（lock tables）和解鎖表（unlock tables）的語法功能，ORACLE與SQL Server資料庫當中沒有這種語法。相信剛接觸MySQL的人，都想詳細、深入的瞭解一下這個功能.下麵就儘量全... ...
MySQL中lock tables和unlock tables淺析
在MySQL中提供了鎖定表（lock tables）和解鎖表（unlock tables）的語法功能，ORACLE與SQL Server資料庫當中沒有這種語法。相信剛接觸MySQL的人，都想詳細、深入的瞭解一下這個功能.下麵就儘量全面的解析、總結一下MySQL中lock tables與unlock tables的功能，如有不足或不正確的地方，歡迎指點一二。
鎖定表的語法：
LOCK TABLES
tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
LOCAL修飾符表示可以允許在其他會話中對在當前會話中獲取了READ鎖的的表執行插入。但是當保持鎖時，若使用Server外的會話來操縱資料庫則不能使用READ LOCAL。另外，對於InnoDB表，READ LOCAL與READ相同。
The LOCAL modifier enables nonconflicting INSERT statements (concurrent inserts) by other sessions to execute while the lock is held. (See Section 8.11.3, “Concurrent Inserts”.) However, READ LOCAL cannot be used if you are going to manipulate the database using processes external to the server while you hold the lock. For InnoDB tables, READ LOCAL is the same as READ.
修飾符LOW_PRIORITY用於之前版本的MySQL，它會影響鎖定行為,但是從MySQL 5.6.5以後，這個修飾符已經被棄用。如果使用它則會產生警告。
[LOW_PRIORITY] WRITE lock:
The session that holds the lock can read and write the table.
Only the session that holds the lock can access the table. No other session can access it until the lock is released.
Lock requests for the table by other sessions block while the WRITE lock is held.
The LOW_PRIORITY modifier has no effect. In previous versions of MySQL, it affected locking behavior, but this is no longer true. As of MySQL 5.6.5, it is deprecated and its use produces a warning. Use WRITE without LOW_PRIORITY instead.
解鎖表的語法：
UNLOCK TABLES
LOCK TABLES為當前會話鎖定表。 UNLOCK TABLES釋放被當前會話持有的任何鎖。官方文檔“13.3.5 LOCK TABLES and UNLOCK TABLES Syntax”已經對LOCK TALES與UNLOCK TABLES做了不少介紹，下麵我們通過一些測試例子來深入的理解一下鎖表與解鎖表的相關知識點。我們先準備一下測試環境用的表和數據。
mysql> create table test( id int, name varchar(12));
Query OK, 0 rows affected (0.07 sec)mysql> insert into test-> select 10001, 'kerry' union all
-> select 10002, 'richard' union all
-> select 10003, 'jimmy' ;
Query OK, 3 rows affected (0.05 sec)Records: 3 Duplicates: 0 Warnings: 0
mysql>
當前會話（會話ID為61）持有test表的READ鎖後，那麼當前會話只可以讀該表，而不能往表中寫入數據，否則就會報“Table 'test' was locked with a READ lock and can't be updated”這樣的錯誤。
mysql> select connection_id();+-----------------+
| connection_id() |
+-----------------+
| 61 |
+-----------------+
1 row in set (0.00 sec)
mysql> show open tables where in_use >=1;
Empty set (0.00 sec)mysql> lock tables test read;Query OK, 0 rows affected (0.00 sec)mysql> show open tables where in_use >=1;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| MyDB | test | 1 | 0 |
+----------+-------+--------+-------------+
1 row in set (0.01 sec)
mysql> select * from test;
+-------+---------+
| id | name |
+-------+---------+
| 10001 | kerry |
| 10002 | richard |
| 10003 | jimmy |
+-------+---------+
3 rows in set (0.00 sec)
mysql> insert into test-> values(10004, 'ken');
ERROR 1099 (HY000): Table 'test' was locked with a READ lock and can't be updated
mysql>
另外，我們測試一下修飾符LOCAL的用途，如下所示：
mysql> create table test2( id int , name varchar(12)) engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)mysql> insert into test2-> select 1001, 'test';
Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0
mysql> select connection_id();
