Implicit lock of InnoDB

http://blog.csdn.net/taozhi20084525/article/details/19545231First, the tacit 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 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 can guarantee the correctness of the operation.

Second, the specific code

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

InnoDB insert operation, the inserted record is not locked, but at this time if another thread is currently reading, similar to the following use case, Session 2 will lock waiting session 1, then how is this implemented?

Session 1:                session 2:set autocommit = ' off '; INSERT into C values (one, ' AAA ');        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 lockLock_REC_CONVERT_IMPL_TO_EXPL ()//query for implicit lock//1 on the current record.  Must already hold the kernel mutex//2.  Gets the db_trx_id system column on the record and gets the transaction id//3.  Determines whether the current transaction is active transaction//4, based on the transaction ID. Returns this active transaction object if it is an active transaction 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);//Determine if the transaction is returned, whether it contains a explicit lock, or, if so, direct return; the//implicit lock is converted to a explicit lock; Session 1 insert record completed by session 2 The lockLock_REC_HAS_EXPL (Impl_trx);//Current session 1 does not exist explicit lock, so directly create a lock, the 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 and the X-lock plusLock_rec_add_to_queue ();//After completing the insert operation of Session 1 implicit to explicit lock conversion, you can add the session 2//scan S lock, but will wait for session 1 Release lockLock_rec_Lock();

Note: Insert is not locked, or the meaning of implicit lock is actually very significant. From the lock structure described earlier, we can analyze the fact that the cost of a lock structure of InnoDB is relatively large. Or innodb the overhead of locking a record is the same as the cost of locking all the records in a page. and insert by implicit way lock, greatly reduce the lock module overhead, for InnoDB support concurrent Insert operation, is a great boost.

Three, the important problem 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

Here we mainly look at the check duplicate of the clustered index. Check duplicate is the guarantee that in the case of an implicit lock, multiple transaction inserts are guaranteed to be unique to the index.

1. Clustered index Check Duplicate

Ha_innobase::write_row ();.. row_ins_index_entry_low ();//search path, position the cursor to the first location less than or equal to the insertion value Btr_cur_search_to_nth _level (PAGE_cur_le);//cursor is the insert position of the leaf node after binary search//The result of binary search, the current record and the number of columns to be insert record have several identical columns//If the same column value (curso R->low_match), exceeding the number of unique key values for the current index,//There may be a unique key-value conflict row_ins_duplicate_error_in_clust (cursor, entry, THR); n_unique = Dict_ Index_get_n_uniques (); if (Cursor->low_match >= n_unique)//the cursor corresponding to the existing record plus S lock (May wait), to ensure that the operation of the record, including://insert/ Update/delete has already committed or rolled back/S lock can guarantee that the insert operation of other transactions is not possible, as the real//insert operation will attempt to add X lock to the next record, as detailed in the next chapter analysis row_ins_set_ Shared_rec_loc (LOCK_s);Lock_clust_rec_read_check_and_Lock();//Determine if there is a implicit lock on the cursor corresponding record (active transaction)//If present, the implicit lock is converted to explicit lockLock_REC_CONVERT_IMPL_TO_EXPL ();Lock_rec_Lock(); If the above implicit lock conversion succeeds, the plus S lock will wait until the active transaction releases the lock.  After the S lock and lock is complete, you can make a judgment again, and finally decide whether there is a unique conflict//1.  Determine if the record value of the Insert record corresponds to the cursor//2.  Two-level unique key-value lock-in, multiple NULL values can exist//3. Finally judge the Delete_bit status of the record, determine whether the record was deleted to submit 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 rollback and release the lock, but whether it is a commit or a rollback, the record that the cursor points to is still present, and there may be a delete flag (a rollback occurs).

2. Auxiliary index Check Duplicate

A, clustered index primary key is unique, non-clustered unique index, and its index is unique.
b, if the insert record is exactly the same as the clustered index entry, and the clustered index entry is a delete item, set its delete tag directly to 0 and update on the deleted item.
C, if the insert record and the non-clustered unique index key value is exactly the same, and the non-clustered index entry is deleted, it does not necessarily modify the item state, but also need to determine whether the corresponding primary key is the same, if the primary key is the same, then reuse the item; otherwise, insert the new item.
D, clustered index, the same primary key value of the item, there is only one item, there is no possibility of multiple items.
E, non-clustered unique index, an item with the same index key value may have multiple items, but these items, whose primary key is different, and, for these items, only one is a valid item, the other item is a deleted commit item.
F, the uniqueness of the clustered index check, only need to detect the insert corresponding records, because there is only one item; uniqueness detection of nonclustered unique indexes, need to check the same record of multiple key values backwards

Implicit lock of InnoDB