Detailed explanation of the lock structure in MySQL

Mysql supports 3 types of lock structures

• Table-level lock, low overhead, fast locking, no deadlock, large locking granularity, high probability of conflict, and lowest concurrency
• Row-level locks, low overhead, slow locking, deadlock, small locking granularity, lowest conflict probability, highest concurrency
• Page locks, overhead and locking are between table locks and row locks, deadlocks may occur, the lock granularity is based on tables and rows, and concurrency is generally
  

InnoDB lock issue

There are two biggest differences between InnoDB and MyISAM: one is that it supports transactions (TRANSACTION); the other is the use of row-level locks.
There are many differences between row-level locks and table-level locks. In addition, the introduction of transactions also brings some new problems.

InnoDB row lock mode and locking method

InnoDB implements the following two types of row locks.

• Shared lock(s): Allows one transaction to read a row, preventing other transactions from obtaining exclusive locks on the same data set.
• Exclusive lock (X): Allows transactions that obtain exclusive locks to update data, preventing other transactions from obtaining shared read locks and exclusive write locks on the same data set.

In addition, 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 Locks), both of which are table locks.

• Intentional shared lock (IS): The transaction intends to assign a shared lock to a data row. The transaction must first obtain the IS lock of the table before adding a shared lock to a data row.
• Intentional exclusive lock (IX): The transaction intends to add an exclusive lock to a data row. The transaction must first obtain the IX lock of the table before adding an exclusive lock to a data row.

InnoDB row locks are implemented through index items on the index, which is different from MySQL and Oracle, which implements it by locking the corresponding data rows in the data. This row lock implementation feature of InnoDB means that InnoDB will use row-level locks only when retrieving data through index conditions, otherwise, InnoDB will use table locks!

Next-Key Locks

When we retrieve data using range conditions instead of equality conditions and request shared or exclusive locks, InnoDB will lock the index items of existing data that meet the conditions; for records whose key values ​​are within the condition range but do not exist, it is called a "gap", and InnoDB will also lock this "gap". This locking mechanism is not the so-called gap lock (Next-Key lock).
For example, if there are only 101 records in the emp table, and the empid values ​​are 1, 2, ..., 100, 101 respectively, the following SQL:

SELECT * FROM emp WHERE empid > 100 FOR UPDATE

This is a range condition search. InnoDB will not only lock the records with an empid value of 101 that meet the conditions, but also lock the "gaps" where the empid value is greater than 101 (these records do not exist).
The purpose of InnoDB using gap locks is, on the one hand, to prevent phantom reads to meet the requirements of related isolation levels. For the above example, if gap locks are not used, if other transactions insert any records with empid greater than 100, then if this transaction executes the above statement again, phantom reads will occur; on the other hand, it is to meet the needs of its recovery and replication. About the impact of its recovery and replication mechanisms, and the use of gap locks by InnoDB at different isolation levels.
Obviously, when using range conditions to retrieve and lock records, the InnoDB locking mechanism will block concurrent insertion of key values ​​that meet the conditions, which often causes serious lock waits. Therefore, in actual development, especially for applications with a lot of concurrent inserts, we should try our best to optimize the business logic, use equality conditions to access and update data, and avoid using range conditions.

When to use table locks?

For InnoDB tables, row-level locks should be used in most cases, because transactions and row locks are often the reasons why we choose InnoDB tables. However, in some special transactions, you may also consider using table-level locks.

The first situation is: 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 long lock waits and lock conflicts for other transactions. In this case, you can consider using table locks to speed up the execution of the transaction.

The second situation is that the transaction involves multiple tables, which is relatively complex and may cause deadlocks and rollback of a large number of transactions. 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.

Of course, there should not be too many of these two types of transactions in the application, otherwise, you should consider using the MyISAM table.
In InnoDB, pay attention to the following two points when using table locks.

(1) Although LOCK TALBES can be used to add table-level locks to InnoDB, it must be noted that table locks are not managed by the InnoDB storage engine layer, but by the upper layer MySQL Server. Only when autocommit=0 and innodb_table_lock=1 (default settings) can the InnoDB layer know the table locks added by MySQL and MySQL Server can 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 LOCAK TABLES to lock InnoDB, be careful to set AUTOCOMMIT to 0, otherwise MySQL will not lock the table. Do not use UNLOCAK TABLES to release the table lock before the end of the transaction, because UNLOCK TABLES will implicitly commit the transaction. COMMIT or ROLLBACK cannot release the table-level lock added by LOCAK TABLES. You must use UNLOCK TABLES to release the table lock. The correct method is shown in the following statement.
For example, if you need to write to table t1 and read from table t, you can do it as follows:

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and here];
COMMIT;
UNLOCK TABLES;

Deadlock

In InnoDB, except for transactions consisting of a single SQL statement, locks are acquired gradually, which makes it possible for InnoDB to deadlock.
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 locks are involved, InnoDB cannot completely automatically detect deadlocks, which needs to be solved by setting the lock wait timeout parameter innodb_lock_wait_timeout. It should be noted that this parameter is not only used to solve the deadlock problem. In the case of high concurrent access, if a large number of transactions are suspended because they cannot immediately obtain the required locks, it will occupy a large amount of computer resources, causing serious performance problems and even dragging down the database. We can avoid this situation by setting an appropriate lock wait timeout threshold.

Generally speaking, deadlocks are application design issues and can mostly be avoided by adjusting business processes, database object design, transaction size, and SQL statements used to access the database. The following examples introduce several common methods of deadlock.

(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. If two sessions access the two tables in different orders, the chance of deadlock is very high! But if access is done in the same order, deadlock can be avoided.

(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 sufficient level, that is, an exclusive lock, instead of applying for a shared lock first and then applying for an exclusive lock when updating, which may even lead to a deadlock.

(4) At the REPEATEABLE-READ isolation level, if two threads simultaneously use SELECT ... ROR UPDATE to add an exclusive lock to the same condition record, both threads will succeed in locking if no record matches 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 COMMITED, if both threads first execute SELECT...FOR UPDATE, they determine whether there are records that meet the conditions. If not, they insert the records. 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 has 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.

Differences between MyISAM and InnoDB

For MyISAM table locks, there are mainly the following points

(1) Shared read locks (S) are compatible with each other, but shared read locks (S) and exclusive write locks (X), as well as exclusive write locks (X) are mutually exclusive, which means that reading and writing are serial.
(2) Under certain conditions, MyISAM allows queries and inserts to be executed concurrently. We can use this to solve the lock contention problem for the same table and insert in the application.
(3) The default lock scheduling mechanism of MyISAM is write priority, which is not necessarily suitable for all applications. Users can adjust the contention of read-write locks by setting the LOW_PRIPORITY_UPDATES parameter or specifying the LOW_PRIORITY option in INSERT, UPDATE, and DELETE statements.
(4) Since the table lock has a large locking granularity and reading and writing are serial, if there are many update operations, MyISAM tables may experience serious lock waits. You can consider using InnoDB tables to reduce lock conflicts.

For InnoDB tables, there are the following main points

(1) InnoDB's marketing is based on indexes. If data is not accessed through an index, InnoDB will use a table lock.
(2) InnoDB gap lock mechanism and the reason why InnoDB uses gap lock.
(3) At different isolation levels, InnoDB's locking mechanism and consistent read strategy are different.
(4) MySQL recovery and replication also have a significant impact on InnoDB's locking mechanism and consistent read strategy.
(5) Lock conflicts and even deadlocks are difficult to avoid completely.

After understanding the lock characteristics of InnoDB, users can reduce lock conflicts and deadlocks through design and SQL adjustment measures, including:

• Use a lower isolation level if possible
  
• Carefully design indexes and use indexes to access data as much as possible to make locking more precise and reduce the chance of lock conflicts.
• Choose a reasonable transaction size. Small transactions are less likely to have lock conflicts.
• When explicitly locking a recordset, it is best to request a sufficient level of locks at one time. For example, if you want to modify data, it is best to apply for an exclusive lock directly, rather than applying for a shared lock first and then requesting an exclusive lock when modifying, which can easily cause deadlock.
• When different programs access a set of tables, they should try to agree to access the tables in the same order. For a table, try to access the rows in a fixed order. This can greatly reduce the chance of deadlock.
• Try to access data using equal conditions to avoid the impact of gap locks on concurrent insertions.
• Do not apply for more lock levels than are actually needed; do not display locks when querying unless necessary.
• For some specific transactions, table locks can be used to increase processing speed or reduce the possibility of deadlock.

MySql optimistic locking pessimistic locking

Pessimistic Lock

The characteristic of pessimistic locking is that the lock is acquired first, and then the business operation is performed. In other words, it is "pessimistic" to believe that acquiring the lock is very likely to fail, so it is necessary to ensure that the lock is acquired successfully before performing business operations. The so-called "one lock, two checks, and three updates" usually refers to the use of pessimistic locking. Generally speaking, pessimistic locking on a database requires support from the database itself, that is, pessimistic locking is implemented through the commonly used select ... for update operation. When the database executes select for update, it acquires the row lock of the selected data row. Therefore, if other concurrently executed select for update attempts to select the same row, they will be excluded (need to wait for the row lock to be released), thus achieving the locking effect. The row lock acquired by select for update will be automatically released at the end of the current transaction, so it must be used within a transaction.

One thing that needs to be noted here is that different databases have different implementations and support for select for update. For example, Oracle supports select for update no wait, which means that if the lock cannot be obtained, an error is reported immediately instead of waiting. MySQL does not have the no wait option. Another problem with MySQL is that all scanned rows will be locked during the execution of the select for update statement, which can easily cause problems. Therefore, if you use pessimistic locking in MySQL, make sure to use the index instead of a full table scan.

Optimistic Locking

The characteristic of optimistic locking is to perform business operations first and not take the lock unless it is absolutely necessary. That is, we "optimistically" believe that getting the lock will most likely be successful, so we only need to get the lock at the last step of the business operation that actually updates the data.

The implementation of optimistic locking on the database is completely logical and does not require special support from the database. The general approach is to add a version number or timestamp to the data that needs to be locked, and then implement it as follows:

1. SELECT data AS old_data, version AS old_version FROM …;
2. Perform business operations based on the acquired data to obtain new_data and new_version
3. UPDATE SET data = new_data, version = new_version WHERE version = old_version
if (updated row > 0) {
  // Optimistic lock acquisition is successful, operation completed} else {
  // Optimistic lock acquisition failed, rollback and retry}

Concurrency is not allowed when updating the same row within the database. That is, each time the database executes an update statement, it acquires the write lock of the updated row and does not release it until the row is successfully updated. Therefore, before the business operation is performed, the current version number of the data that needs to be locked is obtained, and then when the data is actually updated, the version number is compared again to confirm that it is the same as the previously obtained one, and the version number is updated to confirm that no concurrent modifications have occurred in between. If the update fails, it can be considered that the old version of the data has been modified concurrently and no longer exists. At this time, it is considered that the lock acquisition has failed, and the entire business operation needs to be rolled back and the entire process can be retried as needed.

Optimistic locking has lower overhead than pessimistic locking when there is no lock acquisition failure, but the rollback overhead is relatively large once a failure occurs. Therefore, it is suitable for scenarios where the probability of lock acquisition failure is relatively small, and can improve the system concurrency performance. Optimistic locking is also applicable to some special scenarios, such as when it is impossible to maintain a connection with the database during business operations, and other places where pessimistic locking cannot be applied.

The above is a detailed explanation of the lock structure in MySQL. For more information about MySQL lock structure, please pay attention to other related articles on 123WORDPRESS.COM!