How to lock multiple tables in MySQL?

You can achieve multiple table locks with the help of LOCK TABLES command. The syntax is as follows −

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

The table locks are not transaction safe and it commits the active transaction first implicitly before attempting to lock the second table.

Let’s say 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 table OrderDemo and utfdemo. The utfdemo is already present 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 a session. If you try to create a table then you will 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 fix this, you need to restart MySQL.