Today, while troubleshooting a database synchronization issue, I used the flush tables with read lock command and took the opportunity to learn more about table locking.
1. FLUSH TABLES WITH READ LOCK
This command applies a global read lock. Once executed, all tables across all databases are locked in a read-only state. It is typically used during online database backups, where write operations are blocked but read operations proceed without issue.
The unlock statement is also unlock tables.
2. LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
This command is a table-level lock, allowing you to lock a specific table. For example: lock tables test read; does not affect write operations on other tables.
The unlock statement is also unlock tables.
Both of these statements share an important characteristic: they involve implicit commits. When you exit the MySQL terminal, unlock tables is implicitly executed. This means you must keep the session active for the table locks to remain in effect.
P.S. MySQL’s Read Lock and Write Lock
Read-lock: Allows other concurrent read requests but blocks write requests, meaning simultaneous reads are permitted, but any writes are prohibited. Also known as a shared lock.
Write-lock: Does not allow any other concurrent read or write requests; it is exclusive. Also known as an exclusive lock.
Source: http://blog.csdn.net/shootyou/archive/2010/11/22/6026735.aspx