preface

In the last article we shared MVCC and learned how it works. So the read operations performed by transactions using MVCC are called consistent reads, or consistent lockless reads, and most articles are also called snapshot reads. All ordinary SELECT statements are consistent reads at the RC and RR isolation levels. For example,

select * from t;
Copy the code

Consistent reads do not lock any records in the table, so they are very fast.

However, as mentioned in the previous article, MVCC does not prevent phantom reads at RR level. The solution to phantom reads is gap locking. For example:

begin; Select * from users lock in share mode select * from users lock in share mode; Select * from users where id=1; select * from users where id=1; Update users set name='mysql' where id=1; Select * from users; Select * from snapshot; commit; Transaction B commits successfully and inserts dataCopy the code

Using locks to access data I understand as lock reads, so let’s talk about locks now.

Think about locks abstractly

I understand that locks are used for sorting, in particular scenarios, to ensure that things are executed in order. The abstraction of the lock implementation is also an object in memory, such as an object in JAVA, etc. So the lock in mysql is actually a structure in memory.

There is no lock before the transaction is executed, that is, there is no lock structure to begin with, as in the following record:

When a transaction wants to make changes to the record, it first looks to see if there is a lock structure associated with the record. If there is no lock structure associated with the record, it generates a lock structure associated with the record. For example, if transaction 80 changes this record, it needs to generate a lock structure associated with it:

Transaction 120 will also modify this record and find that there is already a lock structure, so it will also generate a lock structure. Just is_waiting = true

Wake up 120 when 80 is finished

Comb through the memory structure of the lock

Basic information:

  • TRX information: represents which transaction generated this lock structure.

  • Is_waiting: indicates whether the current transaction is waiting.

Other information:

  • Index information: Record which index the locked record belongs to

  • Lock type: row lock or table lock.

  • Lock mode: shared lock or exclusive lock.

  • The specific types of row locks include next-key lock, gap lock, and record lock

  • Table lock/row lock information:

  • Table locks: Records which tables are locked, among other things

  • Row locks:

  • Space ID: indicates the tablespace where the record resides.

  • Page Number: indicates the Page Number of a record.

  • N_bits: bitmap of records in a page

So that’s the big picture of a lock.

The classification of the lock

The classification of the large

  • Shared Locks are Shared Locks for short. Reading is not mutually exclusive; reading and writing are mutually exclusive. Before a transaction reads a record, it first acquires the record’s S lock.

  • Exclusive Locks are also called Exclusive Locks. Reading, reading and writing are mutually exclusive. When a transaction changes a record, it first acquires the record’s X lock.

Engine level classification

Locks in other storage engines

For storage engines such as MyISAM, MEMORY, MERGE, they only support table-level locking, and these engines do not support transactions.

The SELECT operation is equivalent to adding a table level S lock to the table

UPDATE operation, equivalent to obtaining the X lock on the table

Table level locking in InnoDB

Table level S locks, X locks

Changing the mutual exclusion of tables is done at the server level using something called Metadata Locks (MDL), and InnoDB typically does not use the table level S and X Locks provided by the InnoDB storage engine.

Table level auto-inc lock:

While a transaction holds an auto-Inc lock, insert statements of other transactions are blocked, ensuring that increments allocated in a statement are continuous.

The auto-Inc lock is released after the AUTO_INCREMENT column is generated, and the lock is not released until the entire insert is complete.

Row-level locking in InnoDB

Record Locks: Locks only one Record

Gap Locks: Locks the current record with the previous direct interval record of the current record.

Next-key Locks: Short for next-key Locks, a combination of Record Locks and Gap Locks.

Lock read statement

Lock S on read records:

SELECT … LOCK IN SHARE MODE;

Add X lock to read records:

SELECT … FOR UPDATE;

DELETE:

The DELETE operation is treated as a lock read to acquire the X lock.

There are two types of UPDATE operations:

  • The primary key value is not changed: the procedure is treated as a lock read to acquire the X lock.

  • Primary key modification: This is equivalent to an INSERT operation after a DELETE operation on the original record.

INSERT:

In general, the operation of inserting a new record is unlocked. Insert intent locks are sometimes added (for example, when a transaction inserts a record, it needs to determine whether the insert position is a GAP lock that has been added by another transaction, and if so, the insert operation needs to wait until the transaction with the GAP lock commits).

Two phase locking protocol

–

In daily work, in order to improve the performance of mysql, in addition to understanding the MVCC in the previous article, there is also a two-phase lock protocol.

Transaction A holds row locks on two records, both of which were released at commit time, so transaction B’s update is blocked until transaction A commits. In other words, in InnoDB transactions, row locks are added when they are needed, but they are not released immediately when they are not needed, they are released when the transaction ends.

This is a two-phase lock protocol (2PL). It is divided into two phases: unlock and add. All lock operations follow unlock.

Note:

2PL, two-phase locking protocol: mainly used for consistency and isolation in single-machine transactions.

2PC, two-phase commit protocol: mainly used for distributed transactions.

Don’t be confused with ！！！！

MySql InnoDB engine uses the two-order locking protocol to ensure the isolation and consistency of single transaction.

Share about lock here, welcome to exchange.