- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
- MySQL count(*) from multiple tables?
- MySQL query to count rows in multiple tables
- Count(*) rows from multiple tables in MySQL?
- Insert records from multiple tables in MySQL
- How can I sum columns across multiple tables in MySQL?
- How can we upload data into multiple MySQL tables by using mysqlimport?
- How to debug Lock wait timeout exceeded on MySQL?
- How can we create a MySQL view by using data from multiple tables?
- Finding total number of rows of tables across multiple databases in MySQL?
- How can I get the output of multiple MySQL tables from a single query?
- How can we see MySQL temporary tables in the list of tables?
- Which one is preferred between a large table or multiple small tables in MySQL?
- How to search multiple columns in MySQL?
- MySQL join two tables?
- How to display all the MySQL tables in one line?