About MySQL MyISAM table concurrency

Lock scheduling for MyISAM

The read and write locks of the MyISAM storage engine are mutually exclusive, and the read and write operations are serial. So, one process requests a read lock on a MyISAM table, and another process requests a write lock on the same table, how does mysql handle it? The answer is that the write process gets the lock first. Not only that, even if the read request goes to the lock waiting queue, and the write request is reached, the write lock is inserted before the read lock request! This is because MySQL considers writing requests to be generally more important than reading requests. This is why the MyISAM table is not well suited for applications with a large number of update operations and query operations, because a large number of update operations can cause query operations to be difficult to obtain read locks, which can be blocked forever. This situation can sometimes get very bad! Fortunately we can adjust the scheduling behavior of MyISAM by some settings.

By specifying a startup parameter of Low-priority-updates, the MyISAM engine defaults to giving the read request priority rights.

By executing the command set Low_priority_updates=1, the priority for update requests made by this connection is reduced.

Reduce the priority of the statement by specifying the Low_priority property of the Insert, UPDATE, DELETE statement.

Although the above 3 methods are either update first or query first method, but still can use it to solve the query of relatively important applications (such as user logon system), read lock waiting for serious problems.

In addition, MySQL also provides a compromise method to adjust the read-write conflict, that is, to set the system parameter Max_write_lock_count a suitable value, when a table read lock reached this value, MySQL temporarily reduced the priority of the write request, to the reading process must obtain the opportunity to lock.

Above has discussed the writing priority scheduling mechanism brings the problem and the solution. It is also important to emphasize that some long-running query operations can also cause the write process to "starve"! 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, often more complex, the execution time is longer, where possible, by using the intermediate table and other measures to do a certain "decomposition" of SQL statements, Enables each step of the query to be completed in a shorter time, thereby reducing lock collisions. If complex queries are unavoidable, you should try to schedule them during the database idle time, such as some periodic statistics that can be scheduled for nightly execution.

With these basics in view, here are a few easy-to-ignore configuration options: MyISAM

Concurrent_insert:

Generally speaking, the read and write operation is serial in MyISAM, but when querying and inserting the same table, in order to reduce the frequency of the lock competition, according to the Concurrent_insert setting, MyISAM can process the query and insert in parallel:

Concurrent insert functionality is not allowed when concurrent_insert=0.

When Concurrent_insert=1 is enabled, concurrent inserts are allowed for tables that do not have holes, and the new data is at the end of the data file (default).

When concurrent_insert=2, it is allowed to insert at the end of the data file, regardless of whether the table has holes.

In this case, setting Concurrent_insert to 2 is a good deal, and as a result of the resulting file fragments, you can use Optimize table syntax optimization regularly.

Max_write_lock_count:

By default, the write operation takes precedence over the read operation, even if the read request is sent, and then the write request is sent, and the write request is processed first, and then the read request is processed. This creates a problem: once I make several write requests, it blocks all of the read requests until the write request is processed and the read request is processed. You might consider using Max_write_lock_count at this point:

Max_write_lock_count=1

With this setting, when the system handles a write operation, it pauses the write operation, giving the opportunity to read the execution of the operation.

Low-priority-updates:

We can also more simply, lower the priority of the write operation, give the read operation a higher priority.

Low-priority-updates=1

In general, concurrent_insert=2 is definitely recommended, as for max_write_lock_count=1 and Low-priority-updates=1, depending on the situation, if you can reduce the priority of the write operation, Use Low-priority-updates=1, otherwise use max_write_lock_count=1.

About MySQL MyISAM table concurrency