Mysql 简明教程

MySQL - Table Locking

MySQL 数据库提供多用户环境，允许多个客户端同时访问该数据库。为了顺利运行此环境，MySQL 引入了锁的概念。

MySQL database provides a multi-user environment, that allows multiple clients to access the database at the same time. To run this environment smoothly, MySQL introduced the concept of locks.

会话中的客户端可以锁定他们正在处理的某个表，以防止其他客户端使用同一张表。此过程将避免在多个用户同时处理同一张表时可能出现的任何数据丢失情况。

A client in a session can lock a certain table they are working on, in order to prevent other clients from using the same table. This process will avoid any data losses that might occur when multiple users work on the same table simultaneously.

客户端可以根据需要锁定和解锁表。但是，如果某个客户端会话已锁定表，则在该表被释放之前，其他客户端会话无法访问该表。

A client can lock a table and unlock it whenever needed. However, if a table is already locked by a client session, it cannot be accessed by other client sessions until it is released.

Locking Tables in MySQL

您可以通过锁定表来限制访问 MYSQL 中表中的记录。这些锁用于阻止其他会话修改当前会话中的表。

You can restrict the access to records of the tables in MYSQL by locking them. These locks are used to keep other sessions away from modifying the tables in the current session.

MySQL 会话只能为自己获取或释放表中的锁。要使用 MySQL LOCK TABLES 语句锁定表，您需要具有 TABLE LOCK 和 SELECT 特权。

MySQL sessions can acquire or release locks on the table only for itself. To lock a table using the MySQL LOCK TABLES Statement you need have the TABLE LOCK and SELECT privileges.

这些锁用于解决并发问题。MYSQL 表锁有两种 -

These locks are used to solve the concurrency problems. There are two kinds of MYSQL table locks −

READ LOCK − If you apply this lock on a table the write operations on it are restricted. i.e., only the sessions that holds the lock can write into this table.
WRITE LOCK − This lock allows restricts the sessions (that does not possess the lock) from performing the read and write operations on a table.

Syntax

以下是 MySQL LOCK TABLES 语句的语法 -

Following is the syntax of the MySQL LOCK TABLES Statement −

LOCK TABLES table_name [READ | WRITE];

Unlocking Tables in MySQL

一旦客户端会话完成对 MySQL 表的使用/访问后，他们必须解锁该表以便其他客户端会话使用它。为此，可以使用 MySQL UNLOCK TABLE 语句。这将释放表，直到其他会话再次将其锁定。

Once the client session is done using/accessing a MySQL table, they must unlock the table for other client sessions to use it. To do so, you can use the MySQL UNLOCK TABLE statement. This will release the table until other sessions lock it again.

Syntax

以下是 MySQL UNLOCK TABLES 语句的语法 -

Following is the syntax of the MySQL UNLOCK TABLES Statement −

UNLOCK TABLES;

Example

让我们从创建一个名为 CUSTOMERS 的表开始，该表包含以下所示的详细信息 -

Let us start with creating a table named CUSTOMERS that contains the details as shown below −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

现在，让我们使用 INSERT 语句向上面创建的表中插入 2 条记录，如下所示 -

Now, let’s insert 2 records into the above created table using the INSERT statement as −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
  (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
  (2, 'Khilan', 25, 'Delhi', 1500.00 );

使用以下查询创建另一个名为 BUYERS 的表 -

Create another table named BUYERS using the following query −

CREATE TABLE BUYERS (
   B_ID INT AUTO_INCREMENT,
   B_NAME VARCHAR(20) NOT NULL,
   B_AGE INT NOT NULL,
   B_ADDRESS CHAR (25),
   B_SALARY DECIMAL (18, 2),
   PRIMARY KEY (B_ID)
);

以下是使用 INSERT INTO SELECT 语句将记录插入 BUYERS 表的查询。在此，我们尝试从 CUSTOMERS 表向 BUYERS 表插入记录。

Following queries inserts records into the BUYERS table using the INSERT INTO SELECT statement. Here, we are trying to insert records from the CUSTOMERS table to BUYERS table.

Locking and Unlocking:

在传输之前，我们要获取我们向其中插入记录的 BUYERS 表的写锁定，并获取我们从中插入记录的 CUSTOMERS 表的读锁定。最后，在传输后，我们要释放这些记录。

Here before the transfer, we are acquiring the write lock on the BUYERS table to which we are inserting records and acquiring read lock on the CUSTOMERS table from which we are inserting records. Finally, after the transfer we are releasing the records.

LOCK TABLES CUSTOMERS READ, BUYERS WRITE;

INSERT INTO BUYERS (B_ID, B_NAME, B_AGE, B_ADDRESS, B_SALARY)
   SELECT
      ID, NAME, AGE, ADDRESS, SALARY
   FROM
      CUSTOMERS
   WHERE
      ID = 1 AND NAME = 'Ramesh';
INSERT INTO BUYERS (B_ID, B_NAME, B_AGE, B_ADDRESS, B_SALARY)
   SELECT
      ID, NAME, AGE, ADDRESS, SALARY
   FROM
      CUSTOMERS
   WHERE
      ID = 2 AND NAME = 'Khilan';

UNLOCK TABLES;

Verification

我们可以使用以下查询验证 BUYERS 表的内容 −

We can verify the contents of the BUYERS table using the below query −

SELECT * FROM BUYERS;

正如我们在 BUYERS 表中所看到的，记录已传输完毕。

As we can see in the BUYERS table, the records has been transferred.

Table Locking Using a Client Program

除了通过 MySQL 查询锁定 MySQL 数据库中的表之外，我们还可以使用客户端程序来执行 LOCK TABLES 操作。

Besides locking a table in a MySQL database with a MySQL query, we can also use a client program to perform the LOCK TABLES operation.

Syntax

以下是使用各种编程语言锁定 MySQL 表的语法 −

Following are the syntaxes to Lock a table in MySQL in various programming languages −

Example

以下是这些程序 −

Following are the programs −