The basic concept

Dirty reads are those that read uncommitted changes from other transactions.

The difference between unrepeatable reads and phantom reads is that unrepeatable reads focus on UPDATE and DELETE (committed changes cannot be read) and phantom reads focus on INSERT.

Isolation level

Snapshot read and current read

In multi-version concurrency control, read operations fall into two categories:

Snapshot read: The snapshot read reads the visible version of a record, which may be a historical version, without locking.
Current read: The latest version of a read is read. Records returned by the current read are locked to ensure that other transactions do not concurrently modify the record.

A simple select operation is a snapshot read, as shown below. For details, see MVCC.

select * from table where xxx;
Copy the code

Special read, insert, update, and delete operations belong to the current read and need to be locked, as shown below.

select * from table where xxx lock in share mode;
select * from table where xxx for update;
insert into table values(xxx);
update table set xxx where xxx;
delete from table where xxx;
Copy the code

Snapshot reads in RR levels are inconsistent with current reads

http://mysql.taobao.org/monthly/2017/06/07/
Copy the code

Rc-level replication causes data inconsistency

See the example below where the current read data is inconsistent because of the order in which the commit was copied. RR does not have this problem because RR has a Gap lock

https://www.cnblogs.com/fanguangdexiaoyuer/p/11323248.html
Copy the code

The lock

The lock type

Innodb internally uses a data of type unsiged long to indicate the type of lock. As shown in the figure, the lowest 4 bits indicate lock_mode, 5-8 bits indicate lock_type, and the remaining high bits indicate the type of row lock.

So the Innodb lock is the combination of the above three, such as LOCK_S | LOCK_REC | LOCK_ORDINARY said S row locking type of clearance. Status code = 2048 + 512 + 32

For row locks, lock_mode only uses LOCK_S and LOCK_X.

Lock conflict matrix

For lock_mode, the error conflict matrix is: TOdo

For row lock types, the lock conflict matrix is: TODO

Put it all together

The conflict matrix can be understood as any LOCK_S row locking type is compatible with LOCK_S, LOCK_S and LOCK_X, LOCK_X and LOCK_X compatible to consider row locking type, such as GAP and GAP compatible, LOCK_X | GAP with LOCK_X | GAP compatible.

Pay special attention to GAP locks and Insert Intention locks. GAP locks block insert intention locks.

In fact, GAP locks exist only to block insert Intention locks, and RR levels are used to solve the illusion problem of the current read. Insert intention lock Does not block any lock.

Implicit lock

For clustered index pages, the record itself naturally carries a transaction ID. For secondary index pages, the maximum transaction ID of the last update is recorded on the page, and visibility is judged by way of back table.

purpose

Implicit locking is a delay locking mechanism implemented by InnoDB, which is characterized by locking only when conflicts are possible, thus reducing the number of locks and improving the overall performance of the system.

The characteristics of

Implicit locks are for B+ Tree records that are being modified, so they are record type locks and cannot be gap locks or next-key types.

scenario

Insert

Secondary indexes

details

Mysql.taobao.org/monthly/202… Mp.weixin.qq.com/s/547fdypJD…

Lock the specific analysis

Factors affecting locking

The isolation level of the current transaction
SQL is consistent nonlocking read or DML(INSERT/UPDATE/DELETE) or locking read.
Whether the SQL is executed with an index, and the type of index used (primary key index, secondary index, unique index)

The order of locking

The lock factor describes the index used. The order in which different indexes are locked for the same record also depends on which index is used.

For example, if the index is located through the primary key index, the primary key index is locked first, and then the secondary index and unique index are locked (implicit lock). If the updated content does not involve a secondary or unique index, you do not need to lock the index. The diagram below.

Of course Delete needs to lock all indexes.

MySQL 2-phase lock

One of the principles of traditional RDBMS locking is 2PL (two-stage locking) :Two-Phase Locking. The lock operation is divided into two phases: lock and unlock, and the lock and unlock phases do not intersect. Let’s take a look at the implementation of 2PL in MySQL again.

Release the lock

In most cases, transaction locks are released when a transaction commits or rolls back (the two-phase locking idea), but there are three types of accidents, two active and one passive:

Auto-inc lock is released directly at the end of SQL (innobase_commit –> lock_unlock_table_autoinc);
Unlock (ha_innobase:: unlock_ROW) a record returned from the engine layer to the Server layer when a DML statement is executed at the RC isolation level. If the WHERE condition is not met, unlock (ha_innobase::unlock_row) must be unlocked immediately.

In the second figure below, the val condition does not satisfy the where query.

In the lock wait case, if the waiting data record disappears (such as a rollback), the local lock may disappear.

As shown below, the RC level lock waits.The RR wait lock does not disappear but becomes the GAP lock of the next record, preventing phantom read. See lock splitting and inheritance for details.

Lock splitting and inheritance

A lot of complex lock analysis relies on this thing, and if you understand it, you can handle lock changes in a variety of situations.

Update locking implementation without conflict

Equivalent query

select * from table where col=xxx lock in share mode; Select * from table where col= XXX for update; update table set xxx where col=xxx; delete from table where col=xxx;Copy the code

Locking situation

Range queries

Range lookups have changed in detail due to b-tree positioning issues.Copy the code

Insert lock implementation

The phases involved in locking

A uniqueness constraint check that determines whether a unique key record already exists for a primary key or unique index.

1) If there are active transactions, judge whether there are active transactions on the record. (1) If there are active transactions, add the lock of the conflict record to the transaction, and then add the wait S lock of the conflict record to its own transaction to enter the lock waiting stage. (2) If there is no active transaction, add S lock of the record to its own transaction. 2) If it does not exist, it is not locked.Copy the code

2. Check whether it is DB_DUPLICATE_KEY(row_ins_DUPL_ERROR_WITH_REc). An error message is returned to the client, but locks added in phase 1 are not released.

3. Perform lock compatibility check on the next record at the insertion position, and monitor whether the next record at the insertion position conflicts with the record with the insertion intention.

1) If yes, add a wait lock for the record to its transaction. 2) If not, insert data without locking.Copy the code

For subsequent possibilities of lock wait in 1->1)->(1) :

- Active transaction commit, acquire waiting S lock, and then report an error at phase 2. - Active transaction rollback, because the target record currently waiting for the lock disappears, lock splitting and inheritance occur, and the wait lock becomes the S lock of the target record next (primary key and unique key tests are GAP locks). Then, phase 2 and phase 3 detection will be carried out. If the final insertion is successful, lock splitting and inheritance will occur, that is, the S lock of the next record will be inherited to itself.Copy the code

----row_ins_clust_index_entry_low --------btr_pcur_open Move the cursor to the corresponding position of the record to be inserted. Insert always uses PAGE_CUR_LE, so it will locate to the previous record to be inserted -------- If there is a unique conflict, enter the following function to lock --------if (! Index - > allow_duplicates / / is the only index && n_uniq / / the only index number field && (cursor - > up_match > = n_uniq | | cursor - > low_match > = n_uniq)) ------------row_ins_duplicate_error_in_clust // Stages 1 and 2 occur in this function, Detailed see https://www.jianshu.com/writer#/notebooks/48095140/notes/80218523 -- -- -- -- -- -- -- --} -- -- -- -- -- -- -- -- the if (! Index ->allow_duplicates && ROW_ins_must_MODIfy_rec (CURSOR)) {return row_ins_must_modify_rec Primary key in-place update (primary key in-place update) ------------ (primary key in-place update (primary key in-place update) ------------ Note Records of unique key conflicts are deleted -mark ------------btr_cur_optimistic_update or btr_cur_update --------}else{ ------------btr_cur_optimistic_insert or btr_cur_pessimistic_insert ----------------btr_cur_ins_lock_and_undo -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- lock_rec_insert_check_and_lock -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- determine whether conflict with insert intent locks -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- lock_rec_other_has_conflicting / / 3 stage -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- because of positioning to the above is for insert records before a record, So here is insert intent locks, to obtain a location to record a record lock -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- next_rec = page_rec_get_next_const (rec); ----------------------------heap_no = page_rec_get_heap_no(next_rec); -- -- -- -- -- -- --}Copy the code

https://www.jianshu.com/p/8607f106525c
Copy the code

Locking implementation in conflict cases

Locking type

Primary key conflict, conflict record committed

Primary key conflict, conflict record not committed

Unique index conflict, conflict record committed

Unique index conflict, conflict record not committed

The primary key conflicts, and the conflict records are rolled back

The following deadlock occurs because of record rollback, the wait lock inherits into the S-lock (Gap) that is targeted to record the next record, and then conflicts with the insert intent lock of the third stage.

A thinking

Why does an S lock need to be added to a unique key conflict if the target record of the S lock needs to be converted to a Gap lock after being rolled back?

Because the record is gone, the lock cannot be added to the record. If there are multiple WAITING S locks, they will not change into gap and move down together, and there is no way to prevent them from inserting, including latch.

RC

RR

The lock is split after the insert is successful. Procedure

A lock split occurred after the record was successfully inserted. Procedure

RR unique index, rollback and deadlock, as shown in the second figure below.

# # # # # # a thinking Actually after the success of the insert records, the back of the S | GAP can lock release? It could be released in theory, but it violates the two-phase lock protocol.

The difference between a primary key and a unique key in the case of Delete Mark records

There are several premises for this:

A primary key is
Secondary index updates always start with DELETE and then INSERT
The delete-mark record of the same uniq field is always the front (left) of the non-delete -mark record. This is determined by the search mode of MySQL B tree Insert operation using PAGE_CUR_LE.
Therefore, if the secondary unique index has no non-delete-mark duplicate record, it needs to add the lock to the insert position, depending on the code.

RC primary key

In the primary key

RC unique index

RR unique index

Current read optimization – Semi-consistent read

Semi-consistent reading premise:

Update takes effect, but Delete does not.
RC isolates or enables innodb_lockS_unSAFE_for_binlog
A lock wait occurred
Must be a full table scan && This index is a secondary index

Case study:

https://mp.weixin.qq.com/s?__biz=MzU0MTczNzA1OA==&mid=2247483804&idx=1&sn=c4360f68444bdc91ff46f662c3f27f9e&chksm=fb24289 1cc53a187231c797be8777633ccf2e951cb99e63298b944cba5ff6a84000f188208bb&mpshare=1&scene=1&srcid=0331LzGltSmGsIdUujfl5KN6%2 3rdCopy the code

Query: Semi-consistent read is valid when an update full table sweep encounters an uncommitted INSERT. To take effect.RR requires waiting.

Rc-level unique index inserts have a gap lock

zhuanlan.zhihu.com/p/52098868

zhuanlan.zhihu.com/p/52100378

zhuanlan.zhihu.com/p/52234835

reference

Mp.weixin.qq.com/s?__biz=MzU…

hedengcheng.com/?p=771

www.itcodemonkey.com/article/130…

Mysql.taobao.org/monthly/201…

Mysql.taobao.org//monthly/20…

Github.com/Yhzhtk/note…