MySQL lock usage-table-Level Lock

MySQL lock usage-table-level 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 at www.2cto.com: Usage: lock table table_name [AS alias_name] READ specifies the data TABLE. The LOCK type is READ. The AS alias is an optional parameter. If an alias is specified, you must specify an alias to apply for a read lock. Note: 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 locks are not allowed for read operations. No (error Reported) whether the read lock thread is used or not (blocking). Because the read lock does not allow any thread to modify the lock table, before the lock resource is released, this thread can only perform read operations on the table. 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] the usage of WRITE aliases is the same AS that of read locks. 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 on www.2cto.com: Other MySQL threads cannot use Write locks for Lock tables, read locks, write operations, read operations, use Write locks, or use Write locks. Can they not use Write locks? Can (Block) 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, which 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. You cannot operate (query or update) tables that are not locked. 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: www.2cto.com 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. you cannot use a table twice in an SQL statement (unless you use an alias). When you use a table 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 the alias is used to solve this problem. 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 ); + ---- + ----------- + www.2cto.com | id | content | + ---- + ----------- + | 1 | tt_1 | 3 | new_3 | 4 | new_4 | 5 | content_5 | + ---- + ----------- + 4 rows in set (0.00 sec) 3. apply for a lock using an alias. When using a locked table, you must add an alias.