Lock (MySQL article)-The MyISAM table lock

 Objective

A lock is a mechanism by which a computer coordinates multiple processes or threads concurrently accessing a resource, and in a database, data is a resource that is shared by many users in addition to contention for traditional computing resources such as CPU, RAM, I/O, and so on. How to guarantee the consistency and validity of data concurrent access is a problem that all databases must solve, and the conflict is also an important factor that affects the database concurrent access.

MySQL Table overview

Compared to other databases, the MySQL lock mechanism is relatively simple, and its most notable feature is that different storage engines support different locking mechanisms. For example, the MyISAM and memory storage engines use table-level locks (table-level locking), BDB storage engines use page locks (page-level locking), but table-level locks are also supported The InnoDB storage engine supports both row-level locks (row-level locking) and table-level locks, but row-level locks are used by default.
MySQL These 3 types of lock characteristics are broadly summarized as follows

• Table-level Lock: Low overhead, lock fast, no deadlock, lock granularity is high, the probability of collision is the highest, the concurrency is the lowest.
• Row-level locks: high overhead, locking slow, deadlock, minimum lock granularity, lowest probability of collision, highest concurrency.
• Page Lock: Overhead and lock time between table and row locks, deadlock, lock granularity between table and row locks, and concurrency is common.
  From the above characteristics can be seen, it is difficult to generally say which kind of lock better, only for the characteristics of the specific application which kind of lock more suitable! Table-level locks are more suitable for applications that are primarily query-based and have only a small number of updates to the index criteria, such as Web applications, and row-level locks are more suitable for applications that have a large number of concurrent updates by index criteria that are less different and have concurrent queries, such as some online transaction processing (OPTP) systems.

MyISAM table lock

The MyISAM storage engine only supports table locks, which is the only type of lock supported in MySQL's first few versions. As the application and the requirements of the integrity and concurrency of the things continue to improve, MySQL began to develop a thing-based storage engine, and then slowly appeared to support the page lock BDB storage engine and support row lock InnoDB storage engine. But MyISAM's table lock is still the most widely used lock type.

Query for table-level lock contention: You can analyze table lock contention on the system by examining the table_locks_waited and table_locks_immediate state variables:

Mysql> Show status like ' table% '; +----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| Table_locks_immediate      | 118 |   | table_locks_waited         | 0     | | Table_open_cache_hits      | 5     | | Table_open_cache_misses    | 3     | | Table_open_cache_overflows | 0     |+----------------------------+-------+5 rows in Set (0.00 sec)

If the value of the table_locks_waited is higher, then there is a more serious table-level lock contention condition.

The lock mode for MySQL table-level lock

There are two modes of table-level lock for MySQL: Table shared read lock (tables read lock) and table exclusive write locks (table write lock). The compatibility of the lock mode is shown in the following table.

current mode \ compatibility \ request Lock mode	None	Read Lock	Write lock
Read lock	Is	Is	Whether
Write lock	Is	Whether	Whether

It can be seen that the read operation on the MyISAM table will not block other users from reading requests to the same table, but will block write requests to the same table, and write to the MyISAM table will block the degree and write operations of other users on the same table, the read and write operations of the MyISAM table, and the writing operations are serial.

How to add a table lock

MyISAM in executing the query statement (SELECT) Money, will automatically give the design of all table read lock, in the update operation (update, delete, insert, etc.), will automatically write locks to the design table, this process does not require user intervention, therefore, Users generally do not need to explicitly lock the MyISAM table directly with the Lock Table command.
The explicit locking of the MyISAM table is generally done in order to simulate the transaction operation in a certain degree, and realize the consistent reading of multiple tables at a certain point in time. For example, there is an order form orders, where the total amount of an order is recorded, and there is an order schedule Order_Detail, which records the amount of each product in the order subtotal subtotal, assuming that the difference between the two tables is equal to the amount of the sum, You might need to execute the following two SQL statements:

Select SUM (total) from orders; Select SUM (subtotal) from Order_Detail;

　　

This is, if you do not first lock two tables, you may produce incorrect results, because the Order_Detail table may have changed during the execution of a statement, so the correct method should be:

Lock tables Orders read local, Order_Detail read Local;select sum (total) from Orders;select sum (subtotal) from Order_detai L Unlock tables;

　　

< Span class= "Hljs-keyword" >< Span class= "Hljs-keyword" >< Span class= "Hljs-keyword" >< Span class= "Hljs-keyword" >                  

• The above example adds the "local" option to lock tables, which allows other users to insert records concurrently at the end of the table if the MyISAM table concurrency Insert condition is satisfied;
• When you explicitly add a table lock to a table by using lock tables, all locks involving the table must be obtained at the same time, and MySQL does not support lock escalation, that is, after the lock tables is executed, only those tables that are explicitly locked can be accessed and the unlocked tables cannot be accessed, and if you add a read lock, You can only perform query operations and not perform update operations. In the case of automatic locking, MyISAM always gets all the SQL statements needed at once, which is why the MyISAM table does not deadlock (Deadlock free).

Concurrent insertion (Concurrent inserts)

The read and write of the MyISAM table mentioned earlier is serial, but this is in general, and under certain conditions, the MyISAM table also supports querying and the concurrent operation of tea like operations.
The MyISAM storage engine has a system variable Concurrent_insert that is specifically designed to control the behavior of its concurrent insertions, which can be 0, 1, or 2, respectively.

• Concurrent insertions are not allowed when Concurrent_insert is set to 0 o'clock.
• When Concurrent_insert is set to 1 o'clock, if there is no hole in the MyISAM table (that is, the row in the middle of the table is not deleted), MyISAM allows a process to read the table while another process inserts records from the end of the table, which is the default setting for MyISAM.
• When Concurrent_insert is set to 2 o'clock, the record is allowed to be inserted concurrently at the end of the table, regardless of whether there is an empty hole in the MyISAM table.

Lock scheduling for MyISAM

Because the read and write locks of the MyISAM storage engine are mutually exclusive, the read and write operations are serial. So one this morning to request a read lock for a MyISAM table, and another process to request the consent form of the write lock, how does MySQL handle it? The answer is that the write process gets the lock first. Not only that, when read request first to lock wait queue, write request Roar, write lock will also plug in before reading lock! This is because MySQL considers writing requests to be generally more important than reading 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 can cause query operations to be difficult to obtain read locks, which can be blocked forever. This situation is likely to become very bad! Fortunately, some settings can be used to adjust the scheduling behavior of MyISAM.

• By specifying a startup parameter of Low-priority-updates, the MyISAM engine defaults to giving the read request priority rights.
• By executing command set Low_priority_updates=1, the priority of the update request is reduced for that connection.
• 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 login 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 to achieve this, MySQL will temporarily reduce the priority of the write request, to the reading process must obtain the opportunity to lock.

Summary

The above shows that the write-priority scheduling mechanism brings the problems and solutions, here also to emphasize a bit: some long-running query operations, will also make the write process "starved"! 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, Is that each step of the query can be completed in a short time, thereby reducing the lock conflict. 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.

Lock (MySQL article)-MyISAM table lock