MySQL lock mechanism (reprint)

A lock is a mechanism by which a computer coordinates multiple processes or threads concurrently accessing a resource. In a database, data is a resource 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 lock conflict is also an important factor that affects the performance of database concurrent access. From this perspective, locks are especially important and complex for databases. In this chapter we focus on the characteristics of the MySQL lock mechanism, common locking problems, and some methods or suggestions for solving the MySQL lock problem.

MySQL Lock overview
Compared with other databases, MySQL's locking 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 a table-level lock (table-level locking);
The BDB storage engine uses a page lock (page-level locking), but also supports table-level locks;
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 features of these 3 types of locks can be broadly summarized as follows.
Table-level Lock: Low overhead, lock fast, no deadlock, lock granularity, lock conflict is the highest probability, concurrency is the lowest.
Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock collisions, and the highest degree of concurrency.
Page locks: overhead and lock times are bounded between table and row locks, deadlock occurs, locking granularity bounds 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! From the lock point of view only: table-level locks are more suitable for queries that are primarily query-based, with few applications that update data by index criteria, such as Web applications, and row-level locks are more suitable for applications that have a large number of simultaneous updates by index criteria and concurrent queries, such as some online transaction processing (OLTP) systems. This is also mentioned in the book "Development article" to introduce table type selection. In the following sections we focus on MySQL table lock and InnoDB row lock problem, because BDB has been replaced by InnoDB, is about to become a history, there is no further discussion.

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 applications continue to improve transactional integrity and concurrency requirements, MySQL begins to develop a transaction-based storage engine, and then slowly comes the BDB storage engine that supports page locks and the InnoDB storage engine that supports row locks (the actual innodb is a separate company, Has now been acquired by Oracle Corporation). But MyISAM's table lock is still the most widely used lock type. This section describes the use of MyISAM table locks in detail. You can analyze table lock contention on the system by examining the table_locks_waited and table_locks_immediate state 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 the table_locks_waited is higher, then there is a more serious table-level lock contention condition. MySQL table-level lock lock mode the table-level lock for MySQL has two modes: table-Shared read-Lock and table-exclusive write-lock (tables write lock). The compatibility of the lock mode is shown in table 20-1.

It can be seen that the read operation on the MyISAM table does not block other users from reading requests to the same table, but blocks write requests to the same table, and writes to the MyISAM table will block the read and write operations of the same table by other users, the read and write operations of the MyISAM table, and the write operations are serial! According to the example shown in table 20-2, when a thread obtains a write lock on a table, only the thread holding the lock can update the table. Read and write operations on other threads wait until the lock is released.



How to add a table lock MyISAM before executing a query statement (SELECT), it automatically adds read locks to all tables involved, automatically writes locks to the table involved before performing the update operation (update, DELETE, INSERT, etc.), which does not require user intervention, so Users generally do not need to explicitly lock the MyISAM table directly with the Lock Table command. In the example in this book, explicit locking is basically for convenience only, not necessarily. To MyISAM table display lock, is generally to a certain extent to simulate transaction operations, to achieve a point in time multiple tables consistent read. For example, there is an order form orders, which records the total amount of each order, and there is also an order schedule Order_Detail, which records the amount of each product in each order subtotal subtotal, assuming we need to check whether the sum of these two tables matches, You may need to execute the following two Sql:java code

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

At this point, if you do not first lock the two tables, you can produce an incorrect result because the Order_Detail table may have changed during the execution of the first statement. Therefore, the correct approach 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;

The following two points are to be described in particular. The above example adds the "local" option to lock tables, which is to allow other users to insert records concurrently at the end of the table, in the case of MyISAM table concurrency insertion conditions, and further describes the concurrency insertion problem for MyISAM tables in later chapters. o when explicitly adding a table lock to a table with 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, the unlocked tables cannot be accessed, and if a read lock is added, only the query operation can be performed and the update operation cannot be performed. In fact, in the case of automatic lock-up, the MyISAM always get all the locks required by the SQL statement at once. This is why the MyISAM table does not appear deadlocked (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, which can query the records in the locked table, but updates or accesses other tables will prompt for errors; The other session can query the records in the table, but the update will show a lock wait.

When you use lock tables, not only do you need to lock all tables at once, but how many times the same table appears in the SQL statement, it will be blocked by the same alias as in the SQL statement, or it would be an error! Examples are described below. (1) Read lock on the actor table: Java code

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

(2) But access via aliases will prompt for errors: Java code

1. Mysql> Select A.first_name,a.last_name,b.first_name,b.last_name from actor A,actor b where a.first_name = B.first_ Name and A.first_name = ' Lisa ' and a.last_name = ' Tom ' and a.last_name <> b.last_name;
2. ERROR 1100 (HY000): Table ' A ' is not locked with LOCK TABLES

(3) You need to lock the 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) The query according to the alias can be executed correctly: Java code

1. mysql> select a.first_name,a.last_name,b.first_name,b.last_name from  Actor a,actor b where a.first_name = b.first_name and a.first_name  =  ' Lisa '  and a.last_name =  ' Tom '  and a.last_name <>  b.last_name;  
2. +------------+-----------+------------+-----------+  
3. |  first_name | last_name | first_name | last_name |  
4. +------------+-----------+------------+-----------+  
5. | lisa       | tom       |  lisa       | monroe    |  
6. +------------+-----------+------------+-----------+  
7. 1 row in set  (0.00 sec)   

Concurrent insertion (Concurrent inserts) the read and write of the MyISAM table mentioned above is serial, but this is in general. Under certain conditions, the MyISAM table also supports concurrency of query and insert operations.        The MyISAM storage engine has a system variable Concurrent_insert that is specifically designed to control the behavior of its concurrent insertions, with values of 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 are no holes in the MyISAM table (that is, rows in the middle of the table are not deleted), 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. 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. In the example shown in table 20-4, session_1 obtains a read local lock on a table that can query the table, but cannot update the table, and other threads (session_2), although the table cannot be deleted and updated, but it can Table for concurrent insert operations where there is no hole in the middle of the table.


You can use the Concurrency insertion feature of the MyISAM storage engine to resolve lock contention for the same table queries and insertions in your app. For example, setting the Concurrent_insert system variable to 2 always allows concurrent insertions, and by periodically executing the OPTIMIZE table statement in the system's idle time to defragment the space and reclaim the middle hole resulting from the deletion of the record. For a detailed introduction to the Optimize table statement, see the 18th chapter, "Two simple and useful optimization methods" section. MyISAM's lock Dispatch was previously said, the MyISAM storage engine read and write locks are mutually exclusive, 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 the startup parameter low-priority-updates, the MyISAM engine defaults to giving the read request priority rights.    reduces the priority of update requests made by this connection by executing command set Low_priority_updates=1.    reduces 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 the SQL statementA certain "decomposition" so 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.

MySQL lock mechanism (reprint)