MySQL表鎖定
在本教學中，您將學習如何使用MySQL鎖來協調對談之間的表存取。
MySQL允許用戶端對談明確獲取表鎖，以防止其他對談在特定時間段內存取表。用戶端對談只能為自己獲取或釋放表鎖。它不能獲取或釋放其他對談的表鎖。
在詳細介紹之前，我們將建立一個名為sampledb的範例資料庫，其中包含一個簡單的tbl表來模擬練習表鎖定語句。
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
CREATE TABLE tbl (
id int(11) NOT NULL AUTO_INCREMENT,
col int(11) NOT NULL,
PRIMARY KEY (id)
);
LOCK和UNLOCK TABLES語法
獲取表的鎖的簡單形式如下：
LOCK TABLES table_name [READ | WRITE]
可將表的名稱放在LOCK TABLES關鍵字後面，後跟一個鎖型別。 MySQL提供兩種鎖型別：READ和WRITE。 我們將在下一節詳細介紹這兩種鎖型別。
要釋放表的鎖，請使用以下語句：
UNLOCK TABLES;
表鎖定為READ
表的READ鎖具有以下功能：
- 同時可以通過多個對談獲取表的
READ鎖。此外，其他對談可以從表中讀取資料，而無需獲取鎖定。 - 持有
READ鎖的對談只能從表中讀取資料，但不能寫入。此外，其他對談在釋放READ鎖之前無法將資料寫入表中。來自另一個對談的寫操作將被放入等待狀態，直到釋放READ鎖。 - 如果對談正常或異常終止，MySQL將會隱式釋放所有鎖。這也與
WRITE鎖相關。
下面我們來看看在以下情況下READ鎖如何工作。
首先，連線到testdb資料庫。要查詢當前的連線ID，請使用CONNECTION_ID()函式，如下所示：
mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 6 |
+-----------------+
1 row in set
然後，在向tbl表中插入一個新行。
INSERT INTO tbl(col) VALUES(10);
接下來，從上表tbl中檢索所有行。
mysql> SELECT * FROM tbl;
+----+-----+
| id | col |
+----+-----+
| 1 | 10 |
+----+-----+
1 row in set
之後，要獲取鎖，可以使用LOCK TABLE語句。
最後，在同一個對談中，如果您嘗試在tbl表中插入一個新行，將收到一條錯誤訊息。
mysql> LOCK TABLE tbl READ;
Query OK, 0 rows affected
mysql> INSERT INTO tbl(col) VALUES(11);
1099 - Table 'tbl' was locked with a READ lock and can't be updated
mysql>
所以一旦獲得了READ鎖定，就不能在同一個對談中的表中寫入資料。讓我們從不同的對談中來檢視READ鎖。
首先，開啟另一個終端並連線到資料庫testdb，然後檢查連線ID：
mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 7 |
+-----------------+
1 row in set
然後，從tbl檢索資料，如下所示 -
mysql> SELECT * FROM tbl;
+----+-----+
| id | col |
+----+-----+
| 1 | 10 |
+----+-----+
1 row in set
接下來，從第二個對談(對談ID為7)插入一個新行到tbl表中。
第二個對談的插入操作處於等待狀態，因為第一個對談已經在tbl表上獲取了一個READ鎖，並且尚未釋放。
可以使用SHOW PROCESSLIST語句檢視詳細資訊，如下所示 -
mysql> SHOW PROCESSLIST;
+----+------+-----------------+----------+---------+------+---------------------------------+---------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+----------+---------+------+---------------------------------+---------------------------------+
| 2 | root | localhost:51998 | NULL | Sleep | 474 | | NULL |
| 3 | root | localhost:51999 | yiibaidb | Sleep | 3633 | | NULL |
| 6 | root | localhost:52232 | testdb | Query | 0 | starting | SHOW PROCESSLIST |
| 7 | root | localhost:53642 | testdb | Query | 110 | Waiting for table metadata lock | INSERT INTO tbl(col) VALUES(20) |
+----+------+-----------------+----------+---------+------+---------------------------------+---------------------------------+
4 rows in set
之後，返回第一個對談並使用UNLOCK TABLES語句來釋放鎖。從第一個對談釋放READ鎖之後，在第二個對談中執行INSERT操作。
最後，檢視tbl表中的資料，以檢視第二個對談中的INSERT操作是否真的執行。
mysql> SELECT * FROM tbl;
+----+-----+
| id | col |
+----+-----+
| 1 | 10 |
| 2 | 20 |
+----+-----+
2 rows in set
將MySQL表鎖定WRITE
表鎖為WRITE具有以下功能：
- 只有擁有表鎖定的對談才能從表讀取和寫入資料。
- 在釋放
WRITE鎖之前，其他對談不能從表中讀寫。
詳細了解WRITE鎖的工作原理。
首先，從第一個對談獲取一個WRITE鎖。
LOCK TABLE tbl WRITE;
然後，在tbl表中插入一個新行。
INSERT INTO tbl(col) VALUES(11);
沒有問題，上面語句可能正常執行。接下來，從tbl表讀取資料。
mysql> SELECT * FROM tbl;
+----+-----+
| id | col |
+----+-----+
| 1 | 10 |
| 2 | 20 |
| 3 | 11 |
+----+-----+
3 rows in set
之後，開啟第二個連線到MySQL的對談，嘗試寫和讀資料：
MySQL將這些操作置於等待狀態。可以在第一個對談中，使用SHOW PROCESSLIST語句來檢視它。
mysql> SHOW PROCESSLIST;
+----+------+-----------------+----------+---------+-------+---------------------------------+---------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+----------+---------+-------+---------------------------------+---------------------------------+
| 2 | root | localhost:51998 | NULL | Sleep | 8477 | | NULL |
| 3 | root | localhost:51999 | yiibaidb | Sleep | 11636 | | NULL |
| 8 | root | localhost:54012 | testdb | Sleep | 119 | | NULL |
| 9 | root | localhost:54013 | testdb | Query | 0 | starting | SHOW PROCESSLIST |
| 10 | root | localhost:54016 | testdb | Query | 49 | Waiting for table metadata lock | INSERT INTO tbl(col) VALUES(21) |
+----+------+-----------------+----------+---------+-------+---------------------------------+---------------------------------+
5 rows in set
最後，從第一個對談釋放鎖。執行以下語句 -
UNLOCK TABLES;
執行上面語句後，將看到第二個對談中的所有待處理已經執行操作。
SELECT * FROM tbl;
Query OK, 1 row affected
+----+-----+
| id | col |
+----+-----+
| 1 | 10 |
| 2 | 20 |
| 3 | 11 |
| 4 | 21 |
+----+-----+
4 rows in set
在本教學中，我們向您展示了如何鎖定和解鎖：READ和WRITE操作，以便在對談之間配合表存取。