MySQL MyISAM Table concurrency

Lock scheduling of MyISAM

The read and write locks of the MyISAM storage engine are mutually exclusive, and read/write operations are sequential. So, one process requests the read lock of a MyISAM table, and the other process also requests the write lock of the same table. How does MySQL handle this? The answer is that the write process obtains the lock first. Not only that, even if the Read Request first goes to the lock wait queue, after the write request arrives, the write lock will be inserted before the read lock request! This is because MySQL considers that write requests are generally more important than read requests. This is precisely why the MyISAM table is not suitable for a large number of update operations and query operation applications, because a large number of update operations will make query operations difficult to obtain read locks, and thus may be blocked forever. This situation may sometimes become very bad! Fortunately, we can adjust the scheduling behavior of MyISAM through some settings.

By specifying the startup parameter low-priority-updates, the MyISAM engine gives the Read Request priority by default.

Run the SET LOW_PRIORITY_UPDATES = 1 command to lower the priority of the update request sent by the connection.

You can specify the LOW_PRIORITY attribute of an INSERT, UPDATE, or DELETE statement to reduce the priority of the statement.

Although the above three methods are both update-first or query-first methods, they can still be used to query applications that are relatively important (such as user logon to the system, read lock wait is a serious problem.

In addition, MySQL also provides a compromise to adjust read/write conflicts, that is, to set an appropriate value for the system parameter max_write_lock_count. When the read lock of a table reaches this value, mySQL temporarily lowers the priority of write requests, giving the read process a certain chance to get the lock.

The Problems and Solutions brought about by the write priority scheduling mechanism have been discussed above. It should also be emphasized that some query operations that require a long time to run will also starve the write process "! Therefore, the application should try to avoid long-running query operations. Do not always want to use a SELECT statement to solve the problem, because this seemingly clever SQL statement is often complicated, the execution takes a long time. If possible, you can use an intermediate table or other measures to "break down" the SQL statement, so that each step of the query can be completed in a short time, this reduces lock conflicts. If complex queries are unavoidable, they should be executed during idle time periods. For example, some regular statistics can be executed at night.

After learning about these basic knowledge, let's talk about Several configuration options that are easily overlooked by MyISAM:

Concurrent_insert:

In general, read/write operations in MyISAM are serial, but when querying and inserting the same table, to reduce the frequency of lock competition, according to the settings of concurrent_insert, myISAM can process queries and inserts in parallel:

When concurrent_insert = 0, concurrent insertion is not allowed.

When concurrent_insert = 1, concurrent insertion is allowed for tables without holes. The new data is at the end of the data file (default ).

When concurrent_insert = 2, concurrent insertion at the end of the data file is allowed regardless of whether the table has holes.

In this case, it is very cost-effective to set concurrent_insert to 2. As for the resulting file fragments, You can regularly use the optimize table syntax to OPTIMIZE them.

Max_write_lock_count:

By default, the write operation has a higher priority than the read operation. Even if the read request is sent first and then the write request is sent, the write request is processed first, then process the Read Request. This causes a problem: Once I send several write requests, all read requests will be blocked until all write requests are processed. Max_write_lock_count:

Max_write_lock_count = 1

With this setting, when the system processes a write operation, the write operation will be suspended to give the read operation a chance.

Low-priority-updates:

We can also simply reduce the write operation Priority and give the read operation a higher priority.

Low-priority-updates = 1

In summary, concurrent_insert = 2 is absolutely recommended. As for max_write_lock_count = 1 and low-priority-updates = 1, it depends on the situation. If you can reduce the priority of write operations, use low-priority-updates = 1; otherwise, use max_write_lock_count = 1.