MySQL syntax analysis table locking

This article brings you relevant knowledge about mysql, which mainly introduces the relevant syntax analysis about table locking. MySQL allows client sessions to explicitly acquire table locks to prevent other sessions To access the same table within a specific time period, let’s take a look at it. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

MySQL table lock

A lock is a sign associated with a table. MySQL allows client sessions to explicitly acquire table locks to prevent other sessions from accessing the same table for a specific period of time. A client session can acquire or release table locks only for itself. It cannot acquire or release table locks for other sessions.

CREATE TABLE tbl (
  id INT NOT NULL AUTO_INCREMENT,
  col INT NOT NULL,
  PRIMARY KEY (id)) Engine = InnoDB;

LOCK and UNLOCK TABLES syntax

The following statements explicitly acquire table locks:

LOCK TABLES table_name [READ | WRITE]

To lock a table, specify its name after the LOCK TABLES keyword. Additionally, you can specify the type of lock, which can be READ or WRITE.

To release the table lock, please use the following statement:

UNLOCK TABLES;

Read lock

READ lock has the following characteristics:

1. READ can be passed Multiple sessions acquire table locks at the same time. Additionally, other sessions can read data from the table without acquiring a lock.
2. A session holding a READ lock can only read data in the table, but cannot write. Additionally, other sessions cannot write data to the table until READ releases the lock. Write operations from another session will enter a wait state until the READ lock is released.
3. If the session terminates normally or abnormally, MySQL will implicitly release all locks. This feature is also related to WRITE lock.

Example:
In the first session, first, connect to the mysqldemo database and use the CONNECTION_ID() function to get the current connection ID, as shown below:

SELECT CONNECTION_ID();

Then, insert a new row in the tbl table.

INSERT INTO tbl(col) VALUES(10);

Next, query the data in the tbl table.

SELECT * FROM tbl;

Afterwards, to obtain the lock, use the LOCK TABLE statement.

LOCK TABLE tbl READ;

Finally, within the same session, if you try to insert a new row in the tbl table, you will get an error message.

INSERT INTO tbl(col) VALUES(11);

So once READ acquires the lock, it cannot write data to the table in the same session.

Check the lock from another session.
First, connect to mysqldemo and check the connection ID:

SELECT CONNECTION_ID();

Then, retrieve the data from the tbl table:

SELECT * FROM tbl;

Next , insert a new row in the tbl table:

INSERT INTO tbl(col) VALUES(20);

The insert operation from the second session is waiting because the first session READ has acquired the lock on the tbl table, But it has not been released yet.

You can view detailed information from SHOW PROCESSLIST.

SHOW PROCESSLIST;

After that, return to the first session and use the UNLOCK TABLES statement to release the lock. After READ releases the lock from the first session, INSERT performs the operation in the second session.

unlock tables;

Finally, check the data of the tbl table to see if the INSERT operation of the second session is actually executed.

SELECT * FROM tbl;

Write lock

WRITE lock has the following characteristics:

1. The only session holding the table lock can read from the table Fetch and write data.
2. Other sessions cannot read data from and write data to the table until the WRITE lock is released.

First, WRITE acquires the lock from the first session.

LOCK TABLE tbl WRITE;

Then, inserting a new row in the tbl table

INSERT INTO tbl(col) VALUES(11);

is useful.

Next, read data from the tbl table.

SELECT * FROM tbl;

It works too.

Afterwards, starting from the second session, try to write and read data:

INSERT INTO tbl(col) VALUES(21);
 SELECT * FROM tbl;

最后，从第一个会话中释放锁定。

UNLOCK TABLES;

看到第二个会话中的所有待处理操作都已执行，下图说明了结果：

读锁与写锁

1. 读锁是“共享”锁，它可以防止正在获取写锁，但不能锁定其他读锁。
2. 写锁是“独占”锁，可以防止任何其他类型的锁。

推荐学习：mysql视频教程

The above is the detailed content of MySQL syntax analysis table locking. For more information, please follow other related articles on the PHP Chinese website!