Shulou(Shulou.com)05/31 Report--

This article mainly introduces "Overview of mysql table locking mechanism". In daily operation, I believe many people have doubts about the overview of mysql table locking mechanism. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "Overview of mysql table locking mechanism". Next, please follow the editor to study!

I. Overview

MySQL has three levels of locks: page level, table level, and row level.

The MyISAM and MEMORY storage engines use table-level locks (table-level locking); the BDB storage engines use page locks (page-level

Locking), but also supports table-level locks; the InnoDB storage engine supports both row-level locks and table-level locks, but row-level locks are used by default.

The characteristics of the three locks of MySQL can be roughly summarized as follows:

Table-level lock: low overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict and the lowest concurrency.

Row-level locks: expensive and slow to add locks; deadlocks occur; locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of concurrency is the highest.

Page lock: the overhead and locking time are between table lock and row lock; deadlocks occur; lock granularity is between table lock and row lock, and the concurrency is general.

II. MyISAM table lock

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

1. Query table-level lock contention

Table locking contention on the system can be analyzed 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 | |

+-+

The value of 2 rows in set (0.00 sec) Table_locks_waited is relatively high, indicating that there is serious table-level lock contention.

2. Lock mode of MySQL table-level lock

There are two modes of MySQL table-level lock: table shared read lock (Table Read Lock) and table exclusive write lock (Table Write).

Lock). MyISAM automatically locks all tables involved before executing the query statement (SELECT), and automatically locks the tables involved before performing update operations (UPDATE, DELETE, INSERT, etc.).

So when you manipulate the MyISAM table, you will have the following situations:

A. Read operations on the MyISAM table (with read locks) will not block other processes' read requests to the same table, but will block write requests to the same table. Only when the read lock is released will the write operations of other processes be performed.

B, the write operation to the MyISAM table (with a write lock) will block the read and write operations of other processes to the same table, and the read and write operations of other processes will be performed only when the write lock is released.

Here are examples to verify the above point of view. There are more than two million data in the data table gz_phone, the field id,phone,ua,day. Now multiple clients are used to analyze 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='11111111111'where id=1001

Query OK, 0 rows affected (2 min 57.88 sec)

Rows matched: 1 Changed: 0 Warnings: 0

It indicates that when the data table has a read lock, the query operation of other processes can be executed immediately, but the update operation will not be performed until the read lock is released.

B. When client 1 is used to update for a long time, client 2p3 is used to read and write respectively:

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 |

+-- +-+

10 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 the data table has a write lock, the read and write operations of other processes will not be performed until the read lock is released.

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 the concurrency of query and insert operations.

The MyISAM storage engine has a system variable, concurrent_insert, specifically designed to control its concurrent insertion behavior, which can be 0, 1, or 2, respectively.

When concurrent_insert is set to 0, concurrent insertion is not allowed.

B, when concurrent_insert is set to 1, if there are no holes in the MyISAM table (that is, there are no deleted rows in the middle of the table), 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, records are allowed to be inserted concurrently at the end of the table, regardless of whether there are holes in the MyISAM table.

4. Lock scheduling of MyISAM

Because MySQL believes that write requests are generally more important than read requests, MYSQL will give priority to write operations if there are read and write requests at the same time. In this way, when the MyISAM table carries out a large number of update operations (especially if there is an index in the updated field), it will make it difficult for the query operation 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 priority to the read request by default.

B. reduce the priority of update requests issued by the connection by executing the command SET LOW_PRIORITY_UPDATES=1.

C. Lower the priority of the statement by specifying the LOW_PRIORITY attribute of the INSERT, UPDATE, and DELETE statement.

The above three methods are either update-first or query-first. What I want to say here is that do not blindly set mysql to read first, because some long-running query operations will also make the writing process "starve to death". It is only based on your actual situation that you decide which operation to set first. These methods still 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, we can also use another strategy to optimize, that is, to achieve load balancing through the separation of mysql master and slave (read and write), so as to avoid which operation is preferred, which may lead to congestion of another operation. Here is a space to illustrate the read-write separation technology of mysql.

At this point, the study of "Overview of mysql table locking mechanism" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.