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;

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)

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)

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

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!