Mysql Locks

Table locks can be an important tool for ENGINE=MyISAM, but are rarely useful for ENGINE=InnoDB. If you are tempted to use table locks with InnoDB, you should rethink how you are working with transactions.

MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

Locks may be used to emulate transactions or to get more speed when updating tables. This is explained in more detail later in this section.

Command:LOCK TABLES table_name READ|WRITE;

you can assign only lock type to a single table;

Example (READ LOCK):

LOCK TABLES table_name READ;

Example (WRITE LOCK):

LOCK TABLES table_name WRITE;

To see lock is applied or not, use following Command

SHOW OPEN TABLES;

To flush/remove all locks, use following command:

UNLOCK TABLES;

EXAMPLE:

LOCK TABLES products WRITE:  
INSERT INTO products(id,product_name) SELECT id,old_product_name FROM old_products;
UNLOCK TABLES;

Above example any external connection cannot write any data to products table until unlocking table product

EXAMPLE:

LOCK TABLES products READ:  
INSERT INTO products(id,product_name) SELECT id,old_product_name FROM old_products;
UNLOCK TABLES;

Above example any external connection cannot read any data from products table until unlocking table product