Analysis of the lock table mechanism when mysql queries an update (only MYISAM is introduced) _ MySQL

Analysis of the lock table mechanism when mysql queries an update (only MYISAM is introduced) in order to better optimize mysql in high concurrency, it is necessary to understand the lock table mechanism when mysql queries an update.

I. Overview

MySQL has three levels of locks: page, table, and row.
The MyISAM and MEMORY storage engines use table-level locking. the BDB storage engine uses page-level locking, but also supports table-level locking; the InnoDB storage engine supports both row-level locking and table-level locking, but row-level locks are used by default.

The features of the three MySQL locks are summarized as follows:

Table-level locks: low overhead, fast locking, no deadlock, large lock granularity, the highest probability of lock conflict, the lowest concurrency.
Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock conflict, and the highest concurrency.
Page Lock: overhead and lock time are between table locks and row locks. deadlocks may occur. the lock granularity is between table locks and row locks, with a general concurrency.

II. MyISAM table lock

The MyISAM storage engine only supports table locks and is the most widely used storage engine.

1. query table-level lock contention

You can analyze the table lock contention on the system by checking the table_locks_waited and table_locks_immediate status variables:

Mysql> show status like 'table % ';
+ -------- + ---- +
| Variable_name | Value |
+ -------- + ---- +
| Table_locks_immediate | 76939364 |
| Maid | 305089 |
+ -------- + ---- +
2 rows in set (0.00 sec)

The value of Table_locks_waited is relatively high, indicating that there is a serious table-level lock contention.

2. MySQL table-level lock mode

There are two Table-level locks for MySQL: Table Read Lock and Table Write Lock ). Before executing a query statement (SELECT), MyISAM automatically locks all involved tables before performing UPDATE operations (UPDATE, DELETE, INSERT, etc, A write lock is automatically applied to the involved table.

Therefore, the MyISAM table may be operated in the following situations:
A. read operations on the MyISAM table (Read lock) Does not block read requests from other processes to the same table, but blocks write requests to the same table. Write operations of other processes are performed only after the read lock is released.
B. write operations on the MyISAM table (Write lock) To block other processes from accessing the same table.ReadAndWriteThe read/write operations of other processes are performed only when the write lock is released.

The following example is used to verify the above points. The data table gz_phone contains more than 2 million data, field id, phone, ua, and day. Now, you can use multiple clients to perform operation analysis on the table at the same time.
A. When I use client 1 for a long read operation, I use client 2 for read and write operations respectively:
Client1:

Mysql> select count (*) from gz_phone group by ua;
75508 rows in set (3 min 15.87 sec)

Client2:

Select id, phone from gz_phone limit 1000,10;
+ -- + --- +
| Id | phone |
+ -- + --- +
| 1001/2222 |
| 1002/2222 |
| 1003/2222 |
| 1004/2222 |
| 1005/2222 |
| 1006/2222 |
| 1007/2222 |
| 1008/2222 |
| 1009/2222 |
| 1010/2222 |
+ -- + --- +
10 rows in set (0.01 sec)

Mysql> update gz_phone set phone = '000000' where id = 11111111111;
Query OK, 0 rows affected (2 minutes 57.88 sec)
Rows matched: 1 Changed: 0 Warnings: 0

This means that when the data table has a read lock, the query operation of other processes can be executed immediately, but the update operation will be executed only after the read lock is released.

B. When client 1 is used for a long update operation, client 2 and 3 are used for read/write operations respectively:
Client1:

Mysql> update gz_phone set phone = '000000 ′;
Query OK, 1671823 rows affected (3 min 4.03 sec)
Rows matched: 2212070 Changed: 1671823 Warnings: 0

Client2:

Mysql> select id, phone, ua, day from gz_phone limit 10;
+ -- + --- + ------- + ---- +
| Id | phone | ua | day |
+ -- + --- + ------- + ---- +
| 1 | 2222 | SonyEricssonK310c |
| 2 | 2222 | SonyEricssonK750c |
| 3 | 2222 | maui wap Browser |
| 4 | 2222 | Nokia3108 | 2007-12-19 |
| 5 | 2222 | LENOVO-I750 |
| 6 | 2222 | BIRD_D636 | 2007-12-19 |
| 7 | 2222 | SonyEricssonS500c |
| 8 | 2222 | SAMSUNG-SGH-E258 |
| 9 | 2222 | NokiaN73-1 |
| 10 | 2222 | Nokia2610 | 2007-12-19 |
+ -- + --- + ------- + ---- +
10 rows in set (2 min 58.56 sec)

Client3:

Mysql> update gz_phone set phone = '000000' where id = 1;
Query OK, 1 row affected (3 min 50.16 sec)
Rows matched: 1 Changed: 1 Warnings: 0

This indicates that when a data table has a write lock, read/write operations of other processes must be performed only after the read lock is released.

3. concurrent inserts

In principle, when a data table has a read lock, other processes cannot update the table. However, under certain conditions, the MyISAM table also supports concurrent query and insert operations.

The MyISAM storage engine has a system variable concurrent_insert, which is used to control its concurrent insertion behavior. the values can be 0, 1, or 2, respectively.
A. concurrent inserts are not allowed when concurrent_insert is set to 0.
B. When concurrent_insert is set to 1, if the MyISAM table has no holes (that is, the rows in the middle of the table are not deleted), MyISAM allows a process to read the table at the same time, another process inserts records from the end of the table. This is also the default setting of MySQL.
C. when concurrent_insert is set to 2, records can be inserted concurrently at the end of the table regardless of whether there are holes in the MyISAM table.

4. lock scheduling for MyISAM

MySQL considers that write requests are generally more important than read requests. Therefore, if a read/write request is performed simultaneously, MYSQL will give priority to write requests. This wayWhen a MyISAM table performs a large number of update operations (especially when an index exists in the updated field), the query operation is difficult to obtain the read lock., Resulting in query blocking.

We can adjust the scheduling behavior of MyISAM through some settings:

A. by specifying the startup parameter low-priority-updates, the MyISAM engine gives the read request priority by default.
B. run the SET LOW_PRIORITY_UPDATES = 1 Command to lower the priority of the update request sent by the connection.
C. reduce the priority of an INSERT, UPDATE, or DELETE statement by specifying the LOW_PRIORITY attribute.

The above three methods are either update-first or query-first methods. It should be noted that do not blindly set mysql to read first, because some query operations that require a long time to run will also starve the write process ". You can only decide which operation is preferred based on your actual situation. These methods do not fundamentally solve the problem of query and update at the same time.

In a mysql with a large amount of data and published data, we can use another method for optimization, that is, implementing load balancing through mysql master-slave (read/write) separation, in this way, you can avoid the first operation which may lead to congestion of the other operation.