[[384586]]

本文轉(zhuǎn)載自微信公眾號「DBA閑思雜想錄」，作者瀟湘隱者。轉(zhuǎn)載本文請聯(lián)系DBA閑思雜想錄公眾號。  

在MySQL中提供了鎖定表(lock tables)和解鎖表(unlock tables)的語法功能，ORACLE與SQL Server數(shù)據(jù)庫當(dāng)中沒有這種語法。相信剛接觸MySQL的人，都想詳細(xì)、深入的了解一下這個功能.下面就盡量全面的解析、總結(jié)一下MySQL中l(wèi)ock tables與unlock tables的功能，如有不足或不正確的地方，歡迎指點(diǎn)一二。

鎖定表的語法：

LOCK TABLES 
tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} 
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ... 

LOCAL修飾符表示可以允許在其他會話中對在當(dāng)前會話中獲取了READ鎖的的表執(zhí)行插入。但是當(dāng)保持鎖時(shí)，若使用Server外的會話來操縱數(shù)據(jù)庫則不能使用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以后，這個修飾符已經(jīng)被棄用。如果使用它則會產(chǎn)生警告。

[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為當(dāng)前會話鎖定表。UNLOCK TABLES釋放被當(dāng)前會話持有的任何鎖。官方文檔“13.3.5 LOCK TABLES and UNLOCK TABLES Syntax”已經(jīng)對LOCK TALES與UNLOCK TABLES做了不少介紹，下面我們通過一些測試?yán)觼砩钊氲睦斫庖幌骆i表與解鎖表的相關(guān)知識點(diǎn)。我們先準(zhǔn)備一下測試環(huán)境用的表和數(shù)據(jù)。

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>  

當(dāng)前會話(會話ID為61)持有test表的READ鎖后，那么當(dāng)前會話只可以讀該表，而不能往表中寫入數(shù)據(jù)，否則就會報(bào)“Table 'test' was locked with a READ lock and can't be updated”這樣的錯誤。

注意：如果使用LOCK TABLE WRITE鎖定表后，則可以更新數(shù)據(jù)。詳見后面介紹

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>  

其它會話也能查詢表test，但是不能修改表，如果執(zhí)行DML操作的話，則會一直處于被阻塞狀態(tài)(Waiting for table metadata lock)。

另外，我們測試一下修飾符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(); 
+-----------------+ 
| connection_id() | 
+-----------------+ 
|              66 | 
+-----------------+ 
1 row in set (0.00 sec) 
 
mysql> lock tables test2 read local; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select * from test2; 
+------+------+ 
| id   | name | 
+------+------+ 
| 1001 | test | 
+------+------+ 
1 row in set (0.00 sec) 
 
mysql> insert into test2 
    -> select 1002, 'kkk'; 
ERROR 1099 (HY000): Table 'test2' was locked with a READ lock and can't be updated 
mysql>  

但是在其它會話當(dāng)中，你可以看到表test2可以被插入。當(dāng)然前提是表的存儲引擎不能是innodb引擎，否則使用修飾符LOCAL和不用LOCAL是一樣的，其它會話無法對表寫入。

mysql> select connection_id(); 
+-----------------+ 
| connection_id() | 
+-----------------+ 
|              65 | 
+-----------------+ 
1 row in set (0.00 sec) 
 
mysql> select * from test2; 
+------+------+ 
| id   | name | 
+------+------+ 
| 1001 | test | 
+------+------+ 
1 row in set (0.00 sec) 
 
mysql> insert into test2 
    -> select 1002, 'kkk'; 
Query OK, 1 row affected (0.00 sec) 
Records: 1  Duplicates: 0  Warnings: 0 

那么其他會話是否也能讀此表呢? 其它會話能否也能鎖定該表(LOCK TABLES READ LOCAL)?其它會話是否也能鎖定寫(LOCK TABLE WRITE)呢?。關(guān)于這些疑問，其它會話也能讀此表，其它表也能鎖定該表(LOCK TABLES READ LOCAL)，但是不能LOCK TABLE WRITE。

對于MyISAM表，現(xiàn)在用的比較少，我們還是用InnoDB類型的表來實(shí)驗(yàn)一下，在其中一個會話使用lock table鎖定表test，

mysql> select connection_id(); 
+-----------------+ 
| connection_id() | 
+-----------------+ 
|              61 | 
+-----------------+ 
1 row in set (0.00 sec) 
 
mysql> lock table 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 | 

然后在會話62中進(jìn)行下面測試：

mysql> select connection_id(); 
+-----------------+ 
| connection_id() | 
+-----------------+ 
|              62 | 
+-----------------+ 
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> 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  |      2 |           0 | 
+----------+-------+--------+-------------+ 
1 row in set (0.00 sec) 
 
mysql> unlock tables; 
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.00 sec) 
 
mysql> lock tables test write; 

如上測試所示，如果一個會話在一個表上獲得一個READ鎖后，所有其他會話只能從表中讀。不能往表中寫，其它會話也可在該表上獲取一個READ鎖，此時(shí)你會在show open tables里面看到in_use的值增加。其實(shí)LOCK TABLES READ是一個表鎖，而且是共享鎖。但是當(dāng)一個會話獲取一個表上的READ鎖后，其它會話就不能獲取該表的WRITE鎖了，此時(shí)就會被阻塞，直到持有READ鎖的會話釋放READ鎖。

該會話(會話61)中則可以繼續(xù)獲取WRITE鎖。當(dāng)該會話獲取WRITE鎖后，其它會話則無法獲取READ鎖了

mysql> lock table test write; 
Query OK, 0 rows affected (0.00 sec) 

另外需要注意的是，當(dāng)前會話如果鎖定了其中一個表，那么是無法查詢其它表的。否則會報(bào)“ERROR 1100 (HY000): Table 'worklog' was not locked with LOCK TABLES”錯誤。

那么我們再來看看WRITE鎖吧。測試前，先在上面兩個會話中執(zhí)行 unlock tables命令。然后獲得表TEST上的一個WRITE鎖，如下所示，當(dāng)前會話可以讀寫表TEST

mysql> unlock tables; 
Query OK, 0 rows affected (0.00 sec) 
 
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 write; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select * from test; 
+-------+---------+ 
| id    | name    | 
+-------+---------+ 
| 10001 | kerry   | 
| 10002 | richard | 
| 10003 | jimmy   | 
+-------+---------+ 
3 rows in set (0.00 sec) 
 
mysql> update test set name='ken' where id=10003; 
Query OK, 1 row affected (0.01 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 
 
mysql>  

其它會話無法讀寫表TEST，都會被阻塞，當(dāng)然也無法獲取表TEST的READ鎖或WRITE鎖。也就是說當(dāng)一個會話獲得一個表上的一個WRITE鎖后，那么只有持鎖的會話才能READ或WRITE表，其他會話都會被阻止。

mysql> unlock tables; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql>  
mysql>  
mysql> show open tables where in_use >=1; 
+----------+-------+--------+-------------+ 
| Database | Table | In_use | Name_locked | 
+----------+-------+--------+-------------+ 
| MyDB     | test  |      1 |           0 | 
+----------+-------+--------+-------------+ 
1 row in set (0.00 sec) 
 
mysql> select * from test; 

mysql> select connection_id(); 
+-----------------+ 
| connection_id() | 
+-----------------+ 
|              63 | 
+-----------------+ 
1 row in set (0.00 sec) 
 
mysql> show processlist; 
+----+------+-----------+------+---------+------+---------------------------------+--------------------+ 
| Id | User | Host      | db   | Command | Time | State                           | Info               | 
+----+------+-----------+------+---------+------+---------------------------------+--------------------+ 
| 61 | root | localhost | MyDB | Sleep   |   86 |                                 | NULL               | 
| 62 | root | localhost | MyDB | Query   |   40 | Waiting for table metadata lock | select * from test | 
| 63 | root | localhost | MyDB | Query   |    0 | init                            | show processlist   | 
| 64 | root | localhost | MyDB | Sleep   | 2551 |                                 | NULL               | 
+----+------+-----------+------+---------+------+---------------------------------+--------------------+ 
4 rows in set (0.00 sec) 

UNLOCK TABLES釋放被當(dāng)前會話持有的任何鎖，但是當(dāng)會話發(fā)出另外一個LOCK TABLES時(shí)，或當(dāng)服務(wù)器的連接被關(guān)閉時(shí)，當(dāng)前會話鎖定的所有表會隱式被解鎖。下面我們也可以測試看看

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.00 sec) 
 
mysql> lock tables worklog read; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> show open tables where in_use >=1; 
+----------+---------+--------+-------------+ 
| Database | Table   | In_use | Name_locked | 
+----------+---------+--------+-------------+ 
| MyDB     | worklog |      1 |           0 | 
+----------+---------+--------+-------------+ 
1 row in set (0.00 sec) 
 
mysql>  

那么我們?nèi)绾卧诋?dāng)前會話鎖定多個表呢?如下所示：

mysql> show open tables where in_use >=1; 
Empty set (0.00 sec) 
 
mysql> lock tables test read, worklog read; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> show open tables where in_use >=1; 
+----------+---------+--------+-------------+ 
| Database | Table   | In_use | Name_locked | 
+----------+---------+--------+-------------+ 
| MyDB     | worklog |      1 |           0 | 
| MyDB     | test    |      1 |           0 | 
+----------+---------+--------+-------------+ 
2 rows in set (0.00 sec) 
 
mysql>  

另外，還有一些細(xì)節(jié)問題，LOCK TABLES是否可以為視圖、觸發(fā)器、臨時(shí)表加鎖呢?

mysql> create table test2( id int, sex bit); 
Query OK, 0 rows affected (0.06 sec) 
 
mysql> insert into test2 
    -> select 10001, 1 union all 
    -> select 10002, 0 union all 
    -> select 10003, 1; 
Query OK, 3 rows affected (0.02 sec) 
Records: 3  Duplicates: 0  Warnings: 0 
mysql> create view v_test 
    -> as 
    -> select t1.id, t1.name, t2.sex 
    -> from test t1 left join test2 t2 on t1.id =t2.id; 
Query OK, 0 rows affected (0.01 sec) 
mysql> lock tables v_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     | test2 |      1 |           0 | 
| MyDB     | test  |      1 |           0 | 
+----------+-------+--------+-------------+ 
2 rows in set (0.00 sec) 
 
mysql>  

如上測試所示，對于VIEW加鎖，LOCK TABLES語句會為VIEW中使用的所有基表加鎖。對觸發(fā)器使用LOCK TABLE，那么就會鎖定觸發(fā)器中所包含的全部表(any tables used in triggers are also locked implicitly)

mysql> unlock tables; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create temporary table tmp like test; 
Query OK, 0 rows affected (0.04 sec) 
 
mysql> show open tables where in_use >=1; 
Empty set (0.00 sec) 
 
mysql> select database(); 
+------------+ 
| database() | 
+------------+ 
| MyDB       | 
+------------+ 
1 row in set (0.00 sec) 
 
mysql> select * from tmp; 
Empty set (0.00 sec) 
 
mysql> insert into tmp 
    -> select 1001, 'kerry' ; 
Query OK, 1 row affected (0.01 sec) 
Records: 1  Duplicates: 0  Warnings: 0 
 
mysql>  

LOCK TABLES 與 UNLOCK TABLES只能為自己獲取鎖和釋放鎖，不能為其他會話獲取鎖，也不能釋放由其他會話保持的鎖。一個對象獲取鎖，需具備該對象上的SELECT權(quán)限和LOCK TABLES權(quán)限。LOCK TABLES語句為當(dāng)前會話顯式的獲取表鎖。最后，關(guān)于LOCK TABLES與事務(wù)當(dāng)中鎖有那些異同，可以參考官方文檔：

LOCK TABLES and UNLOCK TABLES interact with the use of transactions as follows:

• LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.
• ·UNLOCK TABLES implicitly commits any active transaction, but only if LOCK TABLES has been used to acquire table locks. For example, in the following set of statements,UNLOCK TABLES releases the global read lock but does not commit the transaction because no table locks are in effect: