MySQL database InnoDB lock mechanism in the storage engine--reprint

Original address: http://www.uml.org.cn/sjjm/201205302.asp

00– Basic Concepts

When concurrent transactions access a resource at the same time, it is possible to cause inconsistent data. Therefore, a mechanism is needed to order access.

A lock is one of the mechanisms. We use the shop's fitting room to make a metaphor. The fitting room is used by many consumers. So there may be multiple consumers trying clothes at the same time. To avoid conflict, the door of the fitting room was locked. The man who tried the clothes was locked inside, and the others could not be opened from the outside. Only the people in the inside open the door, the outside talent can go in.

-Basic types of locks

Operations on the database can be summarized as two, read and write. When multiple transactions read an object at the same time, there is no conflict.

Simultaneous reading and writing or simultaneous writing can create conflicts. Therefore, in order to improve concurrency, two types of locks are usually defined:

A. A shared lock is also called A read lock.

A shared lock represents a read operation on the data. Therefore, multiple transactions can share a lock on an object at the same time.

B. Exclusive locks (Exclusive Lock) are also called write locks.

An exclusive lock indicates that the data is written. If a transaction has an exclusive lock on the object, the other transaction cannot add any more locks to it.

-S, x Lock compatibility matrix

For locks, a matrix is usually used to describe the conflict between them.

S X

S +–

X--

+ stands for compatibility,-represents incompatible

-The size of the lock

A. Table lock

Locks the entire table, affecting all records of the standard. Typically used in DDL statements, such as delete Table,alter TABLE.

B. Row locks (Row lock)

Locks a row of records, affecting only one record. Typically used in DML statements, such as INSERT, UPDATE, delete, and so on.

Obviously, table locks affect the data for the entire table, so concurrency is not as good as a row lock.

-Intent Lock (Intention Lock)

Table locks and Row locks also conflict because table locks overwrite data on row locks. Such as:

A. Trx1 Begi

B. Trx1 to T1 plus x lock, modify table structure.

C. trx2 BEGIN

D. trx2 to T1 a row of records plus s or x locks (the transaction is blocked, waiting for the lock to succeed).

Trx1 to manipulate the entire table, locking the entire table. Then trx2 can no longer add X or S lock to T1 's single record to read or repair the record.

To facilitate the detection of conflicts between table-level and row-level locks, an intent lock is introduced.

A. The intent lock is divided into the intent read lock (IS) and the intent write lock (IX).

B. An intent lock is a table-level lock, but it indicates that a transaction is reading or writing a row of records, rather than an entire table.

So there is no conflict between intent locks, and real conflicts are checked when the lock is added.

C. Before adding a lock to a row of records, the first thing to do is to add an intent lock to the table. That is, to add table intent and row locks at the same time.

With the intent lock, the above example becomes:

A. trx1 BEGIN

B. Trx1 to T1 plus x lock, modify table structure.

C. trx2 BEGIN

D. Trx2 to T1 Plus IX lock (transaction blocked, waiting for lock to succeed)

E. trx2 give T1 a row of records plus s or x locks.

-Table lock Compatibility matrix

is IX S X

is + + +–

IX + +--

S +-+-

X----

+ stands for compatibility,-represents incompatible

A. There is no conflict between intent locks, because intent locks represent only actions to be made on a row of records. When a lock is added, the conflict is determined.

01– Row Lock

Intuitively, a row lock is one that locks a row of records, preventing other transactions from manipulating the row record. Here's an implicit logic:

A. The insert operation is never blocked because the insert operation does not manipulate an existing record (the processing of the insert duplicate is not considered here). Is this logic right? This is related to the user's usage, in some cases it is acceptable to the user, and in some cases it is unacceptable to the user.

-Phantom Reading (Phantom read)

If the insert operation is not blocked, a phantom read is generated. The MySQL manual has an introduction to Phantom reading.

A. MVCC can avoid phantom reading. But MVCC is only valid for SELECT statements, for SELECT ... [LOCK in SHARE MODE | For UPDATE], UPDATE, DELETE statement is invalid.

B. In order to be able to avoid phantom reading by lock, a next-key mechanism is adopted. Next-key blocks the insert operation by locking the gap between 2 records.

-the mode of the row lock

The line lock s, x lock makes some precise subdivision, called precise Mode in the code. These precise patterns make the lock finer in granularity. can reduce conflicts.

A. Clearance lock (GAP Lock), lock gap only.

B. Record lock lock record only.

C. Next-key Lock (called ordinary lock in the code), while locking records and gaps.

D. Insert Intent Lock (insert Intention Lock), the lock used when inserting. In the code, insert the intent lock,

The gap lock is actually marked with a lock_insert_intention.

The MySQL manual provides a detailed description of these patterns.

-Compatibility matrix for row lock mode

G I R N (already existing lock, including waiting lock)

G + + + +

I-+ +-

R + +--

N + +--

+ stands for compatibility,-represents incompatibility. I represents the insert intent lock,

G stands for Gap Lock, I for Insert intent Lock, R for Record lock, N for Next-key lock.

S-Lock and S-lock are fully compatible, so you do not need to compare the exact mode when judging compatibility.

Accurate mode detection, used between S, x and X, X.

This matrix is introduced from the Code of Lock0lock.c:lock_rec_has_to_wait (). Several features can be seen from this matrix:

A. There is no conflict between insert operations.

B. Gap,next-key will block insert.

C. Gap and Record,next-key will not conflict

D. Conflicting record and record, Next-key.

E. An existing insert lock does not block any locks that are ready to be added.

There are also several questions:

A. Why is the insert intent lock not blocking the gap lock? In certain cases, the insert operation is delayed indefinitely.

B. Does this lock need to exist if you do not block any locks?

-The current function is to wake the waiting thread by locking it.

-but this does not mean that you can do the insert operation directly after being awakened. You need to decide again if there is a lock conflict.

C. Can the gap+lock_insert_intention tag be changed directly into a insert_intention lock?

I'm still looking at it now.

-B+tree Line Lock

InnoDB row locks are not a simple concept of data row locks. Instead, it refers to the row lock on each b+tree, or it can be understood as a row lock on each index. Therefore, when you manipulate a row of records, it is possible to add multiple rows to a different b+tree. Such as:

CREATE TABLE T1 (c1 int KEY, C2 int, C3 int, INDEX (C2));

INSERT into T1 VALUES (1, 1, 1), (3, 3, 3)

UPDATE T1 C3 = Ten WHERE C2 <= 2

The UPDATE statement is also locked on secondary index and clustered index.

-Use of the row lock mode

Under what circumstances are these patterns of row locks used? The MySQL manual is described in detail.

A. next-key is used on the index used by the Where condition (exactly the index used to do search).

In the example above, Next-key Lock is used on Index (C2).

B. Record lock is used on an index that is not used by the Where condition. In the example above, the record lock is used on the cluster index. Therefore, the above UPDATE statement will also add Next-key to the key 1 on index (C2) and the record lock on the primary key 1. When another session is inserted (2,5,2) concurrently (3,5,2), it can be successful, but (2,2,2) is blocked.

Next-key and Record

Test found, SELECT ... [For UPDATE | Lockin SHARE MODE] may cause all records to be locked.

When the table is very small, select uses a full-table scan method. When you use this method, all the data is traversed, so all the data is locked. Although Ha_innobase::unlock_row () is called for non-qualifying records, it is not freed at repeatable read level. Maybe it's time to count a bug.

C. A, B applies to select ... [For UPDATE | LOCK in SHARE MODE], UPDATE, DELETE statement.

D. Gap locks are also clearly used on the index used in the Where condition. Unlike Next-key, gap locks are only added to the upper boundary (the first one is larger than the qualifying record). The Next-key is added to all eligible records. The conditions in the above example c2=2 record, need to add a gap lock on the c2=3.

？ In a forward query, a next-key lock is actually added to the boundary on the InnoDB. This may be an implementation limitation.

The current use of gap is:

The –supremum record is always a gap lock.

– When a reverse query (ORDER by DESC) occurs.

– A gap lock is added to the next record when the equivalent matches an exact key value.

– When equivalent matches an exact key value of the prefix, the next record is added to the gap lock.

E. Insert is usually not locked. An insert intent lock is created only when other transactions have a gap or next-key lock on the insertion point that waits. This lock is in the waiting state.

-Impact of isolation levels on Next-key locks

A. Read uncommitted and read committed do not require a lock on the gap, Nexk-key becomes a record lock.

B. Next-key locks are typically used when repeatable Reads and serializable.

There are 2 cases where the clearance lock is not required:

– Query for a unique value, such as where C1 = 1, C1 is a primary key or unique key, and the query results do not contain a null field.

– When Innodb_locks_unsafe_for_binlog is turned on. Here are some questions to consider:

• In this case, the Update,delete is completely designed to prevent the master and slave data from being inconsistent. Then there is no need to delete, update plus gap lock when you are not using Binlog.
• When Row Format Binlog, no gap lock will cause master, slave.
• Even if the innodb_locks_unsafe_for_binlog,select is set ... [] Whether the lock can be unlocked without clearance.

The main task of determining what locks to add is in Row0sel.c:row_search_for_mysql ().

02– delay locking mechanism

If a table has a lot of indexes, wouldn't it be a lot of locks on different b-tree when you operate a record?

First look at the status information for a transaction:

CREATE TABLE T1 (c1 int KEY, c2 int);

BEGIN;

INSERT into T1 VALUES (1, 1);

INSERT into T1 VALUES (2, 2);

SHOW ENGINE INNODB STATUS;

Status information:

LIST of transactions for each SESSION:

---TRANSACTION 501, ACTIVE 0 sec

1 lock struct (s), heap size 376, 0 row lock (s), Undo log Entries 2

– Implicit Lock

Lock is a pessimistic ordering mechanism. It assumes that conflicts are likely to occur, so lock the data as it is manipulated.

If the likelihood of a conflict is small, most locks are unnecessary.

INNODB implements a delay-locking mechanism to reduce the number of locks that are called implicit locks (implicit lock) in the code.

There is an important element in the implicit lock, the transaction ID (trx_id). The logical process for an implicit lock is as follows:

A. Each record in InnoDB has an implied trx_id field that exists in the b+tree of the cluster index.

B. Before manipulating a record, first check whether the transaction is an active transaction (uncommitted or rollback) based on the trx_id in the record.

In the case of an active transaction, the implicit lock is first converted to an explicit lock (that is, a lock is added to the transaction).

C. Check if there is a lock conflict, create a lock if there is a conflict, and set the waiting state. If no conflicts are unlocked, skip to E.

D. Wait for the lock to succeed, be awakened, or time out.

E. Write the data and write your own trx_id to the trx_id field. Page Lock guarantees the correctness of the operation.

Related code:

A. LOCK_REC_CONVERT_IMPL_TO_EXPL () Converts an implicit lock into a display lock.

B. Lock and test row lock collisions are all used with Lock_rec_lock (), and its first argument indicates whether it is an implicit lock. So pay special attention to this parameter. If true, locks are not added when there is no conflict.

C. The specific content of the conflict testing the row lock in Lock_rec_has_wait ()

D. Create waiting Lock is lock_rec_enqueue_waiting ()

E. Create row lock is Lock_rec_add_to_queue ()

– Features of an implicit lock

A. Locking is only possible when a conflict is likely to occur, reducing the number of locks.

B. An implicit lock is a record-type lock for B+tree records that have been modified. It cannot be a gap or next-key type.

– Use of an implicit lock

A. The insert operation only has an implicit lock and does not require a display lock.

B. When querying, Update,delete uses the display lock directly on the index and primary key of the query, and an implicit lock on the other indexes.

In theory, an implicit lock can be used on the primary key. Early use of the display lock should be to reduce the likelihood of deadlocks.

Insert,update,delete to B+tree operations are from the primary key B+tree start, so the primary key lock can effectively prevent deadlocks.

–secondary an implicit lock on index

The front says that trx_id only exists on the primary key, so how do you implement an implicit index on a secondary index?

It is clear that the primary key value in the secondary index is to be searched two times on the primary key B+tree. This is a huge expense.

InnoDB has an optimization for this process:

A. There is a max_trx_id on each page, and the maximum transaction ID is updated each time a record of the secondary index is modified.

B. When deciding whether to turn an implicit lock into an explicit lock, compare the max_trx_id of the page and the minimum trx_id of the transaction list first. If the max_trx_id is smaller than the minimum trx_id of the transaction list, then it is not necessary to convert to a display lock.

Code in Lock_sec_rec_some_has_impl_off_kernel ()

/* Some transaction may have a implicit x-lock on the record onlyif the Max Trx ID for the page >= min Trx ID for the TRX List, ordatabase recovery is running. We don't write the changes of a page Max Trx ID to the log, and therefore during recovery, this value for a page could be I Ncorrect. */

if (page_get_max_trx_id (page) < trx_list_get_min_trx_id ()

&&!recv_recovery_is_on ()) {

return (NULL);

}

Implementation of 03– Lock

– Storage of Locks

A. Table->locks stores all table-level locks for a table.

B. Lock_sys->rec_hash Store row locks for all tables. The hash value is calculated according to (Spaceid, PageNo).

C. Trx->trx_locks all locks that store transactions, including table-level and row-level locks. All locks for a transaction are released together at the end of the transaction. The code is in Lock_release_off_kernel (). If a waiting lock can be authorized, the waiting lock is converted to the authorized lock and the corresponding transaction is awakened.

– Unique identification of row locks

The first impression is that the unique identification of the row lock is done with the key values of each row. But the key value occupies a large space.

InnoDB uses page No.+heap NO. To do the unique identification of the row lock. We can interpret the heap No. As a self-increment value on the page. Each physical record is assigned a unique heap no when it is created.

A. The key value can be understood as a logical value, page No. + Heap No. is physical.

B. Although the physical footprint is small, processing is more complex. For example, when splitting a B+tree page, half of the records are moved to the new page, so the existing locks are migrated.

The D function of the lock movement is: Lock_move_reorganize_page (), Lock_move_rec_list_start (),

Lock_move_rec_list_end ().

The inheritance of Gap locks is also performed when data is deleted and inserted. Lock_rec_inherit_to_gap ()

Lock_rec_inherit_to_gap_if_gap_lock ().

– Deadlock (Deadlock)

A. Timeout mechanism. Adds a waiting lock and returns a db_lock_wait error when the lock to be added conflicts with another lock.

Row_mysql_handle_error calls Srv_suspend_mysql_thread to suspend a thread.

B. Deadlock detection detection mechanism. Whenever you create a waiting lock, you call Lock_deadlock_occurs () for deadlock detection.

The deadlock detection method is Waits-for Graph. Implemented in Lock_deadlock_recursive ().

When a deadlock is found, select one of the transactions and roll it back to unlock the deadlock. Which transaction can I choose to roll back?

– If a transaction modifies the Non-transactional table (such as the MyISAM table, the modification cannot be rolled back), the other table does not.

The non-transactional will be rolled back without modification.

– If 2 transactions have modified the Non-transactional table or none. Compares the number of records modified by 2 transactions and the number of locks added. A transaction with a sum of small sums is rolled back. Trx_weight_ge () implements this logic.

MySQL database InnoDB lock mechanism in the storage engine--reprint