INNODB Lock Series 2 transaction Lock

The previous article introduced the INNODB synchronization mechanism lock: InnoDB Lock Series 1

This article describes the transaction lock for InnoDB, only so called transaction lock, because the InnoDB is added as a table or row-level lock to implement the ACID properties of the transaction.

This section is divided into two articles, first to introduce the transaction lock related data structure

Transaction lock data structure 1. Lock mode

/*Basic Lock Modes*/enumLock_mode {lock_is=0,/*intention Gkfx*/Lock_ix,/*intention Exclusive*/lock_s,/*shared*/lock_x,/*Exclusive*/Lock_auto_inc,/*locks the auto-inc counter of a table in an exclusive mode*/Lock_none,/*This is used elsewhere to note consistent read*/Lock_num= Lock_none/*Number of lock modes*/};

The pattern of the lock consists of two categories, the shared lock (S) and the Mutex (X), and the s/x lock can be added to the table or the row record,

Like what:

Session1:alter table:table type X-lock

Session2:select * FROM table where ID =1 for Update:record type x lock

Because Session1 and Session2 are mutually exclusive, in order to implement blocking on table tables, Session2 increases the table lock (is) so that the Session2 cannot acquire the is lock and block.

Lock_auto_inc: Self-increment lock, in order to ensure that when the multi-line insertion, the self-increment key value is continuous.

2. Table lock and record lock data structure

-- table lock struct  lock_table_struct {    dict_table_t*    table;                /*  *    /ut_list_node_t (lock_t) locks        ; /* */};

-- row lock struct  lock_rec_struct {    ulint    space            ; /*  */    ulint    page_no        ; /*  */    ulint    n_bits            ; /* !< number of bits in the lock                    bitmap; Note:the lock Bitmap is                    placed immediatelyafter the*/};

--Lock record
structlock_struct {trx_t* TRX;/*!< Transaction Owning the lock*/ut_list_node_t (lock_t) trx_locks; /*!< list of the locks of the transaction*/Ulint Type_mode; /*!< Lock type, mode, Lock_gap or Lock_rec_not_gap, Lock_insert_intention, Wait flag, ORed*/hash_node_t Hash; /*!< hash chain node for a record lock*/dict_index_t* INDEX;/*!< index for a record lock*/Union {lock_table_t Tab_lock;/*!< table Lock*/lock_rec_t Rec_lock;/*!< Record Lock*/} un_member; /*!< Lock Details*/};

Table Lock: Use the table's data dictionary to label and keep a list of all the lock records on the table.

Record Lock: Record lock use space+page_no only to mark a page, n_bits the record of this page, 1 means lock.

Lock Record: lock_t represents a lock record, the transaction lock is always associated with TRX, and Trx_locks represents all the lock records for this transaction.

3. Global structure

　　

/**/struct  lock_sys_struct{    hash_table_t* rec_hash;    /*  * *    ulint        rec_num;}; /*  */extern lock_sys_t*    Lock_sys;

InnoDB establishes a global hash table, and all transaction-added locks are recorded in the Global transaction table, and the hash bucket is calculated using the space+page_no of the record. As shown in:

For different space+page_no, by hash key calculation, mapped to different buckets, for example two different update statements update the same record, different session, respectively, create lock_t and add to Node1 and Node2. This enables all transaction lock maintenance in the Hash+link structure.

Different buckets do not affect each other, and all wait,signal are implemented by the same bucket list.

We can see a function that is quite clear:

Calculates how many lock_t are present on the system by scanning the hash structure +link structure.
Lock_get_n_rec_locks (void){lock_t*Lock; Ulint N_locks=0;    Ulint i; Ut_ad (Mutex_own (&Kernel_mutex));  for(i =0; I < Hash_get_n_cells (Lock_sys->rec_hash); i++) {          Lock= Hash_get_first (lock_sys->Rec_hash, i);  while(Lock) {n_locks++; Lock= Hash_get_next (HASH,Lock); }    }    return(n_locks);}

Note: All lock_t statistics, as well as lock compatibility test objects, wait and signal operations, are implemented through this global structure.

4. Gap mode of the lock

#define Lock_ordinary 0

#define LOCK_GAP

#define LOCK_REC_NOT_GAP 1024

Gap mode implemented by row-level locks:

1. Record Lock: Records lock, record lock master single line according to index

2. Gap Lock:gap Lock, lock the interval of a record

3. Next-gap Lock: Includes record lock and Gap lock

The gap mode of the lock is mainly to achieve the following:

1. Missing updates: For multi-transaction updates of the same record, it is necessary to record locks to guarantee mutual exclusion between transactions and prevent loss of updates.

2. Repeatable READ: Under the transaction isolation level of read repeatable, the gap in the record needs to be locked to ensure repeatable reading.

5. Implicit and explicit lock-in

For a record lock of cluster index, a row-level lock is displayed on the clustered index of id=1, such as SELECT * from table where id=1 for update.

If it is an UPDATE statement, you will need to add a lock on the level two index, in addition to adding a lock on cluster_index.

However, cluster index adds the displayed lock, and the level two index uses an implicit lock.

Implicit lock: is a kind of delayed lock, after all, lock is a cost, so use an optimistic way, because the primary key index record on the trx_id, if the primary key record on the trx_id is the active transaction, then add the display of the lock.

This maximizes the chance of reducing the record lock.

The next article describes the specific locking process: