Detailed explanation of the principle of mysql lock mechanism (1)

Lock is a mechanism for computers to coordinate multiple processes or threads to access a resource concurrently. In a database, in addition to traditional competition for computing resources (such as CPU, RAM, I/O, etc.), data is also a resource shared by many users. How to ensure the consistency and effectiveness of concurrent access to data is a problem that all databases must solve. Lock conflicts are also an important factor affecting the performance of concurrent access to databases. From this perspective, locks are particularly important and complex for databases. In this chapter, we focus on the characteristics of the MySQL lock mechanism, common lock problems, and some methods or suggestions for solving MySQL lock problems.
Mysql uses many such lock mechanisms, such as row locks, table locks, read locks, write locks, etc., which are all locked before operations. These locks are collectively called pessimistic locks.

MySQL lock overview

Compared with other databases, MySQL’s lock mechanism is relatively simple, and its most significant feature is different storage The engine supports different locking mechanisms. For example, the MyISAM and MEMORY storage engines use table-level locking (table-level locking); the BDB storage engine uses Page lock (page-level locking), but also supports table-level lock; InnoDB storage engine supports both row-level lock (row-level locking) and Table-level locking is supported, but row-level locking is used by default.
Table-level lock: Low overhead, fast locking; no deadlocks; large locking granularity, the highest probability of lock conflicts, and the lowest concurrency.
Row-level lock: high overhead, slow locking; deadlock will occur; the locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.
Page lock: The overhead and locking time are between table locks and row locks; deadlocks will occur; the locking granularity is between table locks and row locks, and the concurrency is average
It can be seen from the above characteristics that it is difficult to say which lock is better in general. We can only say which lock is more suitable based on the characteristics of specific applications! Just from the perspective of locking: table-level locks are more suitable for applications that are mainly query-based and only update a small amount of data according to index conditions, such as web applications; while row-level locks are more suitable for applications that have a large number of concurrent updates based on index conditions and a small number of different data. Data, and concurrent query applications, such as some online transaction processing (OLTP) systems.

MyISAM table lock

MySQL table-level lock has two modes: table shared read lock (Table Read Lock) and table exclusive write Lock (Table Write Lock).
Read operations on the MyISAM table will not block other users' read requests for the same table, but will block write requests on the same table; write operations on the MyISAM table will block other users' read and write requests on the same table. Write operations; the read operations and write operations of the MyISAM table, as well as the write operations, are serial! According to the example shown in Table 20-2, we can know that when a thread obtains a write lock on a table, only the thread holding the lock can update the table. Read and write operations from other threads will wait until the lock is released.

MyISAM storage engine's write lock blocking read example:
When a thread obtains a write lock on a table, only the thread holding the lock can update the table. Read and write operations from other threads will wait until the lock is released.

MyISAM storage engine's read lock blocking write example:
A session uses the LOCK TABLE command to add a read lock to the table film_text. This session can query the locked table. records, but updating or accessing other tables will prompt errors; at the same time, another session can query the records in the table, but updates will cause lock waits.

How to add table lock

MyISAM will automatically add read locks to all tables involved before executing the query statement (SELECT). Before executing update operations (UPDATE, DELETE, INSERT, etc.), it will automatically add write locks to the tables involved. This process User intervention is not required, so users generally do not need to directly use the LOCK TABLE command to explicitly lock the MyISAM table. In the examples, explicit locking is mostly for demonstration purposes and is not required.
Display locking of MyISAM tables is generally done to simulate transaction operations to a certain extent and achieve consistent reading of multiple tables at a certain point in time. For example, there is an order table orders, which records the total amount of each order, and there is also an order detail table order_detail, which records the subtotal amount of each product of each order. Suppose we need to check the two tables. To check whether the total amounts match, you may need to execute the following two SQLs:

Select sum(total) from orders;
Select sum(subtotal) from order_detail;

At this time, if the two tables are not locked first, wrong results may occur, because during the execution of the first statement, The order_detail table may have changed. Therefore, the correct method should be:

Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;

The following two points should be noted in particular:
1. The above example adds the "local" option to LOCK TABLES, which is used to satisfy the concurrency of MyISAM tables. In the case of insertion conditions, other users are allowed to concurrently insert records at the end of the table. The issue of concurrent insertion in the MyISAM table will be further introduced later.
2. When using LOCK TABLES to explicitly add table locks to a table, all locks involved in the table must be obtained at the same time, and MySQL does not support lock upgrades. That is to say, after executing LOCK TABLES, you can only access the explicitly locked tables, but not the unlocked tables; at the same time, if you add a read lock, you can only perform query operations, but not update operations. . In fact, this is basically the case in the case of automatic locking. MyISAM always obtains all the locks required by the SQL statement at once. This is why MyISAM tables will not be deadlocked (Deadlock Free).

When using LOCK TABLES, not only do you need to lock all tables used at once, but also how many times the same table appears in the SQL statement, you must lock it through the same alias as the SQL statement, otherwise Something can go wrong! An example is given below.

(1) Obtain a read lock on the actor table:

mysql> lock table actor read; 
Query OK, 0 rows affected (0.00 sec)

(2) However, accessing through aliases will prompt an error:

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 = &#39;Lisa&#39; and a.last_name = &#39;Tom&#39; and a.last_name <> b.last_name;
ERROR 1100 (HY000): Table ‘a’ was not locked with LOCK TABLES

(3) The aliases need to be locked separately:

mysql> lock table actor as a read,actor as b read;
Query OK, 0 rows affected (0.00 sec)

(4) The query according to the alias can be executed correctly:

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 = &#39;Lisa&#39; and a.last_name = &#39;Tom&#39; and a.last_name <> b.last_name;
+————+———–+————+———–+ 
| first_name | last_name | first_name | last_name | 
+————+———–+————+———–+ 
| Lisa | Tom | LISA | MONROE | 
+————+———–+————+———–+ 
1 row in set (0.00 sec)

Query table-level lock contention

Table lock contention on the system can be analyzed by checking the table_locks_waited and table_locks_immediate status variables:

mysql> show status like 'table%';
1Variable_name | Value 
Table_locks_immediate | 2979 
Table_locks_waited | 0 
2 rows in set (0.00 sec))

If the value of Table_locks_waited is relatively high, it indicates that there is a serious table-level lock contention.

Concurrent Inserts

As mentioned above, the reading and writing of MyISAM tables are serial, but this is Overall. Under certain conditions, MyISAM tables also support concurrent query and insert operations.
MyISAM storage engine has a system variable concurrent_insert, which is specially used to control its concurrent insertion behavior. Its value can be 0, 1 or 2 respectively.

1. When concurrent_insert is set to 0, concurrent insertion is not allowed.

2. When concurrent_insert is set to 1, if there are no holes in the MyISAM table (that is, there are no deleted rows in the middle of the table), MyISAM allows one process to read the table while another process inserts from the end of the table. Record. This is also the default setting for MySQL.

3. When concurrent_insert is set to 2, records are allowed to be inserted concurrently at the end of the table regardless of whether there are holes in the MyISAM table.

In the following example, session_1 obtains the READ LOCAL lock of a table. This thread can query the table, but cannot update the table; other threads (session_2), although they cannot query the table, Delete and update operations are performed, but concurrent insert operations can be performed on the table. It is assumed that there are no holes in the table.

As mentioned above, the reading and writing of MyISAM tables are serial, but this is generally speaking. Under certain conditions, MyISAM tables also support concurrent query and insert operations.
MyISAM storage engine has a system variable concurrent_insert, which is specially used to control its concurrent insertion behavior. Its value can be 0, 1 or 2 respectively.

When concurrent_insert is set to 0, concurrent inserts are not allowed. When concurrent_insert is set to 1, if there are no holes in the MyISAM table (that is, there are no deleted rows in the middle of the table), 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, records are allowed to be inserted concurrently at the end of the table regardless of whether there are holes in the MyISAM table.

In the following example, session_1 obtains the READ LOCAL lock of a table. This thread can query the table, but cannot update the table; other threads (session_2), although they cannot query the table, Delete and update operations are performed, but concurrent insert operations can be performed on the table. It is assumed that there are no holes in the table.

MyISAM storage engine's read and write (INSERT) concurrency example:

You can use the concurrent insertion feature of the MyISAM storage engine to solve the problem of inserting the same table in the application. Lock contention for queries and inserts. For example, setting the concurrent_insert system variable to 2 always allows concurrent insertion; at the same time, the OPTIMIZE TABLE statement is regularly executed during the system's idle period to defragment the space and recover the intermediate holes caused by deleting records.

MyISAM lock scheduling

#As mentioned earlier, the read lock and write lock of the MyISAM storage engine are mutually exclusive, and the read and write operations are Serial. So, if one process requests a read lock on a MyISAM table, and at the same time another process also requests a write lock on the same table, how does MySQL handle it? The answer is that the writing process acquires the lock first. Not only that, even if the read request arrives in the lock waiting queue first and the write request arrives later, the write lock will be inserted before the read lock request! This is because MySQL considers write requests to be generally more important than read requests. This is 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 block forever. This situation can get really bad sometimes! Fortunately, we can adjust the scheduling behavior of MyISAM through some settings.

1. By specifying the startup parameter low-priority-updates, the MyISAM engine gives priority to read requests by default.

2. Reduce the priority of update requests issued by this connection by executing the command SET LOW_PRIORITY_UPDATES=1.

3. Reduce the priority of the statement by specifying the LOW_PRIORITY attribute of the INSERT, UPDATE, and DELETE statement.

Although the above three methods are either update first or query first, they can still be used to solve the serious problem of read lock waiting in applications where query is relatively important (such as user login system).
In addition, MySQL also provides a compromise method to adjust read and write conflicts, that is, setting an appropriate value for the system parameter max_write_lock_count. When the read lock of a table reaches this value, MySQL will temporarily cancel the write request. The priority is lowered, giving the reading process a certain chance to obtain the lock.

The problems and solutions caused by the write priority scheduling mechanism have been discussed above. It should also be emphasized here: some query operations that require long running times will also "starve" the writing process! Therefore, you should try to avoid long-running query operations in your application. Don't always try to use a SELECT statement to solve the problem, because this seemingly clever SQL statement is often more complex and takes longer to execute. When possible, SQL statements can be "decomposed" to a certain extent by using intermediate tables and other measures so that each step of the query can be completed in a shorter time, thus reducing lock conflicts. If complex queries are unavoidable, they should be scheduled to be executed during idle periods of the database. For example, some regular statistics can be scheduled to be executed at night.

I will explain InnoDB locks to you later.

For more related questions, please visit the PHP Chinese website: Mysql video tutorial

The above is the detailed content of Detailed explanation of the principle of mysql lock mechanism (1). For more information, please follow other related articles on the PHP Chinese website!