Shulou(Shulou.com)05/31 Report--

This article mainly explains "Introduction to the Usage of MySQL Lock". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian slowly and deeply to study and learn "Introduction to the Usage of MySQL Lock" together.

Locking mechanism is an important feature of database that is different from file system, and it is also an effective way to manage concurrent access. MySQL locks are divided into table-level locks, page-level locks, and row-level locks. Table-level locks are the most granular locks in MySQL. They are simple to implement, consume less resources, and are supported by most MySQL engines. The most commonly used MYISAM and INNODB support table-level locking.

Table-level locking falls into two categories, read locks and write locks. A read lock is an expected read operation on a data table, ensuring that the table cannot be modified during the lock. A write lock is an operation that anticipates an update to a data table and ensures that the table cannot be updated or read by other threads during the lock.

Read lock:

LOCK TABLE table_name [ AS alias_name ] READ

Specify the data table, LOCK type is READ, AS alias is optional parameter, if you specify alias, you must also specify alias when using

Premise for applying read lock: No thread currently uses write lock on this data table, otherwise the application will block.

Operation restrictions: Other threads may use read locks on locked tables; other threads may not use write locks on locked tables

For MySQL threads that use read locks, since read locks do not allow any thread to modify the locked table, the thread can only read the table before releasing the lock resources, and an illegal operation will be prompted when writing. For other MySQL threads that do not use locks, reading the locked table can proceed normally, but when writing, the thread will wait for the release of the read lock, and when all read locks of the locked table are released, the thread will respond to the write operation.

Write lock:

How to use: LOCK TABLE table_name [AS alias_name] [ LOW_PRIORITY ] WRITE

Alias usage is the same as read lock, write lock adds the ability to specify priority, add LOW_PRIORITY can specify write lock as low priority.

Premise for applying write lock: When no thread uses write lock and read lock on the data table, otherwise apply back to block.

Operation restrictions: Other MySQL threads cannot use write locks or read locks on lock tables.

MySQL threads that use write locks can read and write to locked tables. For other threads, however, reading or writing to the specified table is illegal and needs to wait until the write lock is released.

Lock allocation priority:

For priority of lock assignment: LOW_PRIORITY WRITE

< READ < WRITE 　　1、当多个线程申请锁，会优先分配给WRITE锁，不存在WRITE锁时，才分配READ锁，LOW_PRIORITY WRITE需要等到WRITE锁与READ都释放后，才有机会分配到资源。 　　2、对于相同优先级的锁申请，分配原则为谁先申请，谁先分配。 　　注意事项： 　　1、不能操作（查询或更新）没有被锁定的表。 　　例如当只申请table1的读锁，SQL语句中包含对table2的操作是非法的。例如： 　　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

You cannot use tables twice in a SQL (unless you use aliases).

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

The solution to this problem is to use aliases. If you use a table multiple times, you need to 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 alias when applying for lock, and alias must be added when using lock table.

Thank you for reading, the above is the content of "MySQL lock usage introduction", after the study of this article, I believe that everyone has a deeper understanding of MySQL lock usage introduction this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.