Analysis of lock table mechanism in MySQL query update

In order to better optimize MySQL in high concurrency, it is necessary to understand the locking table mechanism when MySQL query is updated.

I. Overview

MySQL has three levels of Lock: page level, table level, row level.
The MyISAM and memory storage engines use table-level locks (table-level locking), BDB storage engines use page locks (page-level locking), but table-level locks are also supported The InnoDB storage engine supports both row-level locks (row-level locking) and table-level locks, but row-level locks are used by default.

MySQL features of these 3 types of locks can be broadly summarized as follows:

Table-level Lock: Low overhead, lock fast, no deadlock, lock granularity, lock conflict is the highest probability, concurrency is the lowest.
Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock collisions, and the highest degree of concurrency.
Page locks: overhead and lock times are bounded between table and row locks, deadlock occurs, locking granularity bounds between table and row locks, and concurrency is common.

Second, MyISAM table lock

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

1, query table level lock contention situation

You can analyze table lock contention on the system by examining the table_locks_waited and table_locks_immediate state variables:

Mysql> Show status like ' table% ';
+-----------------------+----------+
| variable_name | Value |
+-----------------------+----------+
| Table_locks_immediate | 76939364 |
| table_locks_waited | 305089 |
+-----------------------+----------+
2 rows in Set (0.00 sec)

The higher value of the table_locks_waited indicates that there is a serious table-level lock contention condition.

2, the MySQL table-level lock Lock mode

There are two modes of table-level lock for MySQL: Table shared read lock (tables read lock) and table exclusive write locks (table write lock). MyISAM will automatically add read locks to all tables involved before executing the query statement (SELECT), and will automatically write locks to the table involved before performing the update operation (update, DELETE, INSERT, etc.).

As a result, the MyISAM table will be operated in the following situations:
A, read operations on the MyISAM table (read-lock), does not block other processes from reading requests to the same table, but blocks write requests to the same table. Write operations for other processes are performed only when the read lock is released.
b, write to the MyISAM table (write lock), will block the other process to the same table read and write operations, only when the write lock is released, the other process will perform read and write operations.

Here is an example to verify the above view. Data table Gz_phone has more than 2 million data, field id,phone,ua,day. The table is now simultaneously analyzed with multiple clients at the same time.
A, when I use client 1 for a relatively long time read operations, respectively, with client 2 for read and write operations:
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 |
+------+-------+
Rows in Set (0.01 sec)

mysql> Update gz_phone set phone= ' 11111111111′where id=1001;
Query OK, 0 rows affected (2 min 57.88 sec)
Rows matched:1 changed:0 warnings:0

Description When a data table has a read lock, the query actions of other processes can be executed immediately, but the update operation waits for the read lock to be released before it executes.

b, when using Client 1 for a longer time update operation, with the client 2,3 read and write operations:
CLIENT1:

mysql> Update gz_phone set phone= ' 11111111111′;
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 | 2007-12-19 |
| 2 | 2222 | sonyericssonk750c | 2007-12-19 |
| 3 | 2222 | MAUI WAP Browser | 2007-12-19 |
| 4 | 2222 | Nokia3108 | 2007-12-19 |
| 5 | 2222 | lenovo-i750 | 2007-12-19 |
| 6 | 2222 | bird_d636 | 2007-12-19 |
| 7 | 2222 | sonyericssons500c | 2007-12-19 |
| 8 | 2222 | samsung-sgh-e258 | 2007-12-19 |
| 9 | 2222 | nokian73-1 | 2007-12-19 |
| 10 | 2222 | Nokia2610 | 2007-12-19 |
+----+-------+-------------------+------------+
Rows in Set (2 min 58.56 sec)

Client3:

mysql> Update gz_phone set phone= ' 55555′where id=1;
Query OK, 1 row affected (3 min 50.16 sec)
Rows matched:1 changed:1 warnings:0

Indicates that when a data table has a write lock, the read and write operations of other processes are required to wait for the reading lock to be released before execution.

3. Concurrent Insertion

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

The MyISAM storage engine has a system variable Concurrent_insert that is specifically designed to control the behavior of its concurrent insertions, with values of 0, 1, or 2, respectively.
A, concurrent insertions are not allowed when Concurrent_insert is set to 0 o'clock.
B, when Concurrent_insert is set to 1 o'clock, if there are no holes in the MyISAM table (that is, rows in the middle of the table are not deleted), MyISAM allows one process to read the table while another process inserts records from the end of the table. This is also the default setting for MySQL.
C, when Concurrent_insert is set to 2 o'clock, the record is allowed to be inserted concurrently at the end of the table, regardless of whether there is an empty hole in the MyISAM table.

4, MyISAM lock scheduling

Because MySQL considers writing requests to be more important than read requests, MySQL will take precedence in writing if there are read and write requests at the same time. This way, when a large number of update operations are performed (especially in the case of an index in the updated field), the MyISAM table makes the query operation difficult to obtain a read lock, which causes the query to block.

There are some settings that we can use to adjust the scheduling behavior of MyISAM:

A, by specifying the startup parameter low-priority-updates, so that the MyISAM engine defaults to the read request to give priority to the right.
b, by executing the command set Low_priority_updates=1, the update request issued by this connection has a lower priority.
C, reduce the priority of the statement by specifying the Low_priority property of the Insert, UPDATE, DELETE statement.

The above 3 methods are either update priority or query first method. Here to illustrate is, do not blindly to the MySQL set to read first, because some long-running query operations, will also make the write process "starved". Only according to your actual situation, to decide which action to set priority. These methods still do not solve the problem of query and update fundamentally at the same time.

In a MySQL with large data volumes and published, we can also use another strategy to optimize the load balancing by MySQL master-slave (read-write) separation, which avoids the priority of which operations can lead to a blockage of another operation. Here is a space to illustrate MySQL's read-write separation technology.

Analysis of lock table mechanism in MySQL query update