加载中...

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提供兩種鎖型別:READWRITE。 我們將在下一節詳細介紹這兩種鎖型別。

要釋放表的鎖,請使用以下語句:

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

在本教學中,我們向您展示了如何鎖定和解鎖:READWRITE操作,以便在對談之間配合表存取。