Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use LOCK and UNLOCK in SQL". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to use LOCK and UNLOCK in SQL.

MySQL table lock

A lock is a flag 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. Client sessions can only acquire or release table locks for themselves. 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 statement explicitly acquires the table lock

LOCK TABLES table_name [READ | WRITE]

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

To release a lock on a table, use the following statement:

UNLOCK TABLES; read lock

READ locks have the following characteristics:

READ can acquire locks on tables at the same time through multiple sessions. In addition, other sessions can read data from the table without acquiring a lock.

A session that holds an READ lock can only read the data in the table, but cannot write. In addition, other sessions cannot write data to the table until the lock is released by READ. Writes from another session go into a waiting state until the READ lock is released.

If the session terminates normally or abnormally, MySQL implicitly releases all locks. This feature is also related to WRITE locks.

Example:

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

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

Then, to get the lock, use the LOCK TABLE statement.

LOCK TABLE tbl READ

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

INSERT INTO tbl (col) VALUES (11)

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

Check for locks from another session.

First, connect to the 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 from the second session is waiting because the first session READ has acquired the lock on the tbl table but has not been released.

You can view details from SHOW PROCESSLIST.

SHOW PROCESSLIST

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

Unlock tables

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

SELECT * FROM tbl

Write lock

WRITE locks have the following characteristics:

The only session that holds the table lock can read and write data from the table.

Until the WRITE lock is released, other sessions cannot read data from the table and write it to the table.

First, WRITE acquires the lock from the first session.

LOCK TABLE tbl WRITE

Then, insert a new row in the tbl table

INSERT INTO tbl (col) VALUES (11)

It works.

Next, read the data from the tbl table.

SELECT * FROM tbl

It works, too.

Then, starting with the second session, try to write and read the data:

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

Finally, the lock is released from the first session.

UNLOCK TABLES

Seeing that all the pending operations in the second session have been performed, the following figure illustrates the result:

Read locks and write locks are "shared" locks that prevent write locks from being acquired, but cannot lock other read locks. Write locks are "exclusive" locks that prevent any other type of lock.

At this point, I believe you have a deeper understanding of "how to use LOCK and UNLOCK in SQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.