MySQLInnoDB storage engine lock

MySQLInnoDB storage engine lock
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: bytes + 3b/bytes + 3aGjyOe5 + Bytes/i1xMrNt8Who8/bytes + Bytes/7NXVyv2 + bytes + 3 aOsuMPKtc/weight + HVuZG/logs/ 7NXVyv2 + 3bG + large/s1dXK/large + large/7NXVyv2 + large + bKudPDt8fL + large + KOsttTT2r/s1dXK/large/small/large/ s1dXK/samples + 7QwtK7t92/7NXVyv2 + samples/7NXVyv2 + samples/samples + m/9 s/samples/Uyr212LbUyv2 + 3b/Samples + response/response + KOsxuTL + 8 rCzu + response/ijrMbky/response + NDQsuXI67LZ1/response + jna00ndi58/response + export/export/i7 + export/b7dwb + 1xLLlyOu74dOwz + export + bm4bmp wcvsu9bwx + HBV 7y2u6wz4sg/tcTX1NT2s6TKtc/Wu/export =" http://www.2cto.com/uploadfile/Collfiles/20141220/20141220092504215.jpg "Alt =" \ ">
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.