An implicit lock of InnoDB

one, the implicit lock of knowledge preparation

Reference: http://www.uml.org.cn/sjjm/201205302.asp

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 of an implicit lock is as follows:

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

B. Before manipulating a record, first checks whether the transaction is an active transaction (uncommitted or rolled back) based on the trx_id in the record. If it is an active transaction, first converts an implicit lock to an explicit lock (that is, a lock is added to the transaction).

C. Check for lock conflicts and, if there is a conflict, create a lock and set it to the waiting state. If there is no conflict without locking, skip to E.

D. Wait for lock success, wake up, or timeout.

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

second, the specific code

From: InnoDB SMO & Page Extent & Lock & Latch The insert operation by Ho Dengcheng InnoDB does not lock the inserted record, but if another thread is currently reading, similar to the following use case, SES Sion 2 locks up and waits for session 1, so how does this happen?

Session 1: Session                			2:
Set autocommit = ' off ';
INSERT into C values (one, ' AAA ');
					        SELECT * from c where C1 = One lock in share mode;

The following is the source tracking process for session 2:

row_search_for_mysql () Sel_set_rec_lock ();//Convert implicit lock on record to explicit lock Lock_rec_convert_impl_
		TO_EXPL ();  Query whether there is a implicit lock//1 on the current record.  Must already hold the kernel mutex//2.  Gets the db_trx_id system column on the record, obtaining the transaction ID//3.  Determines whether the current transaction is active//4 based on the transaction ID.
			For active transactions, return this active transaction object Impl_trx = Lock_clust_rec_some_has_impl (REC, index, offsets);
			Ut_ad (Mutex_own (&kernel_mutex));
			trx_id = row_get_rec_trx_id ();
		Trx_is_active (trx_id); To determine whether the return transaction contains a explicit lock or, if so, to return the//implicit lock to the explicit lock, and to complete session 1 Insert records by session 2 lock Lock_rec_has_
		EXPL (IMPL_TRX); Current session 1 does not exist explicit lock, so create a lock directly, lock mode is//Lock_rec | lock_x |
LOCK_REC_NOT_GAP//Because there is no other lock on the insert record, the conversion is successful, the X lock plus the lock_rec_add_to_queue (); After the insert operation of Session 1 is completed, the implicit to the explicit lock is converted, the scan S lock of Session 2//is added at this time, but waits for the 1 lock Lock_rec_lock () ;

Note: Insert does not lock, or the meaning of implicit lock is actually very important. From the lock structure described earlier, we can analyze the fact that the cost of a lock structure of InnoDB is quite large. Or the cost of locking a record is the same as the cost of locking all the records in a page, InnoDB said. Insert through the implicit mode lock, greatly reduce the insert when the lock module overhead, for InnoDB support concurrent Insert operation, is a great upgrade.

Three, the important question of the implicit lock--check Duplicate

From: "InnoDB SMO & Page Extent & Lock & Latch" by Ho Dengcheng

Further reference: InnoDB lock system insert/delete lock processing and deadlock example analysis http://fan0321.iteye.com/blog/1984364

This is mainly a check duplicate for clustered indexes. Check duplicate is the assurance that the inserts of multiple transactions are guaranteed to be unique in the case of an implicit lock.

1. Clustered index Check Duplicate

Ha_innobase::write_row ();
		... row_ins_index_entry_low ();
		To do search path, position the cursor to the first position less than or equal to the insertion value btr_cur_search_to_nth_level (page_cur_le); Cursor is binary search after the location of the insert position/to determine the results of binary search, the current record and to insert records have several identical columns//If the same column number of values (cursor-&
			Gt;low_match), which exceeds the number of unique key values for the current index,//There may be a Uniqueness key conflict row_ins_duplicate_error_in_clust (cursor, entry, THR);
			N_unique = Dict_index_get_n_uniques (); if (Cursor->low_match >= n_unique)//To cursor the existing record plus S lock (may wait) to ensure the operation on the record, including://Insert/update/delete has been mentioned AC or rollback/S locks are already available to ensure that insert operations on other transactions are not possible because the next record will be tried to be X-locked when the true//insert operation is in progress, as detailed in the next section for the analysis of Row_ins_set_shared_re
					C_loc (lock_s);
						Lock_clust_rec_read_check_and_lock (); To determine if there is a implicit lock (active transaction)//if present on the record of the cursor, convert the implicit lock to explicit lock LOCK_REC_CONVERT_IMPL_TO_EXPL ()
						;  Lock_rec_lock ();
				If the above implicit lock is converted successfully, the here Plus s lock will wait until the active transaction releases the lock.  When the S lock is complete, it can be judged again, and the final decision is whether there is a unique conflict//1.
Determine if the record of the insert record corresponds to the cursor				2.  Two-level unique key-value locks, which can have multiple NULL values//3.
					Finally, the delete_bit state of the record is judged and the records are deleted and submitted Row_ins_dupl_err_with_rec ();
					Cmp_dtuple_rec_with_match (); return!rec_get_deleted_flag ();

Note: When the S lock is successful, the active transaction should commit or roll back and release the lock, but the record pointed to by cursor still exists, either committed or rolled back, and there may be a delete flag (a rollback occurs).

2. Secondary index Check Duplicate

A, clustered index primary key is unique, nonclustered unique index, and its index is unique.
b, if the insert record is identical to the clustered index entry, and the clustered index entry is a delete item, the delete tag is set directly to 0 and the deletion is done with update.
C, if the insert record is identical to the nonclustered unique index entry key value, and the nonclustered index entry is a deleted item, the item state is not necessarily modified at this time, and the corresponding primary key is also determined to be the same, and if the primary key is the same, reuse the item, otherwise, insert the new item.
D, clustered index, the same primary key value of the item, at most only one, there is no number of items.
E, nonclustered unique indexes, items with the same index key values may have multiple entries, but the keys are different for primary keys, and only one of these items is a valid entry, and the other items are deleted commits.
F, the uniqueness of the cluster index check, only need to detect the corresponding insert records, because only one; the uniqueness detection of nonclustered unique indexes, you need to check back multiple key values same record