共計 11444 個字符，預計需要花費 29 分鐘才能閱讀完成。

行業資訊    
數據庫    
MySQL 中 Lock Tables 和 Unlock Tables 的作用是什么

這期內容當中丸趣 TV 小編將會給大家帶來有關 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”這樣的錯誤。

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

mysql  select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 61 | +-----------------+ 1 row in set (0.00 sec) mysql  show open tables where in_use   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   +----------+-------+--------+-------------+ | 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，但是不能修改表，如果執行 DML 操作的話，則會一直處于被阻塞狀態(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

但是在其它會話當中，你可以看到表 test2 可以被插入。當然前提是表的存儲引擎不能是 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) 呢?。關于這些疑問，其它會話也能讀此表，其它表也能鎖定該表(LOCK TABLES READ LOCAL)，但是不能 LOCK TABLE  WRITE。

對于 MyISAM 表，現在用的比較少，我們還是用 InnoDB 類型的表來實驗一下，在其中一個會話使用 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   +----------+-------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+-------+--------+-------------+ | MyDB | test | 1 | 0 |

然后在會話 62 中進行下面測試：

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   +----------+-------+--------+-------------+ | 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   +----------+-------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+-------+--------+-------------+ | MyDB | test | 1 | 0 | +----------+-------+--------+-------------+ 1 row in set (0.00 sec) mysql  lock tables test write;

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

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

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

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

那么我們再來看看 WRITE 鎖吧。測試前，先在上面兩個會話中執行 unlock  tables 命令。然后獲得表 TEST 上的一個 WRITE 鎖，如下所示，當前會話可以讀寫表 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   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，都會被阻塞，當然也無法獲取表 TEST 的 READ 鎖或 WRITE 鎖。也就是說當一個會話獲得一個表上的一個 WRITE 鎖后，那么只有持鎖的會話才能 READ 或 WRITE 表，其他會話都會被阻止。

mysql  unlock tables; Query OK, 0 rows affected (0.00 sec) mysql  mysql  mysql  show open tables where in_use   +----------+-------+--------+-------------+ | 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 釋放被當前會話持有的任何鎖，但是當會話發出另外一個 LOCK  TABLES 時，或當服務器的連接被關閉時，當前會話鎖定的所有表會隱式被解鎖。下面我們也可以測試看看

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

那么我們如何在當前會話鎖定多個表呢? 如下所示：

mysql  show open tables where in_use   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   +----------+---------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+---------+--------+-------------+ | MyDB | worklog | 1 | 0 | | MyDB | test | 1 | 0 | +----------+---------+--------+-------------+ 2 rows in set (0.00 sec) mysql

另外，還有一些細節問題，LOCK TABLES 是否可以為視圖、觸發器、臨時表加鎖呢?

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   +----------+-------+--------+-------------+ | 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 中使用的所有基表加鎖。對觸發器使用 LOCK  TABLE，那么就會鎖定觸發器中所包含的全部表(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   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 權限和 LOCK  TABLES 權限。LOCK TABLES 語句為當前會話顯式的獲取表鎖。最后，關于 LOCK TABLES 與事務當中鎖有那些異同，可以參考官方文檔：

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.

middot;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:

上述就是丸趣 TV 小編為大家分享的 MySQL 中 Lock Tables 和 Unlock Tables 的作用是什么了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注丸趣 TV 行業資訊頻道。