MySQL InnoDB Storage engine lock, mysqlinnodb

Source: Internet
Author: User

MySQL InnoDB Storage engine lock, mysqlinnodb
Concept:
A lock is used to manage concurrent access to shared files. Innodb locks the database at the row level. However, the innodb Storage engine uses locks in multiple places inside the database to allow concurrent access to different resources. For example, to operate on the LRU list in the buffer pool, delete, add, and move the elements in the LRU list, a lock must be involved to ensure consistency. The MyISAM engine is a table lock, while InnoDB provides consistent non-locking read and row-level locks without any additional overhead.
Lock
Table-level locking)
The entire table is locked by the customer. Based on the lock type, other customers cannot insert records into the table, or even read data from the table is restricted by the default lock levels of MyISAM and MEMORY. In some cases, InnoDB will also upgrade to table-level locks.
Row-level locking)
Only the row currently used by the thread is locked, and other rows are available to other threads as InnoDB default row locks. It is implemented based on the index data structure, rather than the ORACLE lock, it is block-based. InnoDB also upgrades to table-level locks, full/full index updates, and autoinc locks.
Page-level locking)
Lock Certain Row sets (called pages) in the table. Locking rows is only feasible for locking the original thread. If another thread wants to write data to these rows, it must wait until the lock is released. However, the rows on other pages can still use the BDB slave page lock.
Lock and latch
Latch is called a locks (lightweight locks) because it requires a very short lock time. If the duration is long, the application performance will be very poor. The InnoDB Storage engine can also be divided into mutex (mutex) and rwlock (read/write lock ). The objective is to ensure the correctness of the critical resources operated by concurrent threads, and there is usually no Deadlock Detection mechanism. Latch can be viewed by running the show engine innodb mutex command.
We can see that the column Type always displays InnoDB, and the column Name displays latch information and the number of lines in the source code. The OS _waits shown in the column Status indicates the number of waits of the operating system.
The lock object is a transaction used to lock objects in the database, such as tables, pages, and rows. In addition, the lock object is released only after the transaction commit or rollback (the release time may be different at different transaction isolation levels ). There is a deadlock mechanism. The difference between the two rules is as follows:

Features:
InnoDB locks the index. This feature means that InnoDB uses row-level locks only when data is retrieved through index conditions. Otherwise, InnoDB uses table locks.
Lock type:
There are two standard row-level locks:
SHARE lock (s lock): allows a transaction to read a row to prevent other transactions FROM obtaining exclusive locks for the same dataset. SELECT * FROM table_name WHERE... lock IN SHARE MODE
Exclusive lock (X lock): allows transactions that obtain exclusive locks to UPDATE data, and prevents other transactions FROM obtaining shared read locks and exclusive locks FOR the same dataset. SELECT * FROM table_name WHERE... FOR UPDATE
The InnoDB Storage engine supports Intention Locks and is relatively concise in design. There are two types of Intention Locks used internally, both of which are table Locks. (The intention lock is automatically added by InnoDB)
Intention share lock (IS): the transaction intends to add a row share lock to the data row. The transaction must first obtain the IS lock of the table before adding a share lock to the data row.
Intention exclusive lock (IX): the transaction intends to apply a row exclusive lock to the data row. The transaction must first obtain the IX lock of the table before applying an exclusive lock to the data row.
Table-level intention locks are compatible with row-level locks, for example:

View lock
Before InnoDB1.0, you can view lock requests in the current database only through show engine innodb status (viewed in the transactions line) or show full processlist. However, in the information_schema architecture, innodb_trx, innodb_locks, and innodb_lock_waits are added to record the locks in the current database.
Fields in the three tables are described as follows:
Consistent nonlocking read)
Consistent non-locked read refers to the InnoDB Storage engine reading data of the row in the database at the current execution time through multi_versioning. If the read row is performing the delete or update operation, the read operation will not wait for the row to be locked to be released. On the contrary, the InnoDB Storage engine reads a row's snapshot data (the historical version of the current row's data ). Snapshot data refers to the data of previous versions of the row, which is completed through the undo segment. Undo is used to roll back data in transactions. Therefore, there is no additional overhead for snapshot data. Additionally, no locks are required to read snapshot data. Consistent non-locked read is the default read method of the InnoDB Storage engine (the read will not occupy or wait for the table lock ). However, at different transaction isolation levels, the read method is different, not all transaction isolation levels adopt non-locked consistent read. Even if non-locked consistent reads are used, the definition formats of snapshot data are also different. Under the transaction isolation level read committed (RC) and repeatable read (RR, the default transaction isolation level of the InnoDB Storage engine), the InnoDB Storage engine uses non-locked consistent reads. However, the definition of snapshot data is different. At the RC transaction isolation level, for snapshot data, non-consistent reads always read the latest snapshot data of the locked row. Under the RR transaction isolation and unbinding, for snapshot data, non-consistent read always reads the row data version at the beginning of the transaction.
Consistent read locking
As mentioned above, the select Operation of the InnoDB Storage engine uses consistent non-locked read in the default transaction isolation level (RR) mode. However, in some cases, you need to explicitly lock database read operations to ensure data logic consistency. The InnoDB Storage engine supports two consistent locking read operations for select statements:
Select... for update: Apply the X lock to the read row record. Other transactions cannot apply any lock to the row.
Select... lock in share mode: Apply the S lock to the read row record. Other things can apply the S lock to the row, but if the X lock is applied, the row will be blocked.
Auto-growth and lock
In the memory structure of the InnoDB Storage engine, each table containing auto-increment values has an auto-increment counter ). If the insert operation is performed on a table containing a self-increasing counter, the counter is initialized. Run the following statement to obtain the counter value: select max (auto_inc_col) from t for update. The insert operation adds 1 to the auto-increment Column Based on the counter value. This implementation method is called AUTO-INC Locking, which is a special table lock mechanism. To improve the insert performance, the lock is not released after a transaction is completed, instead, the SQL statement inserted into the auto-growth value is released immediately. AUTO-INC Locking improves the efficiency of concurrent inserts to some extent, but there are still some performance problems. First, the Concurrent Insertion performance of Columns with auto-increment values is poor, and the transaction must wait for the completion of the previous insert (no need to wait for the completion of the transaction ). Second, insertion of large data volumes of insert... select will affect the performance of insertion, because the insertion in another transaction will be blocked. Since MySQL 5.1.22, the InnoDB Storage engine provides a lightweight auto-increment implementation mechanism for mutex, which greatly improves the performance of auto-increment value insertion. The innodb_autoinc_lock_mode parameter is used to control the auto-increment mode (default value: 1 ). Auto-increment inserts are classified as follows:
The parameter value of innodb_autoinc_lock_mode and its impact on auto-growth include:
The MyISAM storage engine is a table lock, so you don't need to consider the issue of Concurrent Insertion for auto-growth. Note: In the InnoDB Storage engine, the column with auto-increment values must be an index and the first column of the index. If it is not the first column, mySQL throws an exception. Exception
Foreign keys and locks
Foreign keys are mainly used for integrity constraints. In the InnoDB Storage engine, if a foreign key column is not explicitly indexed, the InnoDB Storage engine automatically adds an index to it to avoid table locks. To insert or update foreign key values, you must first query the records in the parent table. The select Operation on the parent table is not a consistent non-locked read method, this will cause data inconsistency, so select... lock in share mode, that is, an S lock is automatically applied to the parent table.
Lock Problems
Dirty read
Dirty reading refers to reading dirty data (uncommitted data ). One transaction (A) reads the modified but not committed data from another transaction (B) and operates on the basis of the data. At this time, if transaction B rolls back, the data read by transaction A is invalid. Non-conformity.
First, the default RR of the transaction isolation level is changed to RU. The preceding example shows that two select operations in session B have different results, the two records are not submitted in session A, which generates dirty reads. It can be concluded that the condition for dirty reads is that the transaction isolation level is RU.
Unrepeatable read cannot be repeated
Transaction (A) reads the changed data that has been committed by another transaction (B) and does not comply with isolation. The difference between non-repeated read and dirty read is that dirty read reads read uncommitted data, while non-repeated reads Read committed data. First, adjust the transaction isolation level to RC, and then perform the following example:
Phantom read
Transaction (A) reads the new data committed by another transaction (B), which is not isolated.
Lock range (lock algorithm ):
1. record Lock: the Lock on a single Record always locks the index Record. If no index is set when the InnoDB Storage engine table is created, in this case, the InnoDB Storage engine uses an implicit primary key for locking.
2. Gap Lock: A Gap Lock that locks a range, but does not contain the record itself.
3. Next-key Lock: Lock a range and its own Record Lock + Gap Lock to prevent phantom read.
Primary Key Index and unique secondary index = record lock
Non-unique secondary index = next-key lock
Blocking
The Compatibility relationship between different locks. In some moments, the locks in a transaction need to wait for the locks in another transaction to release the resources it occupies, which is blocking. Blocking is not a bad thing. It is to ensure that transactions can run concurrently and normally. In the InnoDB Storage engine, the innodb_lock_wait_timeout parameter is used to dynamically control the waiting time (50 seconds by default ), innodb_rollback_on_timeout is used to statically set the release and roll back the transaction when waiting for timeout (OFF by default, which means no rollback ).
Deadlock

A deadlock occurs when two or more transactions compete for resources during execution. One of the simplest ways to solve the deadlock is timeout, that is, when two transactions wait for each other, when a wait time exceeds a set threshold, one of the transactions will be rolled back, another pending transaction can continue. In the InnoDB Storage engine, the innodb_lock_wait_timeout parameter is used to set the timeout time. However, if the transactions with timeout account for a large proportion of weights and transaction Operations update many rows and occupy a large number of undo logs, the FIFO method is not suitable, because the transaction rollback time may be much longer than the time occupied by another transaction. Therefore, in addition to the timeout mechanism, the current database generally uses the wait-for graph (wait graph) method for Deadlock Detection. The database must report the following two types of information: a. Linked List of locked information; B. linked list of pending transactions. The linked list can be used to construct a graph. If a loop exists in this graph, a deadlock exists. In wait-for graph, transactions are nodes in the graph.

It can be found that there is a loop (1, 2), so there is a deadlock. At this time, the InnoDB Storage engine chooses to roll back the transactions with the smallest undo volume. The Deadlock Detection of wait-for graph is usually implemented using a depth-first algorithm.
Note:
1. s x is ix, indicating whether the lock IS mutually exclusive or compatible with other locks.
2. record lock, gap lock, and NEXT-key lock indicate the range of the locks to be loaded, whether the row RECORD itself, row RECORD + GAP, or even a larger range.
Important conclusion:
1. Any locks on secondary indexes or non-index columns should be traced back to the primary key, and a lock should be applied to the primary key.
2. Before S or X locks on any leaf node, an IS or IX lock will be added to the root node, that IS, the table-level IS and IX locks.
3. All the locks on the primary key index are record locks.
4. the locks on the secondary index of the unique index are also record locks.
5. The lock on the secondary index with non-unique indexes is next-key lock.
6. There will be no separate gap lock, but will only be associated with the record lock.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.