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

In this issue, the editor will bring you about the role of Lock Tables and Unlock Tables in MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

The syntax for locking the table:

LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}].

The LOCAL modifier means that inserts can be allowed in other sessions on tables that have acquired READ locks in the current session. However, if you use a session other than Server to manipulate the database while keeping the lock, you cannot use READ LOCAL. In addition, for InnoDB tables, READ LOCAL is the same as READ.

The LOCAL modifier enables nonconflicting INSERT statements (concurrent inserts) by other sessions to execute while the lock is held. (See Section 8.11.3, "Concurrent Inserts") However, READ LOCAL cannot be used if you are going to manipulate the database using processes external to the server while you hold the lock. For InnoDB tables, READ LOCAL is the same as READ.

The modifier LOW_PRIORITY is used in previous versions of MySQL and affects locking behavior, but it has been deprecated since MySQL 5.6.5. If you use it, it will generate a warning.

[LOW_PRIORITY] WRITE lock: The session that holds the lock can read and write the table. Only the session that holds the lock can access the table. No other session can access it until the lock is released. Lock requests for the table by other sessions block while the WRITE lock is held. The LOW_PRIORITY modifier has no effect. In previous versions of MySQL, it affected locking behavior, but this is no longer true. As of MySQL 5.6.5, it is deprecated and its use produces a warning. Use WRITE without LOW_PRIORITY instead.

The syntax for unlocking the table:

UNLOCK TABLES

LOCK TABLES locks the table for the current session. UNLOCK TABLES releases any locks held by the current session. The official document "13.3.5 LOCK TABLES and UNLOCK TABLES Syntax" has already done a lot of introduction to LOCK TALES and UNLOCK TABLES. Let's use some test examples to gain an in-depth understanding of the relevant knowledge points of lock and unlock tables. Let's first prepare the tables and data for the test environment.

Mysql > create table test (id int, name varchar (12)); Query OK, 0 rows affected (0.07 sec) mysql > insert into test-> select 10001, 'kerry' union all-> select 10002,' richard' union all-> select 10003, 'jimmy'; Query OK, 3 rows affected (0 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql >

After the current session (session ID 61) holds the READ lock of the test table, the current session can only read the table and cannot write data to the table, otherwise an error such as "Table 'test' was locked with a READ lock and can't be updated" will be reported.

Note: if you use LOCK TABLE WRITE to lock the table, you can update the data. For details, see the introduction below.

Mysql > select connection_id (); +-+ | connection_id () | +-+ | 61 | +-+ 1 row in set (0.00 sec) mysql > show open tables where in_use > = 1; Empty set (0.00 sec) mysql > lock tables test read Query OK, 0 rows affected (0.00 sec) mysql > show open tables where in_use > = 1 +-+ | Database | Table | In_use | Name_locked | +-+ | MyDB | test | 1 | 0 | +-- -+ 1 row in set (0.01sec) mysql > select * from test +-+-+ | id | name | +-+-+ | 10001 | kerry | | 10002 | richard | | 10003 | jimmy | +-+-+ 3 rows in set (sec) mysql > insert into test-> values (10004, 'ken') ERROR 1099 (HY000): Table 'test' was locked with a READ lock and can't be updated mysql >

Other sessions can also query the table test, but the table cannot be modified and will always be in a blocked state (Waiting for table metadata lock) if the DML operation is performed.

In addition, let's test the use of the modifier LOCAL, as follows:

Mysql > create table test2 (id int, name varchar (12)) engine=MyISAM; Query OK, 0 rows affected (0.05 sec) mysql > insert into test2-> select 1001, 'test'; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql > select connection_id () +-+ | connection_id () | +-+ | 66 | +-+ 1 row in set (0.00 sec) mysql > lock tables test2 read local; Query OK, 0 rows affected (0.00 sec) mysql > select * from test2 +-+-+ | id | name | +-+-+ | 1001 | test | +-+-+ 1 row in set (0.00 sec) mysql > insert into test2-> select 1002, 'kkk'; ERROR 1099 (HY000): Table' test2' was locked with a READ lock and can't be updated mysql >

But in other sessions, you can see that the table test2 can be inserted. Of course, the premise is that the storage engine of the table cannot be an innodb engine, otherwise using the modifier LOCAL is the same as not using LOCAL, and other sessions cannot write to the table.

Mysql > select connection_id (); +-+ | connection_id () | +-+ | 65 | +-+ 1 row in set (0.00 sec) mysql > select * from test2 +-+-+ | id | name | +-+-+ | 1001 | test | +-+-+ 1 row in set (0.00 sec) mysql > insert into test2-> select 1002, 'kkk'; Query OK, 1 row affected (0 sec) Records: 1 Duplicates: 0 Warnings: 0

So can other sessions also read this table? Can other sessions also lock the table (LOCK TABLES READ LOCAL)? Can other sessions also lock write (LOCK TABLE WRITE)? With regard to these questions, other sessions can also read this table, and other tables can lock the table (LOCK TABLES READ LOCAL), but not LOCK TABLE WRITE.

MyISAM tables are rarely used now, so let's experiment with tables of type InnoDB, using lock table to lock table test in one of the sessions

Mysql > select connection_id (); +-+ | connection_id () | +-+ | 61 | +-+ 1 row in set (0.00 sec) mysql > lock table test read; Query OK, 0 rows affected (0.00 sec) mysql > show open tables where in_use > = 1 +-+ | Database | Table | In_use | Name_locked | +-+ | MyDB | test | 1 | 0 |

Then do the following test in session 62:

Mysql > select connection_id (); +-+ | connection_id () | +-+ | 62 | +-+ 1 row in set (0.01sec) mysql > select * from test +-+-+ | id | name | +-+-+ | 10001 | kerry | | 10002 | richard | 10003 | jimmy | +-+-+ 3 rows in set (0.00 sec) mysql > lock tables test read; Query OK, 0 rows affected (0.00 sec) mysql > show open tables where in_use > = 1 +-+ | Database | Table | In_use | Name_locked | +-+ | MyDB | test | 2 | 0 | +-- -+ 1 row in set (0.00 sec) mysql > unlock tables Query OK, 0 rows affected (0.00 sec) mysql > show open tables where in_use > = 1 +-+ | Database | Table | In_use | Name_locked | +-+ | MyDB | test | 1 | 0 | +-- -+ 1 row in set (0.00 sec) mysql > lock tables test write

As the previous test shows, if a session acquires an READ lock on one table, all other sessions can only be read from the table. Cannot write to the table, other sessions can also acquire a READ lock on the table, and you will see an increase in the value of in_use in the show open tables. LOCK TABLES READ is actually a table lock and a shared lock. But when a session acquires a READ lock on a table, other sessions cannot acquire the WRITE lock on the table, and it is blocked until the session that holds the READ lock releases the READ lock.

The session (session 61) can then continue to acquire the WRITE lock. When the session acquires the WRITE lock, other sessions cannot acquire the READ lock

Mysql > lock table test write; Query OK, 0 rows affected (0.00 sec)

It is also important to note that if one of the tables is locked in the current session, you cannot query the other tables. Otherwise, a "ERROR 1100 (HY000): Table 'worklog' was not locked with LOCK TABLES" error will be reported.

So let's take another look at the WRITE lock. Before testing, execute the unlock tables command in the above two sessions. Then obtain a WRITE lock on the table TEST, as shown below, the current session can read and write the table TEST

Mysql > unlock tables; Query OK, 0 rows affected (0.00 sec) mysql > select connection_id (); +-+ | connection_id () | +-+ | 61 | +-+ 1 row in set (0.00 sec) mysql > show open tables where in_use > = 1 Empty set (0.00 sec) mysql > lock tables test write; Query OK, 0 rows affected (0.00 sec) mysql > select * from test +-+-+ | id | name | +-+-+ | 10001 | kerry | | 10002 | richard | 10003 | jimmy | +-+-+ 3 rows in set (0.00 sec) mysql > update test set name='ken' where id=10003; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql >

Other sessions cannot read or write table TEST, will be blocked, and certainly cannot acquire the READ lock or WRITE lock of table TEST. That is, when a session acquires a WRITE lock on a table, only the locked session can READ or WRITE the table, and all other sessions are blocked.

Mysql > unlock tables; Query OK, 0 rows affected (0.00 sec) mysql > show open tables where in_use > = 1 +-+ | Database | Table | In_use | Name_locked | +-+ | MyDB | test | 1 | 0 | +-- -+ 1 row in set (0.00 sec) mysql > select * from test

Mysql > select connection_id (); +-+ | connection_id () | +-+ | 63 | +-+ 1 row in set (0.00 sec) mysql > show processlist +-+-+ | Id | User | Host | db | Command | Time | State | Info | +-- -+ | 61 | root | localhost | MyDB | Sleep | 86 | | NULL | | 62 | root | localhost | MyDB | Query | 40 | Waiting for table metadata lock | select * from test | 63 | root | localhost | MyDB | Query | 0 | init | show processlist | 64 | root | localhost | MyDB | Sleep | | 2551 | | NULL | +-+ -+ 4 rows in set (0.00 sec)

UNLOCK TABLES releases any locks held by the current session, but when the session issues another LOCK TABLES, or when the connection to the server is closed, all tables locked by the current session are implicitly unlocked. We can also test it next.

Mysql > lock tables test read; Query OK, 0 rows affected (0.00 sec) mysql > show open tables where in_use > = 1 +-+ | Database | Table | In_use | Name_locked | +-+ | MyDB | test | 1 | 0 | +-- -+ 1 row in set (0.00 sec) mysql > lock tables worklog read Query OK, 0 rows affected (0.00 sec) mysql > show open tables where in_use > = 1 +-+ | Database | Table | In_use | Name_locked | +-+ | MyDB | worklog | 1 | 0 | +-+ 1 row in set (0.00 sec) mysql >

So how do we lock multiple tables in the current session? As follows:

Mysql > show open tables where in_use > = 1; Empty set (0.00 sec) mysql > lock tables test read, worklog read; Query OK, 0 rows affected (0.00 sec) mysql > show open tables where in_use > = 1 +-+ | Database | Table | In_use | Name_locked | +-+ | MyDB | worklog | 1 | 0 | | MyDB | test | 1 | 0 | +-+ 2 rows in set (0.00 sec) mysql >

In addition, there are some details. Can LOCK TABLES lock views, triggers, and temporary tables?

Mysql > create table test2 (id int, sex bit); Query OK, 0 rows affected (0.06 sec) mysql > insert into test2-> select 10001, 1 union all-> select 10002, 0 union all-> select 10003, 1; Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql > create view v_test-> as-> select t1.id, t1.name, t2.sex-> from test T1 left join test2 T2 on t1.id = t2.id Query OK, 0 rows affected (0.01 sec) mysql > lock tables v_test read; Query OK, 0 rows affected (0.00 sec) mysql > show open tables where in_use > = 1 +-+ | Database | Table | In_use | Name_locked | +-+ | MyDB | test2 | 1 | 0 | MyDB | | test | 1 | 0 | +-+ 2 rows in set (0.00 sec) mysql > |

As shown in the previous test, for VIEW locking, the LOCK TABLES statement locks all base tables used in VIEW. If you use LOCK TABLE for a trigger, the whole table contained in the trigger (any tables used in triggers are also locked implicitly) is locked.

Mysql > unlock tables; Query OK, 0 rows affected (0.00 sec) mysql > create temporary table tmp like test; Query OK, 0 rows affected (0.04 sec) mysql > show open tables where in_use > = 1; Empty set (0.00 sec) mysql > select database () +-+ | database () | +-+ | MyDB | +-+ 1 row in set (0.00 sec) mysql > select * from tmp; Empty set (0.00 sec) mysql > insert into tmp-> select 1001, 'kerry'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql >

LOCK TABLES and UNLOCK TABLES can only acquire and release locks for themselves, not for other sessions, nor can they release locks held by other sessions. An object acquires a lock and requires SELECT and LOCK TABLES permissions on the object. The LOCK TABLES statement explicitly acquires a table lock for the current session. Finally, refer to the official documentation for the similarities and differences between LOCK TABLES and transaction locks:

LOCK TABLES and UNLOCK TABLES interact with the use of transactions as follows:

LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.

UNLOCK TABLES implicitly commits any active transaction, but only if LOCK TABLES has been used to acquire table locks. For example, in the following set of statements,UNLOCK TABLES releases the global read lock but does not commit the transaction because no table locks are in effect:

This is what the role of Lock Tables and Unlock Tables in MySQL shared by Xiaobian is. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.