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.