MySQL MyISAM Table lock for concurrency control

A lock is a mechanism by a computer to coordinate multiple processes or threads to concurrently access a certain resource. In databases, in addition to traditional computing resources (such as CPU, ram, and I/O), data is also a resource shared by many users. How to ensure consistency and effectiveness of Concurrent Data Access is a problem that must be solved by all databases. Lock conflicts are also an important factor affecting the concurrent database access performance. From this perspective, locks are especially important and more complex for databases. This chapter focuses on the features of the MySQL lock mechanism, common lock problems, and some methods or suggestions to solve the MySQL lock problem.

MySQL lock Overview
Compared with other databases, MySQL locks are relatively simple. The most notable feature is that different storage engines support different locks.
For example, the MyISAM and memory storage engines use table-level locking );
The bdb storage engine uses page-level locking, but also supports table-level locks;
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.
From the above features, it is difficult to say in general what kind of lock is better, but what kind of lock is more suitable for specific application characteristics! From the perspective of the lock only: Table-level locks are more suitable for queries, and only a few applications that update data based on index conditions, such as web applications; row-level locks are more suitable for applications with a large number of concurrent updates to a small amount of different data based on index conditions and concurrent queries, such as some online transaction processing (OLTP) systems. This is also mentioned in the "Development" section of this book when introducing the selection of table types. In the following sections, we will focus on MySQL table locks and InnoDB row locks. Since bdb has been replaced by InnoDB, it is about to become a history and will not be further discussed here.

 

MyISAM Table lock

The MyISAM storage engine only supports table locks, which is the only supported lock type in MySQL versions. With the increasing requirements of applications on transaction integrity and concurrency, MySQL began to develop a transaction-based storage engine, later, we gradually saw the support for page locks for the bdb storage engine and the InnoDB Storage engine that supports row locks (InnoDB is actually a separate company and has now been acquired by Oracle ). However, MyISAM Table locks are still the most widely used lock types. This section describes how to use the MyISAM Table lock. You can analyze the table lock contention on the system by checking the table_locks_waited and table_locks_immediate status variables:

Java Code

1. Mysql> show status like 'table % ';
2. + ----------------------- + ------- +
3. | Variable_name | value |
4. + ----------------------- + ------- +
5. | Table_locks_immediate | 2979 |
6. | Table_locks_waited | 0 |
7. + ----------------------- + ------- +

If the value of table_locks_waited is relatively high, it indicates a serious table-Level Lock contention. MySQL table-Level Lock mode MySQL table-Level Lock has two modes: Table read lock and table write lock ). The compatibility of lock modes is shown in Table 20-1.

It can be seen that the read operation on the MyISAM table will not block the read requests from other users to the same table, but will block the write requests to the same table; write operations on the MyISAM table, the read and write operations on the same table by other users are blocked. The read and write operations on the MyISAM table are serialized! According to the example shown in Table 20-2, when a thread acquires a write lock on a table, only the thread holding the lock can update the table. Read and Write operations of other threads will wait until the lock is released.

 

How to add a table lock before executing a query statement (select), MyISAM will automatically apply a read lock to all the tables involved, before performing an update operation (Update, delete, insert, etc, the write lock is automatically applied to the involved table. This process does not require user intervention. Therefore, you generally do not need to directly use the lock table command to explicitly lock the MyISAM table. In the example of this book, explicit locking is basically for convenience, not required. Lock the MyISAM table, which is generally used to simulate transaction operations to a certain extent to achieve consistent reading of multiple tables at a certain time point. For example, there is an order table orders, which records the total amount of each order, and there is an order list order_detail, where records the amount of each product in each order subtotal, if we need to check whether the total amount of the two tables is consistent, we may need to execute the following two SQL statements:

Java code

1. Select sum (total) from orders;
2. Select sum (subtotal) from order_detail;

In this case, if you do not lock two tables first, an error may occur because the order_detail table may have changed during the execution of the first statement. Therefore, the correct method should be:

Java code

1. Lock tables orders read local, order_detail read local;
2. Select sum (total) from orders;
3. Select sum (subtotal) from order_detail;
4. Unlock tables;

Note the following two points. In the preceding example, the "local" option is added when you lock tables. Its function is to allow other users to insert records concurrently at the end of the table when the MyISAM table's Concurrent Insertion conditions are met, for more information about Concurrent Insertion of MyISAM tables, see the following sections. When using lock tables to explicitly add a table lock to a table, the slave must simultaneously obtain all the locks related to the table, and MySQL does not support lock upgrade. That is to say, after you execute lock tables, you can only access the explicitly locked tables, but not the unlocked tables. if you add a read lock, you can only perform the query operation, you cannot perform the update operation. In fact, this is basically the case when automatic locking is applied. MyISAM always obtains all the locks required by SQL statements at a time. This is exactly why the MyISAM table does not have a deadlock (deadlock free. In the example shown in Table 20-3, a session uses the lock table command to add a read lock to the table film_text. This session can query records in the locked table, but an error will be prompted when updating or accessing other tables. At the same time, another session can query the records in the table, but the update will cause a lock wait.

When you use lock tables, you not only need to lock all the tables used at one time, but also the number of times that the same table appears in an SQL statement, the number of times it is locked using the same alias as the SQL statement. Otherwise, an error will occur! The following is an example. (1) obtain the read lock for the actor table:

Java code

1. Mysql> lock table actor read;
2. Query OK, 0 rows affected (0.00 Sec)

(2) but an error will be prompted during access through the alias:

Java code

1. Mysql> select. first_name,. last_name, B. first_name, B. last_name from actor a, actor B where. first_name = B. first_name and. first_name = 'lisa 'and. last_name = 'Tom 'and. last_name <> B. last_name;
2. Error 1100 (hy000): Table 'A' was not locked with lock tables

(3) You need to lock aliases separately:

Java code

1. Mysql> lock table actor as a read, actor as B read;
2. Query OK, 0 rows affected (0.00 Sec)

(4) query by alias can be correctly executed:

Java code

2. Mysql> select. first_name,. last_name, B. first_name, B. last_name from actor a, actor B where. first_name = B. first_name and. first_name = 'lisa 'and. last_name = 'Tom 'and. last_name <> B. last_name;
4. + ------------ + ----------- +
6. | First_name | last_name |
8. + ------------ + ----------- +
10. | Lisa | Tom | Lisa | Monroe |
12. + ------------ + ----------- +
14. 1 row in SET (0.00 Sec)

Concurrent inserts (concurrent inserts) The read and write operations on MyISAM tables are serial, but this is general. Under certain conditions, MyISAM tables also support concurrent queries and insert operations. The MyISAM storage engine has a system variable concurrent_insert, which is used to control its Concurrent Insertion behavior. The values can be 0, 1, or 2, respectively. Concurrent inserts are not allowed when concurrent_insert is set to 0. When concurrent_insert is set to 1, if the MyISAM table does not have holes (that is, the rows in the middle of the table are not deleted), MyISAM allows a process to read the table at the same time, another process inserts records from the end of the table. This is also the default setting of MySQL. When concurrent_insert is set to 2, records can be inserted concurrently at the end of the table regardless of whether there are holes in the MyISAM table. In the example shown in table 20-4, session_1 acquires a read local lock for the table. This thread can query the table but cannot update the table; for other threads (session_2), although the table cannot be deleted or updated, the table can be inserted concurrently. It is assumed that there is no holes in the table.

The Concurrent Insertion feature of the MyISAM storage engine can be used to solve the contention for the same table query and insertion locks. For example, if you set the concurrent_insert system variable to 2, concurrent insertion is always allowed. At the same time, you can regularly execute the optimize TABLE statement in the idle time of the system to sort out space fragments, reclaim the middle hole generated by deleting the record. For more information about optimize table statements, see section 18th "two simple and practical optimization methods. As mentioned earlier, 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 the reason why MyISAM tables are not suitable for applications with a large number of update operations and query operations, because a large number of update operations will make it difficult for query operations to obtain read locks, which 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 clause 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.