MySQL provides capability to lock/unlock tables in MySQL. This is a useful feature since using locking/unlocking at the table means that we do not have to rely on application level locking/unlocking. However, we should use locking/unlocking sparingly since there is an associated performance penalty.
One of the popular usage of locking/unlocking tables is for taking a backup of the entire database. When we take a snapshot of data, we want to make sure that nobody writes anything to database during that duration and for this, we can lock the database so that all write calls simply get blocked. Other use case for locking is when we don't want other users to read certain data when one user is writing to it.
MySQL provides locking support for two table types: MyISAM and InnoDB. MyISAM supports only table level locking whereas InnoDB can also provide row-level locking. If we have a very granular design where we need row-level locking for certain tables, then we can always consider designing those tables in InnoDB, for others, we can keep the engine type MyISAM.
To lock a table or a set of tables, the general format is: "LOCK TABLES table1 mode, table2 mode, table 3 mode, ...". The table names can also be provided as alias and then format becomes: "LOCK TABLES table1 as alias_for_table1 mode, ...". The mode can be either READ or WRITE mode.
When we lock a table in the READ mode, then we can only issue READ related queries (which is SELECT and SHOW queries). This can be done from the current session/connection that issues the READ lock or can be done from other sessions/connections as well. Basically, with READ lock, no one can do anything with the data except issue a read query. Thus, no one can write to the table (meaning no INSERT, UPDATE, or DELETE queries) including the current session. Once we are done with the operations, we can call "UNLOCK TABLES" to unlock all the tables. Since this query unlock all the tables, we do not need to specify the name of the tables that were locked earlier. Here is an example:
mysql> LOCK TABLES inventory READ; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT * FROM inventory WHERE store_id = 3; +----------+-----------+---------------+ | store_id | item_code | item_quantity | +----------+-----------+---------------+ | 3 | 1003 | 3 | | 3 | 1005 | 22 | +----------+-----------+---------------+ 2 rows in set (0.03 sec) mysql> INSERT INTO inventory VALUES(NULL, 1005, 20); ERROR 1099 (HY000): Table 'inventory' was locked with a READ lock and can't be updated mysql> mysql> DELETE FROM inventory WHERE store_id IS NULL; ERROR 1099 (HY000): Table 'inventory' was locked with a READ lock and can't be updated mysql> mysql> UPDATE inventory SET store_id = 2 WHERE store_id IS NULL; ERROR 1099 (HY000): Table 'inventory' was locked with a READ lock and can't be updated mysql> mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO inventory VALUES(NULL, 1005, 20); Query OK, 1 row affected (0.00 sec)
However, if we were to try to run the same INSERT query from another connection, then the SQL query would block instead of returning an error. Underneath, the query is trying to acquire the lock for the table. And, when we release the lock from the first session, then the query would immediately return in the second session! It makes sense to not return an error for the other session since this way, we do not want other sessions to know that the table is locked.
Now, let us look at WRITE mode. When we do a WRITE lock, then only the current session can run read records from the table (via SELECT) or can write (via INSERT, UPDATE, or DELETE). Here is an example:
mysql> LOCK TABLES inventory WRITE; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM inventory WHERE store_id = 3; +----------+-----------+---------------+ | store_id | item_code | item_quantity | +----------+-----------+---------------+ | 3 | 1003 | 3 | | 3 | 1005 | 22 | +----------+-----------+---------------+ 2 rows in set (0.00 sec) mysql> INSERT INTO inventory VALUES(NULL, 1005, 20); Query OK, 1 row affected (0.00 sec) mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
With WRITE lock, any other session will simply have to wait for any query, whether read or write! In simpler words, if we run a query on any other session, then that query would simply block till we release the lock from the first session.
UNLOCK TABLES is also used with "FLUSH TABLES WITH READ LOCK" statement. A "FLUSH TABLES WITH READ LOCK" statement is used to flush the current tables and add a global lock on all the tables in the current database. This global lock prevents any user from writing any new data to the database. Often, this is done to take a backup of the current database. Once the backup is complete, we can use "UNLOCK TABLES" to unlock all the tables.
In the end, we would list some of the corner case behaviors of locking.
First, if we lock a table (or tables) and then, if we start a transaction (via "START TRANSACTION"), then the tables are automatically unlocked.
Second, if we try to lock a table that has already been locked, then underneath, the second lock first unlocks the table first. This means that if there were any blocked queries from other sessions, then they would potentially get executed! After this, the second query, acquires the lock again.
Third, when we lock a table or a set of tables, then during that session, we can use tables only from the set of locked tables. Attempting to use any tables that were not locked would lead to an error.
Fourth, if we lock a table that has a trigger and if the trigger specifies any additional tables (not present in the earlier lock), then the additional tables also get automatically locked!