The lock mechanism is an important feature of a database that is different from the file system and an effective way to manage concurrent access. MySQL locks include table-level locks, page-level locks, and row-level locks. Table-level locks are the most granular locks in MySQL. They are easy to implement and consume less resources. They are supported by most MySQL engines. The most commonly used MYISAM and INNODB support table-level locks.
The lock mechanism is an important feature of a database that is different from the file system and an effective way to manage concurrent access. MySQL locks include table-level locks, page-level locks, and row-level locks. Table-level locks are the most granular locks in MySQL. They are easy to implement and consume less resources. They are supported by most MySQL engines. The most commonly used MYISAM and INNODB support table-level locks.
The lock mechanism is an important feature of a database that is different from the file system and an effective way to manage concurrent access. MySQL locks include table-level locks, page-level locks, and row-level locks. Table-level locks are the most granular locks in MySQL. They are easy to implement and consume less resources. They are supported by most MySQL engines. The most commonly used MYISAM and INNODB support table-level locking.
Table-level locks fall into two categories: Read locks and write locks. The read lock is intended to read data tables. During the lock, the table cannot be modified. The write lock is expected to update the data table. During the lock period, the table cannot be updated or read by other threads.
Read lock:
Usage: lock table table_name [AS alias_name] READ
Specify the data table. The LOCK type is READ. The AS alias is an optional parameter. If you specify an alias, you must also specify an alias for use.
Prerequisites for applying for a read lock: No threads currently use a write lock on the data table; otherwise, the application will be blocked.
Operation restrictions: other threads can use read locks for locked tables. Other threads cannot use Write locks for locked tables.
Write operation read operation
Whether the read lock thread is used (an error is reported)
No (blocking) can be used without the read lock thread
For MySQL threads that use read locks, since read locks do not allow any threads to modify the lock table, the thread can only perform read operations on the table before releasing the lock resource, an invalid operation is prompted during write operations. For other MySQL threads that do not use the lock, the read operation on the locked table can be normal, but when the write operation is performed, the thread will wait for the release of the read lock, when all read locks of the locked table are released, the thread will respond to the write operation.
Write lock:
Usage: lock table table_name [AS alias_name] [LOW_PRIORITY] WRITE
The alias usage is the same as the read lock. The write lock adds the function of specifying the priority. Adding LOW_PRIORITY can specify the write lock as a low priority.
Prerequisites for applying for a write lock: when no thread uses a write lock or read lock for the data table, otherwise the request is blocked.
Operation restrictions: Other MySQL threads cannot use write or read locks for Lock tables.
Write operation read operation
The write lock thread can
No (blocking) can (blocking) be used without the write lock thread)
For MySQL threads that use Write locks, they can perform read and write operations on the locked tables. However, read/write operations on the specified table are invalid for other threads. Wait until the write lock is released.
Lock allocation priority:
The priority of lock allocation is: LOW_PRIORITY WRITE <READ <WRITE
1. when multiple threads apply for a lock, the lock will be preferentially allocated to the WRITE lock. If there is no WRITE lock, the READ lock will be allocated. LOW_PRIORITY WRITE needs to wait until the WRITE lock and READ are released, to allocate resources.
2. For lock applications with the same priority, the allocation principle is: who applies first and who assigns first.
Note:
1. tables that are not locked (query or update) cannot be operated.
For example, if you only apply for a read lock of Table 1, the SQL statement contains an illegal operation on table 2. For example:
Mysql> lock table test READ;
Query OK, 0 rows affected (0.00 sec)
Mysql> SELECT * FROM test_myisam;
ERROR 1100 (HY000): Table 'test _ myisam' was not locked with LOCK TABLES
2. Tables cannot be used twice in an SQL statement (unless aliases are used)
When a table is used multiple times in an SQL statement, the system reports an error. For example:
Mysql> lock table test READ;
Query OK, 0 rows affected (0.00 sec)
Mysql> SELECT * FROM test WHERE id IN (SELECT id FROM test );
ERROR 1100 (HY000): Table 'test' was not locked with LOCK TABLES
To solve this problem, use an alias. If you use a table multiple times, you must declare multiple aliases.
Mysql> lock table test AS t1 READ, test AS t2 READ;
Query OK, 0 rows affected (0.00 sec)
Mysql> SELECT * FROM test AS t1 WHERE id IN (SELECT id FROM test AS t2 );
+ ---- + ----------- +
| Id | content |
+ ---- + ----------- +
| 1 | tt_1 |
| 3 | new_3 |
| 4 | new_4 |
| 5 | content_5 |
+ ---- + ----------- +
4 rows in set (0.00 sec)
3. Use an alias when applying for a lock. An alias must be added when a table is locked.
