LOCK TABLES in MYSQL

The article contains information about LOCK TABLES in MySQL VTY system.

Features added to the database during database design or later (INDEX, Foreign Key, UNIQUE …) can provide performance improvements.

As the database size increases, the added features cause the basic functions like SELECT, INSERT, UPDATE, DELETE to slow down.

Because when every record is added; As an extra, operations such as adding records to the index, renewing the INDEX map, UNIQUE control are performed.

Also, performing similar operations performed by more than one session causes MySQL VTY system to slow down.

Thanks to the LOCK TABLES feature in MySQL, the table queues requests from other sessions for a period of time for a transaction, and transactions are made faster.

LOCK TABLES in MySQL

The following statement is used to lock the table.

READ: It is not possible to add, delete or update records in the table requested by other sessions. They can only read the data.

WRITE: Blocks all requests requested by other sessions.

While adding sa record to a table, let’s lock the table.

In order to observe the table locking process, two different logins are required from the operating system command interpreter, or two different MySQL tools must be used.

First, let’s lock the table with the following expression from a session.

When the following command is executed in the session where the table is locked, detailed information can be seen about the operation waiting to be unlocked.

UNLOCK TABLES in MySQL

To unlock the table, it will be enough to run the command below or close the session.

Check LOCKED TABLES in MySQL(locked by LOCK TABLE)

You can use the below command to check locked tables which locked by LOCK TABLE. IN_USE column indicates that the table is locked.

or better;

or if you want to see locked tables only for a  specific database;