Mysql lock Table Mechanism Analysis

To 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.
The InnoDB Storage engine supports both row-level locking and table-level locking. However, row-level locking is 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) Table_locks_waited has a high value, which indicates that there is a serious table-Level Lock contention.
 
2. MySQL table-Level Lock mode
 
There are two Table-level locks in 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 (read lock) on the MyISAM table will not block read requests from other processes to the same table, but will block 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 (with write locks) will block read and write operations on the same table by other processes. Only when the write lock is released, to read and write other processes.
 
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. In this way, when the MyISAM Table performs a large number of update operations (especially when indexes exist in the updated fields), it is difficult for the query operation to obtain the read lock, leading to 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. The following sections describe the mysql read/write splitting technology.