Concept:
Locks are used to manage concurrent access to shared files. InnoDB locks the database at the row level. However, the InnoDB storage engine uses locks in many other places within the database, allowing concurrent access to different resources. For example, manipulate the LRU list in the buffer pool, delete, add, move elements in the LRU list, and in order to ensure consistency, there must be a lock involved. The MyISAM engine is a table lock, and InnoDB provides a consistent, non-locked read, row-level lock with no additional overhead associated with row-level locks.
Lock
Table-level Locking (table-level lock)
The entire table is locked by the customer. Depending on the type of lock, other customers cannot insert records into the table, or even read data from it is restricted MyISAM, memory default lock level, InnoDB will also be upgraded to table-level lock
Row-level Locking (Row level lock)
Only the rows currently used by the thread are locked, and the other rows are available for other threads InnoDB default row-level locks. is based on an indexed data structure, rather than an Oracle lock, which is based on block. InnoDB will also be upgraded to table-level locks, full-table/full-index updates, request Autoinc locks, etc.
Page-level Locking (Page level lock)
Locking some rowset (called page) in a table, the locked row is only feasible for locking the original thread. If another thread wants to write data to these lines, it must wait until the lock is released. However, rows from other pages can still use the BDB default page-level lock
Lock and latch
Latch are called latches (lightweight locks) because they require a very short time to lock. If it lasts for a long time, the performance of the application will be very poor. In the InnoDB storage engine, it can be divided into mutexes (mutexes) and Rwlock (read-write locks). The purpose is to ensure the correctness of critical resources for concurrent threading operations, and there is usually no mechanism for deadlock detection. Latch can be viewed by ordering the show engine InnoDB mutex.
The InnoDB is always displayed by the column type, the column name shows the latch information and the number of rows in the source, and the os_waits displayed in the column status indicates the number of times the operating system waits.
The object of lock is a transaction, which is used to lock objects in the database, such as tables, pages, and rows. Also, the general lock object is released only after a transaction commit or rollback (different transaction isolation levels may not be released at the same time). There is a deadlock mechanism. The difference between the two is as follows:
Characteristics:
The InnoDB is implemented by locking the index entries on the index to achieve a row lock. This feature also means that InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise innodb will use table locks.
Type of Lock:
There are two standard row-level locks:
Shared Lock (S Lock): Allows a transaction to read one line, preventing other transactions from acquiring an exclusive lock on the same data set. SELECT * FROM table_name WHERE ... LOCK in SHARE MODE
Exclusive lock (X Lock): Allows transactions that obtain exclusive locks to update data, preventing other transactions from acquiring shared read and exclusive locks of the same data set. SELECT * FROM table_name WHERE ... For UPDATE
The InnoDB storage engine supports intent locks and is designed to be more concise, divided into two internal use intent locks (Intention Locks), both of which are table locks. (Intent lock is automatically added by InnoDB)
Intent shared Lock (IS): The transaction intends to add a row of shared locks to the data row, and the transaction must obtain the IS lock of the table before sharing it with a data row.
Intent exclusive Lock (ix): The transaction intends to add an exclusive lock to the data row, and the transaction must obtain an IX lock on the table before it is added to the exclusive lock on the data row.
Table-Level Intent locks are compatible with row-level locks such as:
View of the Lock
You can only view requests for locks in the current library by show engine InnoDB status (view in Transactions line) or show full processlist before the InnoDB1.0 version. But after this, in the INFORMATION_SCHEMA architecture, new Innodb_trx, Innodb_locks, and innodb_lock_waits three tables record the locks in the current library.
The field descriptions for the three tables are as follows
Consistent non-locking read (consistent nonlocking)
Consistent, non-locking read refers to the way that the InnoDB storage engine reads data from rows in the current execution time database through row versioning (multi_versioning). If the read row is performing a delete or update operation, then the read operation does not wait for the row lock to be released. Instead, the InnoDB storage engine reads a snapshot of the row (the historical version of the current row data). Snapshot data refers to the previous version of the data of the row, which is done through the undo segment. Undo is used to roll back the data in the transaction, so the snapshot data itself is without additional overhead. Also, reading snapshot data does not need to be locked. Consistent non-lock read is the default read for the InnoDB storage engine (the read does not occupy and wait for locks on the table). However, under different transaction isolation levels, the reads are different, not at each transaction isolation level, with a non-locking, consistent read. Even if you are using non-locking consistent reads, the definition format for snapshot data varies. Under the transaction ISOLATION level read COMMITTED (RC) and repeatable read (Rr,innodb storage engine's default transaction isolation level), the INNODB storage engine uses non-locking consistent reads. However, the definition of snapshot data goes differently. Under the RC transaction isolation level, for snapshot data, non-conforming reads always read the latest snapshot data for the locked row. For snapshot data, non-conforming reads always read the row data version at the beginning of the transaction, under the RR transaction isolation unbind.
Consistency Lock Read
As previously known, the default transaction isolation level (RR) mode, the InnoDB storage engine's Select operation uses a consistent non-locking read. However, in some cases, the user needs to explicitly lock the database read operation to ensure the consistency of the data logic. The InnoDB storage engine supports two consistent lock-read operations for SELECT statements:
Select ... for update: Adds an X lock to the read row record, and no other thing can add any locks to the row.
Select ... lock in share mode: Add s lock on the read row record, other things can add s lock to the row, but if X lock is added, it will be blocked.
Self-growth with lock
In the memory structure of the INNODB storage engine, there is a self-growth counter (auto-increment counter) for each table that contains self-growth values. When a table with a self-growing counter is inserted, the counter is initialized, executing the following statement to get the value of the counter: select Max (Auto_inc_col) from the T for update. The insert operation is given a self-growing column based on this self-growing counter value plus 1. This implementation is called Auto-inc Locking, which is a special kind of table locking mechanism, in order to improve the performance of the insert, the lock is not released after a transaction is completed, but is released immediately after completion of the SQL statement inserted into the self-growth value. Auto-inc locking improves the efficiency of concurrent insertions to some extent, but there are some performance problems. First, for a column with a self-growing value, the concurrent insert performance is poor, and the transaction must wait for the previous insert to complete (without waiting for the transaction to complete). Second, for insert ... the insertion of a large amount of data on a select affects the performance of the insert, because inserts in another transaction are blocked. Starting with the MySQL5.1.22 version, the InnoDB storage engine provides a lightweight mutex self-growth implementation mechanism that greatly improves the performance of self-growth value insertions. The self-growing mode is controlled by the parameter Innodb_autoinc_lock_mode (default is 1). Self-growing inserts are categorized:
Innodb_autoinc_lock_mode parameter values and their effects on self-growth such as:
The MyISAM storage engine is a table lock, and self-growth does not take into account concurrent insertions. It is important to note that in the InnoDB storage engine, the self-growing column must be an index and must be the first column of the index, and MySQL throws an exception if it is not the first column. Abnormal
Foreign keys and locks
Foreign keys are primarily used for integrity constraint checking. In the InnoDB storage engine, for a foreign key column, if the column is not indexed explicitly, the InnoDB storage engine automatically adds an index to it, avoiding table locks. For the insertion or updating of foreign key values, first you need to query the records in the parent table, and the select operation for the parent table is not a consistent, non-locking read, because this will result in inconsistent data, so you are using SELECT ... lock in share mode mode, That is, the parent table is actively added to a s lock.
Problems with Locks
Dirty Read Dirty Reading
Dirty reads are read to dirty data (uncommitted data). A transaction (a) reads the modified but uncommitted data from another transaction (B) and operates on the basis of that data. At this point, if the B transaction is rolled back, then the data read by a transaction is invalid. does not conform to consistency.
First, the isolation level of the transaction has the default RR changed to Ru, as shown in session B, two times the select operation has achieved different results, and these 2 records are not committed in session a data, resulting in dirty read. It can be concluded that dirty reads occur when the isolation level of a transaction is RU.
Unrepeatable read non-repeatable reads
Transaction (A) read to another transaction (B) the changed data that has been committed is not eligible for isolation. The difference between non-repeatable reads and dirty reads is that dirty reads read UNCOMMITTED data, and non-repeatable reads are data that has already been committed. First, adjust the transaction isolation level to RC, and then use the example below:
Phantom Read Phantom Reading
Transaction (A) read the new data submitted by another transaction (B), which is not eligible for isolation.
Range of the lock (algorithm of the Lock):
1.Record Lock: A lock on a single record, always locks the index record, if the InnoDB storage engine table does not set any index at the time of establishment, then the INNODB storage engine uses the implicit primary key to lock.
2.Gap Lock: A gap lock that locks a range but does not contain the record itself.
3.next-key Lock: Locks a range and itself Record Lock + GAP lock to prevent phantom reads.
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 a lock in a transaction waits for a lock in another transaction to release the resource 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 parameter innodb_lock_wait_timeout is used to dynamically control the wait time (default 50 seconds), Innodb_rollback_on_ Timeout is used to statically set up the rollback operation on a transaction that is made while waiting for a timeout (default off, which means no rollback).
Dead lock
Deadlocks are two or more than two transactions in the execution process, because of contention for resources caused by a mutual waiting phenomenon. One of the simplest ways to resolve a deadlock is to time out, that is, when two transactions are waiting for each other, when one of the waiting times exceeds the set threshold, one of the transactions is rolled back, and the other waiting transaction can proceed. In the InnoDB storage engine, the parameter innodb_lock_wait_timeout is used to set the time-out. However, if the time-out of the firm's weight is relatively large, if the transaction operation updated a lot of rows, the use of more undo log, then the FIFO method is not appropriate, because the rollback of the transaction time in relation to another office may occupy a lot of time. Therefore, in addition to the time-out mechanism, the current database is generally used wait-for graph (wait diagram) way to deadlock detection. Request database Error The following two kinds of information: a. The information chain list of the lock; b. The transaction waiting list. A graph can be constructed from the list above, and if there is a loop in this diagram, there is a deadlock. In wait-for graph, the transaction is a node in the diagram.
The InnoDB storage engine chooses to roll back the transaction with the least amount of undo, as it can be found that there is a loop, so there is a deadlock. The deadlock detection of wait-for graph is usually implemented by a depth-first algorithm.
Attention:
1.S X is IX, which indicates whether this lock and other locks coexist in a mutually exclusive or compatible way
2.RECORD Lock, GAP lock, Next-key lock, indicates whether these locks are to be loaded in the row record itself, or row record + gap, or even larger range
Important Conclusions:
1, the lock on any secondary index, or the lock on the non-indexed column, will eventually go back to the primary key, but also to add a lock on the primary key
2, any leaf node on the s or X lock before the root node will be an IS or IX lock, that is, the table level is, IX lock
3. The lock on the primary key index is the record lock
4, the only index on the secondary index of the lock, also is the record lock
5, non-unique index on the secondary index of the lock, it is Next-key lock
6, there will be no separate gap lock appear, only with the record lock appears, attached to it
MySQL InnoDB Storage Engine lock
