Analysis on the lock Table Mechanism During mysql query update

Welcome to the Linux community forum and interact with 2 million technical staff. In order to better optimize mysql in high concurrency situations, it is necessary to understand the lock table mechanism when mysql queries updates. I. Overview MySQL has three lock levels: page-level, table-level, and row-level. MyISAM and MEMORY storage engines use table-level locks (table-levelloc

Welcome to the Linux community forum, and interact with 2 million technical staff> in order to better optimize mysql in high concurrency, it is necessary to understand the lock table mechanism when mysql queries updates. I. Overview MySQL has three lock levels: page-level, table-level, and row-level. MyISAM and MEMORY storage engines use table-level locks (table-level loc)

Welcome to the Linux community forum and interact with 2 million technicians>

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, 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 (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.

[1] [2]