# 10.2 表锁定 在本教程中，您将学习如何使用MySQL锁来协调会话之间的表访问。 MySQL允许客户端会话明确获取表锁，以防止其他会话在特定时间段内访问表。客户端会话只能为自己获取或释放表锁。它不能获取或释放其他会话的表锁。 在详细介绍之前，我们将创建一个名为`sampledb`的示例数据库，其中包含一个简单的`tbl`表来模拟练习表锁定语句。 ```sql 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语法 获取表的锁的简单形式如下： ```sql LOCK TABLES table_name [READ | WRITE] ``` 可将表的名称放在`LOCK TABLES`关键字后面，后跟一个锁类型。 MySQL提供两种锁类型：`READ`和`WRITE`。 我们将在下一节详细介绍这两种锁类型。 要释放表的锁，请使用以下语句： ```sql UNLOCK TABLES; ``` ### 表锁定为READ 表的READ锁具有以下功能： * 同时可以通过多个会话获取表的`READ`锁。此外，其他会话可以从表中读取数据，而无需获取锁定。 * 持有`READ`锁的会话只能从表中读取数据，但不能写入。此外，其他会话在释放`READ`锁之前无法将数据写入表中。来自另一个会话的写操作将被放入等待状态，直到释放`READ`锁。 * 如果会话正常或异常终止，MySQL将会隐式释放所有锁。这也与`WRITE`锁相关。 下面我们来看看在以下情况下`READ`锁如何工作。 首先，连接到`testdb`数据库。要查找当前的连接ID，请使用`CONNECTION_ID()`函数，如下所示： ```sql mysql> SELECT CONNECTION_ID(); +-----------------+ | CONNECTION_ID() | +-----------------+ | 6 | +-----------------+ 1 row in set ``` 然后，在向`tbl`表中插入一个[新行](http://www.yiibai.com/mysql/insert-statement.html)。 ```sql INSERT INTO tbl(col) VALUES(10); ``` 接下来，从上表`tbl`中检索所有行。 ```sql mysql> SELECT * FROM tbl; +----+-----+ | id | col | +----+-----+ | 1 | 10 | +----+-----+ 1 row in set ``` 之后，要获取锁，可以使用`LOCK TABLE`语句。 最后，在同一个会话中，如果您尝试在`tbl`表中插入一个新行，将收到一条错误消息。 ```sql 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： ```sql mysql> SELECT CONNECTION_ID(); +-----------------+ | CONNECTION_ID() | +-----------------+ | 7 | +-----------------+ 1 row in set ``` 然后，从`tbl`检索数据，如下所示 - ```sql mysql> SELECT * FROM tbl; +----+-----+ | id | col | +----+-----+ | 1 | 10 | +----+-----+ 1 row in set ``` 接下来，从第二个会话(会话ID为`7`)插入一个新行到`tbl`表中。 ![img](http://www.yiibai.com/uploads/images/201707/2107/862100707_50181.jpg) 第二个会话的插入操作处于等待状态，因为第一个会话已经在`tbl`表上获取了一个`RE`AD锁，并且尚未释放。 可以使用`SHOW PROCESSLIST`语句查看详细信息，如下所示 - ```sql 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`操作是否真的执行。 ```sql mysql> SELECT * FROM tbl; +----+-----+ | id | col | +----+-----+ | 1 | 10 | | 2 | 20 | +----+-----+ 2 rows in set ``` ### 将MySQL表锁定WRITE 表锁为`WRITE`具有以下功能： * 只有拥有表锁定的会话才能从表读取和写入数据。 * 在释放`WRITE`锁之前，其他会话不能从表中读写。 详细了解`WRITE`锁的工作原理。 首先，从第一个会话获取一个`WRITE`锁。 ```sql LOCK TABLE tbl WRITE; ``` 然后，在`tbl`表中插入一个新行。 ```sql INSERT INTO tbl(col) VALUES(11); ``` 没有问题，上面语句可能正常执行。接下来，从`tbl`表读取数据。 ```sql mysql> SELECT * FROM tbl; +----+-----+ | id | col | +----+-----+ | 1 | 10 | | 2 | 20 | | 3 | 11 | +----+-----+ 3 rows in set ``` 之后，打开第二个连接到MySQL的会话，尝试写和读数据： MySQL将这些操作置于等待状态。可以在第一个会话中，使用`SHOW PROCESSLIST`语句来查看它。 ```sql 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 ``` 最后，从第一个会话释放锁。执行以下语句 - ```sql UNLOCK TABLES; ``` 执行上面语句后，将看到第二个会话中的所有待处理已经执行操作。 ```sql SELECT * FROM tbl; Query OK, 1 row affected +----+-----+ | id | col | +----+-----+ | 1 | 10 | | 2 | 20 | | 3 | 11 | | 4 | 21 | +----+-----+ 4 rows in set ``` 在本教程中，我们向您展示了如何锁定和解锁：`READ`和`WRITE`操作，以便在会话之间配合表访问。 --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://hezhiqiang-book.gitbook.io/mysql/di-yi-zhang/index-9/biao-suo-ding.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.