MySQL :: MySQL 9.0 參考手冊 :: 15.3.6 LOCK TABLES 和 UNLOCK TABLES 陳述式

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / LOCK TABLES 和 UNLOCK TABLES 陳述式

15.3.6 LOCK TABLES 和 UNLOCK TABLES 陳述式

LOCK {TABLE | TABLES}
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | WRITE
}

UNLOCK {TABLE | TABLES}

MySQL 允許用戶端工作階段明確取得資料表鎖定，以便與其他工作階段協同存取資料表，或防止其他工作階段在某個工作階段需要獨佔存取權時修改資料表。一個工作階段只能為自己取得或釋放鎖定。一個工作階段無法為另一個工作階段取得鎖定，或釋放另一個工作階段持有的鎖定。

鎖定可以用於模擬交易，或在更新資料表時獲得更快的速度。這在資料表鎖定的限制和條件中有更詳細的解釋。

LOCK TABLES 會為目前用戶端工作階段明確取得資料表鎖定。資料表鎖定可以針對基本資料表或檢視表取得。您必須擁有 LOCK TABLES 權限，以及針對要鎖定的每個物件擁有 SELECT 權限。

對於檢視表鎖定，LOCK TABLES 會將檢視表中使用的所有基本資料表加入要鎖定的資料表集合中，並自動鎖定它們。對於任何要鎖定的檢視表下的資料表，LOCK TABLES 會檢查檢視表定義者（對於 SQL SECURITY DEFINER 檢視表）或呼叫者（對於所有檢視表）是否在資料表上擁有適當的權限。

如果您使用 LOCK TABLES 明確鎖定資料表，則觸發程序中使用的任何資料表也會隱式鎖定，如LOCK TABLES 和觸發程序中所述。

如果您使用 LOCK TABLES 明確鎖定資料表，則透過外鍵約束相關的任何資料表也會隱式開啟和鎖定。對於外鍵檢查，會在相關資料表上取得共享唯讀鎖定 (LOCK TABLES READ)。對於串聯更新，會在操作中涉及的相關資料表上取得共享的無寫入鎖定 (LOCK TABLES WRITE)。

UNLOCK TABLES 會明確釋放目前工作階段持有的任何資料表鎖定。LOCK TABLES 會在取得新鎖定之前，隱式釋放目前工作階段持有的任何資料表鎖定。

UNLOCK TABLES 的另一個用途是釋放使用 FLUSH TABLES WITH READ LOCK 陳述式取得的全域唯讀鎖定，這可讓您鎖定所有資料庫中的所有資料表。請參閱第 15.7.8.3 節「FLUSH 陳述式」。（如果您擁有像 Veritas 這樣可以進行時間點快照的檔案系統，這是一個非常方便的備份方式。）

LOCK TABLE 是 LOCK TABLES 的同義詞；UNLOCK TABLE 是 UNLOCK TABLES 的同義詞。

資料表鎖定僅保護其他工作階段的不當讀取或寫入。持有 WRITE 鎖定的工作階段可以執行資料表層級的操作，例如 DROP TABLE 或 TRUNCATE TABLE。對於持有 READ 鎖定的工作階段，不允許執行 DROP TABLE 和 TRUNCATE TABLE 操作。

以下討論僅適用於非 TEMPORARY 資料表。對於 TEMPORARY 資料表，允許（但會被忽略）LOCK TABLES。無論其他鎖定可能的效果如何，建立資料表的工作階段都可以自由存取資料表。沒有必要鎖定，因為沒有其他工作階段可以看到該資料表。

資料表鎖定取得

若要在目前工作階段中取得資料表鎖定，請使用 LOCK TABLES 陳述式，這會取得中繼資料鎖定（請參閱第 10.11.4 節「中繼資料鎖定」）。

可以使用以下鎖定類型

READ [LOCAL] 鎖定

持有鎖定的工作階段可以讀取資料表（但無法寫入）。
多個工作階段可以同時取得資料表的 READ 鎖定。
其他工作階段可以讀取資料表，而無需明確取得 READ 鎖定。
LOCAL 修飾符允許其他工作階段在持有鎖定時執行不衝突的 INSERT 陳述式（並行插入）。（請參閱第 10.11.3 節「並行插入」）。但是，如果您在持有鎖定的同時要使用伺服器外部的程序來操作資料庫，則不能使用 READ LOCAL。對於 InnoDB 資料表，READ LOCAL 與 READ 相同。

WRITE 鎖定

持有鎖定的工作階段可以讀取和寫入資料表。
只有持有鎖定的工作階段才能存取資料表。在鎖定釋放之前，沒有其他工作階段可以存取它。
在持有 WRITE 鎖定時，其他工作階段對資料表的鎖定請求會遭到封鎖。

WRITE 鎖定通常比 READ 鎖定具有更高的優先權，以確保盡快處理更新。這表示如果一個工作階段取得 READ 鎖定，然後另一個工作階段請求 WRITE 鎖定，則後續的 READ 鎖定請求會等待，直到請求 WRITE 鎖定的工作階段取得鎖定並釋放它。（對於 max_write_lock_count 系統變數的小值，可能會發生此原則的例外情況；請參閱第 10.11.4 節「中繼資料鎖定」。）

如果由於其他工作階段在任何資料表上持有鎖定，導致 LOCK TABLES 陳述式必須等待，則它會遭到封鎖，直到可以取得所有鎖定。

需要鎖定的工作階段必須在單一 LOCK TABLES 陳述式中取得它需要的所有鎖定。在持有這樣取得的鎖定時，工作階段只能存取鎖定的資料表。例如，在下列陳述式序列中，嘗試存取 t2 會發生錯誤，因為它在 LOCK 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

INFORMATION_SCHEMA 資料庫中的資料表是例外。即使工作階段持有使用 LOCK TABLES 取得的資料表鎖定，也可以在未明確鎖定的情況下存取它們。

您無法在單一查詢中使用相同的名稱多次參照鎖定的資料表。請改用別名，並為資料表和每個別名取得個別鎖定

mysql> LOCK TABLE t WRITE, t AS t1 READ;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

第一個 INSERT 會發生錯誤，因為鎖定的資料表有兩個相同的名稱參考。第二個 INSERT 成功，因為資料表的參考使用了不同的名稱。

如果您的陳述式使用別名來參照資料表，則必須使用相同的別名來鎖定資料表。不指定別名鎖定資料表是無效的

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

相反地，如果您使用別名鎖定資料表，則必須在陳述式中使用該別名來參照它

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

資料表鎖定釋放

當工作階段持有的資料表鎖定被釋放時，它們會全部同時釋放。工作階段可以明確釋放其鎖定，或者在某些情況下，鎖定可能會隱式釋放。

工作階段可以使用 UNLOCK TABLES 明確釋放其鎖定。
如果工作階段發出 LOCK TABLES 陳述式以取得鎖定，同時已經持有鎖定，則在授予新鎖定之前，會隱式釋放其現有的鎖定。
如果工作階段開始交易（例如，使用 START TRANSACTION），則會執行隱式的 UNLOCK TABLES，這會導致釋放現有的鎖定。（如需資料表鎖定和交易之間互動的相關資訊，請參閱資料表鎖定和交易的互動。）

如果用戶端工作階段的連線正常或異常終止，伺服器會隱式釋放工作階段持有的所有資料表鎖定（交易性和非交易性）。如果用戶端重新連線，則鎖定不再有效。此外，如果用戶端有作用中的交易，伺服器會在斷線時回復交易，如果發生重新連線，則新工作階段會啟用自動提交。因此，用戶端可能會想要停用自動重新連線。如果啟用自動重新連線，則在發生重新連線時，用戶端不會收到通知，但任何資料表鎖定或目前的交易都會遺失。如果停用自動重新連線，則在連線中斷時，下一個發出的陳述式會發生錯誤。用戶端可以偵測到錯誤，並採取適當的動作，例如重新取得鎖定或重做交易。請參閱自動重新連線控制。

注意

如果您在鎖定的資料表上使用 ALTER TABLE，它可能會變成未鎖定。例如，如果您嘗試第二次 ALTER TABLE 操作，結果可能是 Table 'tbl_name' was not locked with LOCK TABLES 錯誤。若要處理此問題，請在第二次變更之前再次鎖定資料表。另請參閱第 B.3.6.1 節「ALTER TABLE 的問題」。

資料表鎖定與交易的互動

LOCK TABLES 和 UNLOCK TABLES 與交易的使用互動如下：

LOCK TABLES 不是交易安全的，並且會在嘗試鎖定資料表之前隱式地提交任何活動交易。
UNLOCK TABLES 會隱式地提交任何活動交易，但前提是必須已使用 LOCK TABLES 來取得資料表鎖定。例如，在以下陳述式集中，UNLOCK TABLES 會釋放全域讀取鎖定，但不會提交交易，因為沒有生效的資料表鎖定。
```
FLUSH TABLES WITH READ LOCK;
START TRANSACTION;
SELECT ... ;
UNLOCK TABLES;
```
開始交易（例如使用 START TRANSACTION）會隱式地提交任何目前的交易並釋放現有的資料表鎖定。
FLUSH TABLES WITH READ LOCK 取得的是全域讀取鎖定，而不是資料表鎖定，因此它不會像 LOCK TABLES 和 UNLOCK TABLES 那樣受限於資料表鎖定和隱式提交。例如，START TRANSACTION 不會釋放全域讀取鎖定。請參閱第 15.7.8.3 節，「FLUSH 陳述式」。
其他隱式導致交易提交的陳述式不會釋放現有的資料表鎖定。如需此類陳述式的清單，請參閱第 15.3.3 節，「導致隱式提交的陳述式」。
在事務型資料表（例如 InnoDB 資料表）中使用 LOCK TABLES 和 UNLOCK TABLES 的正確方法是使用 SET autocommit = 0（而不是 START TRANSACTION）開始交易，然後使用 LOCK TABLES，並且在您明確提交交易之前不要呼叫 UNLOCK TABLES。例如，如果您需要寫入資料表 t1 並從資料表 t2 讀取，您可以這樣做：
```
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;
```
當您呼叫 LOCK TABLES 時，InnoDB 會在內部取得自己的資料表鎖定，而 MySQL 會取得自己的資料表鎖定。InnoDB 會在下一次提交時釋放其內部資料表鎖定，但是要讓 MySQL 釋放其資料表鎖定，您必須呼叫 UNLOCK TABLES。您不應該有 autocommit = 1，因為這樣 InnoDB 會在呼叫 LOCK TABLES 後立即釋放其內部資料表鎖定，而且很容易發生死鎖。InnoDB 在 autocommit = 1 的情況下根本不會取得內部資料表鎖定，以協助舊版應用程式避免不必要的死鎖。
ROLLBACK 不會釋放資料表鎖定。

LOCK TABLES 與觸發程序

如果您使用 LOCK TABLES 明確鎖定資料表，則觸發程序中使用的任何資料表也會被隱式鎖定。

鎖定與使用 LOCK TABLES 陳述式明確取得的鎖定同時取得。
觸發程序中使用的資料表上的鎖定取決於該資料表是否僅用於讀取。如果僅用於讀取，則讀取鎖定就足夠了。否則，將使用寫入鎖定。
如果使用 LOCK TABLES 明確鎖定資料表進行讀取，但由於可能會在觸發程序中修改資料表而需要鎖定進行寫入，則會取得寫入鎖定而不是讀取鎖定。（也就是說，由於資料表出現在觸發程序中而需要的隱式寫入鎖定，會導致對該資料表發出的明確讀取鎖定請求轉換為寫入鎖定請求。）

假設您使用此陳述式鎖定兩個資料表 t1 和 t2：

LOCK TABLES t1 WRITE, t2 READ;

如果 t1 或 t2 有任何觸發程序，則觸發程序中使用的資料表也會被鎖定。假設 t1 有如下定義的觸發程序：

CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW
BEGIN
  UPDATE t4 SET count = count+1
      WHERE id = NEW.id AND EXISTS (SELECT a FROM t3);
  INSERT INTO t2 VALUES(1, 2);
END;

LOCK TABLES 陳述式的結果是，t1 和 t2 因為它們出現在陳述式中而被鎖定，而 t3 和 t4 因為它們在觸發程序中使用而被鎖定。

t1 會根據 WRITE 鎖定請求被鎖定以進行寫入。
t2 會被鎖定以進行寫入，即使請求是 READ 鎖定。發生這種情況的原因是 t2 會在觸發程序中插入，因此 READ 請求會轉換為 WRITE 請求。
t3 會被鎖定以進行讀取，因為它僅在觸發程序中讀取。
t4 會被鎖定以進行寫入，因為它可能會在觸發程序中更新。

資料表鎖定的限制和條件

您可以安全地使用 KILL 來終止正在等待資料表鎖定的工作階段。請參閱第 15.7.8.4 節，「KILL 陳述式」。

LOCK TABLES 和 UNLOCK TABLES 不能在預存程式中使用。

performance_schema 資料庫中的資料表無法使用 LOCK TABLES 鎖定，但 setup_xxx 資料表除外。

LOCK TABLES 產生的鎖定範圍是單一 MySQL 伺服器。它與 NDB Cluster 不相容，NDB Cluster 沒有辦法在多個 mysqld 執行個體之間強制執行 SQL 層級的鎖定。您可以改為在 API 應用程式中強制執行鎖定。如需更多資訊，請參閱第 25.2.7.10 節，「與多個 NDB Cluster 節點相關的限制」。

當 LOCK TABLES 陳述式生效時，會禁止使用以下陳述式：CREATE TABLE、CREATE TABLE ... LIKE、CREATE VIEW、DROP VIEW，以及預存函式和程序與事件上的 DDL 陳述式。

對於某些操作，必須存取 mysql 資料庫中的系統資料表。例如，HELP 陳述式需要伺服器端說明資料表的內容，而 CONVERT_TZ() 可能需要讀取時區資料表。伺服器會視需要隱式地鎖定系統資料表進行讀取，因此您不需要明確地鎖定它們。這些資料表的處理方式如上所述。

mysql.help_category
mysql.help_keyword
mysql.help_relation
mysql.help_topic
mysql.time_zone
mysql.time_zone_leap_second
mysql.time_zone_name
mysql.time_zone_transition
mysql.time_zone_transition_type

如果您想使用 LOCK TABLES 陳述式在其中任何資料表上明確放置 WRITE 鎖定，則該資料表必須是唯一鎖定的資料表；不得使用同一陳述式鎖定其他資料表。

通常，您不需要鎖定資料表，因為所有單一 UPDATE 陳述式都是不可分割的；沒有其他工作階段可以干擾任何其他目前正在執行的 SQL 陳述式。但是，在某些情況下，鎖定資料表可能會提供優勢：

如果您要在一組 MyISAM 資料表上執行許多操作，則鎖定您要使用的資料表會快得多。鎖定 MyISAM 資料表會加快在這些資料表上插入、更新或刪除的速度，因為 MySQL 不會在呼叫 UNLOCK TABLES 之前清除鎖定資料表的索引快取。通常，索引快取會在每個 SQL 陳述式之後清除。
鎖定資料表的缺點是，沒有任何工作階段可以更新 READ 鎖定的資料表（包括持有鎖定的工作階段），也沒有任何工作階段可以存取 WRITE 鎖定的資料表，持有鎖定的工作階段除外。
如果您將資料表用於非事務性儲存引擎，則如果要確保在 SELECT 和 UPDATE 之間沒有其他工作階段修改資料表，則必須使用 LOCK TABLES。此處顯示的範例需要 LOCK TABLES 才能安全執行：
```
LOCK TABLES trans READ, customer WRITE;
SELECT SUM(value) FROM trans WHERE customer_id=some_id;
UPDATE customer
  SET total_value=sum_from_previous_statement
  WHERE customer_id=some_id;
UNLOCK TABLES;
```
如果沒有 LOCK TABLES，則在執行 SELECT 和 UPDATE 陳述式之間，另一個工作階段可能會在 trans 資料表中插入新列。

在許多情況下，您可以透過使用相對更新（UPDATE customer SET value=value+new_value）或 LAST_INSERT_ID() 函數來避免使用 LOCK TABLES。

在某些情況下，您也可以使用使用者層級的諮詢鎖定函數 GET_LOCK() 和 RELEASE_LOCK() 來避免鎖定表格。這些鎖定儲存在伺服器中的雜湊表中，並使用 pthread_mutex_lock() 和 pthread_mutex_unlock() 來實現，以達到高速。請參閱第 14.14 節，「鎖定函數」。

如需鎖定原則的詳細資訊，請參閱第 10.11.1 節，「內部鎖定方法」。