How to lock multiple tables in MySQL?-Mysql Tutorial-php.cn

How to lock multiple tables in MySQL?

王林

Release： 2023-09-04 11:13:11

forward

659 people have browsed it

How to lock multiple tables in MySQL?

With the LOCK TABLES command, you can implement multiple table locks. The syntax is as follows -

LOCK TABLES yourTableName1 WRITE;
LOCK TABLES yourTableName2 WRITE;
LOCK TABLES yourTableName3 WRITE;
LOCK TABLES yourTableName4 WRITE;
.
.
.
N;

Copy after login

The table lock is not transaction safe and it implicitly commits the active transaction first before trying to lock the second table.

Suppose I have a table OrderDemo -

mysql> create table OrderDemo
   -> (
   -> OrderId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> OrderPrice int,
   -> OrderDatetime datetime
   -> );
Query OK, 0 rows affected (0.66 sec)

Copy after login

Here is the query to lock the tables OrderDemo and utfdemo. utfdemo already exists in the sample database. The query is as follows -

mysql> LOCK TABLES OrderDemo WRITE;
Query OK, 0 rows affected (0.03 sec)
mysql> LOCK TABLES utfdemo WRITE;
Query OK, 0 rows affected (0.07 sec)

Copy after login

Now it locks the table for the session. If you try to create a table, you'll get an error.

The error is as follows -

mysql> create table LockTableDemo
   -> (
   -> UserId int,
   -> UserName varchar(10)
   -> );
ERROR 1100 (HY000): Table 'LockTableDemo' was not locked with LOCK TABLES
mysql> create table UserIformation
   -> (
   -> UserId int,
   -> UserName varchar(10)
   -> );
ERROR 1100 (HY000): Table 'UserIformation' was not locked with LOCK TABLES

Copy after login

To resolve this issue, you need to restart MySQL.

The above is the detailed content of How to lock multiple tables in MySQL?. For more information, please follow other related articles on the PHP Chinese website!