MySQL lock table study _ MySQL

Yang Tao briefly summarized the MySQL lock table mechanism in his blog, listing the possibility of various MySQL lock tables.
1. for MySQL, there are three lock levels: Page-level, table-level, and RoW-level.

A typical page-level engine is BDB.
A typical table-level engine is MyISAM, MEMORY, and ISAM, which was a long time ago.
A typical line-level engine is INNODB.

2. row locks are the most commonly used in actual applications.
Row-level locks have the following advantages:
1) reduce the LOCK status when multiple connections perform different queries separately.
2) if an exception occurs, data loss can be reduced. Because only one or several rows of data can be rolled back at a time.
The disadvantages of row-level locks are as follows:
1) the page-level lock and table-level lock occupy more memory.
2) query requires more I/O than page-level locks and table-level locks. Therefore, we often use row-level locks for write operations rather than read operations.
3) deadlock is prone.

3. MySQL uses the write queue and read queue to perform write and read operations on the database.

Write locks are as follows:
1) if the table is not locked, write the table to lock it.
2) Otherwise, put the request into the write lock queue.
Read locks are as follows:
1) if no write lock is applied to the table, add a read lock.
2) Otherwise, put the request in the read lock queue.
Of course, we can use low_priority and high_priority to change these actions in write and read operations.

4. here is a simple example to explain the above statement.

Let's run a long query.
1) client 1:
Mysql> select count (*) from content group by content;

...
Client 2:
Mysql> update content set content = 'I love you' where id = 444;
Query OK, 1 row affected (30.68 sec)
Rows matched: 1 Changed: 1 Warnings: 0

It took half a minute.
2) Now we terminate client 1.
Client 2:
Mysql> update content set content = 'I hate you 'Where id = 444;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

It takes only 20 milliseconds.

This example demonstrates the operation of read/write queues.
For client 1 in 1, the table is not locked, and of course no write lock is applied. Therefore, client 1 adds a read lock to the table.
For client 2 in 1, the UPDATE request is put into the write lock queue because the table has a read lock.
When the read lock is released, that is, when the STATUS in show processlist is copy to tmp table, the UPDATE operation starts.

5. You can apply different locks to the MASTER and SLAVE in REPLICATION to achieve optimal performance. (Of course, the premise is that SQL statements are the best .)