Analysis of MySQL lock mechanism and usage

This article uses examples to illustrate the MySQL lock mechanism and usage. Share with you for your reference, the details are as follows:

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 table-level locks; the BDB storage engine uses page locks, but also supports table-level locks; the InnoDB storage engine supports both row-level locks and table-level locks, but uses row-level locks by default.

The characteristics of these three types of MySQL locks can be roughly summarized as follows:

(1) Table-level lock : low overhead, fast locking; no deadlock; large locking granularity, highest probability of lock conflict, lowest concurrency.

(2) Row-level lock : high overhead, slow locking; deadlock may occur; the locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.

(3) Page lock : The overhead and locking time are between table lock and row lock; deadlock may occur; the locking granularity is between table lock and row lock, and the concurrency is average.

From the perspective of locks alone, table-level locks are more suitable for applications that are query-oriented 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 of a small amount of different data according to index conditions and concurrent queries, such as some online transaction processing systems.

1. MyISAM table lock

1. Query table-level lock contention

show status like 'table%';

If the value of table_locks_waited is high, it means that there is serious table-level lock contention.

2. Lock mode of MySQL table-level lock

MySQL table-level locks have two modes: table shared read lock and table exclusive write lock.

When a session adds a read lock to a table, the session can only access the locked table and can only perform read operations; other sessions can read the table, but write operations will be blocked and need to wait for the lock to be released. When a session adds a write lock to a table, the session can only access the locked table and perform read and write operations. Other sessions' read and write operations on the table will be blocked and need to wait for the lock to be released.

The read and write operations of MyISAM tables, as well as the write operations themselves, are serial.

3. How to add table lock

Add read lock:

lock table tbl_name read;

Add write lock:

lock table tbl_name write;

Release the lock:

unlock tables;

Before executing a query statement, MyISAM will automatically add a read lock to all tables involved. Before performing an update operation, it will automatically add a write lock to the tables involved. This process does not require user intervention. Therefore, users generally do not need to explicitly lock the MyISAM table directly using the LOCK TABLE command. Explicitly locking a MyISAM table is generally done to simulate transaction operations to a certain extent and to achieve consistent reading of multiple tables at a certain point in time.

Note that when using LOCK TABLES, not only do you need to lock all the tables used at once, but you also need to lock the same table as many times as it appears in the SQL statement using the same alias in the SQL statement, otherwise an error will occur!

4. Concurrent Insert

The MyISAM storage engine has a system variable concurrent_insert, which is specifically used to control its concurrent insertion behavior. Its value can be 0, 1, or 2.

(1) When concurrent_insert is set to 0, concurrent inserts are 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 records from the end of the table. This is also the default setting for MySQL.

(3) When concurrent_insert is set to 2, concurrent insertion of records at the end of the MyISAM table is allowed regardless of whether there are holes in the table.

Just add the "local" option to the table lock command, that is, lock table tbl_name local read . If the concurrent insertion conditions of the MyISAM table are met, other users can concurrently insert records at the end of the table, but the update operation will be blocked, and the locked user cannot access the records concurrently inserted by other users.

5. MyISAM Lock Scheduling

When a writer and reader request a write lock and a read lock for the same MyISAM table at the same time, the writer gets the lock first. Not only that, even if the read request arrives at 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 also 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, and thus may be blocked forever.

Adjust the scheduling behavior of MyISAM through the following settings:

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

(2) By executing the command SET LOW_PRIORITY_UPDATES=1 , the priority of the update requests issued by the connection is lowered.

(3) Lower the priority of INSERT, UPDATE, or DELETE statements by specifying the LOW_PRIORITY attribute of the statement.

(4) 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 the write request to give the read process a chance to obtain the lock.

2. InnoDB lock problem

1. Query InnoDB row lock contention

show status like 'innodb_row_lock%';

If the values ​​of InnoDB_row_lock_waits and InnoDB_row_lock_time_avg are relatively high, it means that the lock contention is serious. In this case, you can set InnoDB Monitors to further observe the tables and data rows where lock conflicts occur, and analyze the causes of lock contention.

To open the monitor:

CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
Show innodb status\G;

To stop the monitor:

DROP TABLE innodb_monitor;

After turning on the monitor, the monitoring content will be recorded in the log every 15 seconds by default. If it is turned on for a long time, the .err file will become very large. Therefore, after confirming the cause of the problem, the user must remember to delete the monitoring table to turn off the monitor, or start the server with the "--console" option to turn off writing log files.

2. InnoDB row lock and locking method

InnoDB has two types of row locks: shared locks (S) and exclusive locks (X). In order to allow row locks and table locks to coexist and implement a multi-granularity lock mechanism, InnoDB also has two internally used intention locks: intention shared locks and intention exclusive locks. Both intention locks are table locks. A transaction must first obtain the intent lock for the corresponding table before locking a data row.

Intention locks are added automatically by InnoDB without user intervention. For UPDATE, DELETE, and INSERT statements, InnoDB automatically adds an exclusive lock (X) to the data set involved; for ordinary SELECT statements, InnoDB does not add any locks; transactions can explicitly add shared locks or exclusive locks to the record set through the following statements.

Set autocommit=0;

Shared lock(s):

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

Exclusive lock (X):

SELECT * FROM table_name WHERE ... FOR UPDATE

Release the lock:

unlock tables;

(Transaction will be implicitly committed)

When a transaction obtains a shared lock on a table, other transactions can query records in the table and also add shared locks to the records. When a transaction updates a table, if there is another transaction that has also added a shared lock to the table, it needs to wait for the lock to be released. If the other transaction also updates the table at the same time, it will cause a deadlock, the other transaction will exit, and the current transaction will complete the update operation. When a transaction obtains an exclusive lock on a table, other transactions can only query the records of the table, but cannot add shared locks or update records, and will experience waiting.

3. InnoDB row lock implementation method

InnoDB row locks are implemented by locking the index items on the index. This row lock implementation feature of InnoDB means:

(1) InnoDB uses row-level locks only when retrieving data through index conditions. Otherwise, InnoDB uses table locks.

(2) Since MySQL row locks are locked against indexes, not records, lock conflicts will occur when accessing records in different rows using the same index key.

(3) When a table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, whether using a primary key index, a unique index, or a normal index, InnoDB will use row locks to lock the data. (Although a different index is used, you still need to wait if the record has been locked by another session.)

(4) Even if an index field is used in the condition, whether to use the index to retrieve data is determined by MySQL by judging the cost of different execution plans. If MySQL believes that a full table scan is more efficient, such as for some very small tables, it will not use the index. In this case, InnoDB will use a table lock instead of a row lock.

4. Gap Lock

When using range conditions to retrieve data, InnoDB will also lock records whose key values ​​are within the condition range but do not exist. This lock is called a "gap lock." InnoDB uses gap locks to prevent phantom reads and to meet the needs of recovery and replication. However, this locking mechanism will block concurrent insertion of key values ​​within the qualifying range, causing serious lock waits, so you should try to avoid using range conditions to retrieve data.

In addition to using gap locks when locking through range conditions, InnoDB will also use gap locks if an equality condition is used to request a lock on a non-existent record!

5. Impact of recovery and replication requirements on InnoDB locking mechanism

MySQL uses BINLOG to record successful INSERT, UPDATE, DELETE and other SQL statements that update data, thereby realizing the recovery and master-slave replication of the MySQL database. The MySQL recovery mechanism (replication is actually continuous recovery based on BINLOG in the slave MySQL) has the following characteristics:

(1) MySQL recovery is at the SQL statement level, that is, re-executing the SQL statements in BINLOG.

(2) MySQL's Binlog records transactions in the order in which they are submitted, and recovery is also performed in this order.

Therefore, MySQL's recovery and replication requirements for the locking mechanism are: before a transaction is committed, other concurrent transactions cannot insert any records that meet their locking conditions, that is, phantom reads are not allowed.

In addition, for general select statements, MySQL uses multi-version data to achieve consistency and does not require any locks. However, for SQL statements such as " insert into target_tab select * from source_tab where ... " and " create table new_tab ...select ... From source_tab where ... ", the user did not make any updates to source_tab, but MySQL made special processing for such SQL statements and added a shared lock to source_tab. This is because, if the lock is not added, if another transaction updates source_tab and commits it first during the execution of this SQL statement, then in the BINLOG, the update operation will be located before the SQL statement. If this BINLOG is used to recover the database, the recovery result will not match the actual application logic, and replication will lead to inconsistency between the master and slave databases. This is because the data actually inserted into target_tab or new_tab is the data before source_tab is updated by another transaction, but BINLOG records the data before the update and then the execution of the select...insert... statement. If the SELECT in the above statement is a range condition, InnoDB will also add a gap lock to the source table. Therefore, this kind of SQL statement will block concurrent updates to the original table and should be avoided as much as possible.

6. Situations and precautions for using InnoDB table locks

For InnoDB tables, row-level locks should be used in most cases, but table-level locks can also be considered in some special transactions, mainly in the following two situations:

(1) The transaction needs to update most or all of the data, and the table is relatively large. If the default row lock is used, not only will the transaction execution efficiency be low, but it may also cause other transactions to wait for a long time and cause lock conflicts. In this case, you can consider using a table lock to increase the execution speed of the transaction.

(2) The transaction involves multiple tables and is relatively complex. It is likely to cause deadlock and result in a large number of transaction rollbacks. In this case, you can also consider locking the tables involved in the transaction at one time to avoid deadlock and reduce the database overhead caused by transaction rollback.

In addition, when using table locks in InnoDB, you need to pay attention to the following two points:

(1) Although LOCK TABLES can be used to add table-level locks to InnoDB, table locks are not managed by the InnoDB storage engine layer, but by its upper layer, MySQL Server. Only when autocommit=0 and innodb_table_locks=1 (default settings) can the InnoDB layer know about the table locks added by MySQL, and MySQL Server can also perceive the row locks added by InnoDB. In this case, InnoDB can automatically identify deadlocks involving table-level locks; otherwise, InnoDB will not be able to automatically detect and handle such deadlocks.

(2) When using LOCK TABLES to lock an InnoDB table, be careful to set AUTOCOMMIT to 0, otherwise MySQL will not lock the table. Do not use UNLOCK TABLES to release the table lock before the transaction ends, because UNLOCK TABLES will implicitly commit the transaction. COMMIT or ROLLBACK cannot release the table-level lock added by LOCK TABLES . UNLOCK TABLES must be used to release the table lock.

7. About deadlock

MyISAM table locks are deadlock free because MyISAM always obtains all the locks it needs at once, either satisfying all of them or waiting, so there will be no deadlock. But in InnoDB, except for transactions consisting of a single SQL statement, locks are acquired gradually, which makes it possible for deadlock to occur in InnoDB.

After a deadlock occurs, InnoDB can generally detect it automatically and make one transaction release the lock and roll back, while the other transaction acquires the lock and continues to complete the transaction. However, when external locks or table locks are involved, InnoDB cannot automatically detect deadlocks. This needs to be solved by setting the lock wait timeout parameter innodb_lock_wait_timeout .

Generally speaking, deadlocks are a problem of application design. Most deadlocks can be avoided by adjusting business processes, database object design, transaction size, and SQL statements for accessing the database. The following are some common methods to avoid deadlock through examples.

(1) In an application, if different programs access multiple tables concurrently, you should try to agree to access the tables in the same order. This can greatly reduce the chance of deadlock.

(2) When the program processes data in batches, if the data is sorted in advance to ensure that each thread processes records in a fixed order, the possibility of deadlock can be greatly reduced.

(3) In a transaction, if you want to update a record, you should directly apply for a lock of a sufficient level, that is, an exclusive lock, rather than applying for a shared lock first and then applying for an exclusive lock when updating. This is because when a user applies for an exclusive lock, other transactions may have already obtained a shared lock for the same record, causing a lock conflict or even a deadlock.

(4) At the REPEATABLE-READ isolation level, if two threads simultaneously use SELECT...FOR UPDATE to add an exclusive lock to the same condition record, both threads will successfully lock the record if there is no record that meets the condition. The program finds that the record does not exist yet, so it tries to insert a new record. If two threads do this, a deadlock will occur. In this case, changing the isolation level to READ COMMITTED can avoid the problem.

(5) When the isolation level is READ COMMITTED , if both threads first execute SELECT...FOR UPDATE to determine whether there are records that meet the conditions, if not, the records are inserted. At this time, only one thread can insert successfully, and the other thread will wait for the lock. When the first thread submits, the second thread will make an error due to the primary key, but although this thread makes an error, it will obtain an exclusive lock! At this time, if a third thread applies for an exclusive lock again, a deadlock will occur. In this case, you can directly perform the insert operation and then catch the primary key duplicate exception, or when encountering a primary key duplicate error, always execute ROLLBACK to release the acquired exclusive lock.

Readers who are interested in more MySQL-related content can check out the following topics: "Summary of MySQL Database Lock-Related Skills", "Summary of MySQL Stored Procedure Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills" and "Summary of MySQL Transaction Operation Skills".

I hope this article will be helpful to everyone's MySQL database design.