InnoDB storage engine – lock

What is the lock

Locks exist to support concurrent access to shared resources and to ensure data consistency and integrity.

The lock and the latch

The latch is a lightweight lock that requires a very short lock time. In InnoDB, there are two implementations of latch: mutex and read/write lock.

A lock object is a transaction that locks objects in the database, such as tables, pages, and rows. Locked objects are only released after the transaction commits or rollback.

The LATCH does not have a deadlock detection mechanism, but the LOCK does.

InnoDB stores locks in the engine

The type of lock

InnoDB implements two standard row-level locks

• Shared Lock (S Lock) allows multiple transactions to be read at the same time, but transaction writes block.
• An X Lock allows only one transaction to write, but blocks both reads and writes for the rest of the transaction.

For better locking, InnoDB supports intentional locking. What does that mean? It is divided into more fine-grained locking objects, which form an object organization tree. Each time a fine-grained lock is added, a coarser grained lock of the same type is added.

There are also two types of intentional locks: IX and IS

For example, if you want to put an X lock on a row, you’ll put an IX lock on the table. The intention lock is used to quickly determine whether there is a fine-grained conflict when the coarser-grained X/S lock is added.

If the table already has an IX lock, then at least one row in the table is locked by X, so adding X or S to the table is not allowed. At this point, you can make a quick judgment based on the table’s intention lock, rather than having to traverse the row lock.

So if X IS added to one row and S IS added to another row, even if two image locks IX and IS are added to the same table, the image locks will not conflict with each other. The image locks simply indicate that there IS a smaller size of X/S lock in the current size.

X/S are table locks, IS/IX are table level intention locks generated by locking rows. This table illustrates the compatibility between table level and table level intention locks

Consistent non-locked reads

A consistent non-locked read is an MVCC that reads a row of data without blocking because of the X lock on the row. According to the definition, the historical data of the row is read in this way, and the historical data is saved by snapshot.

The snapshot is implemented through the undo segment, which uses rollback transactions. Historical data is not changed, so you do not need to lock the snapshot. In addition, because a single row record can have multiple snapshots, it is called MVCC(multi-version concurrency control).

InnoDB uses this read mode by default for ReadCommited and RepeatableRead. However, the non-locked reads of the two isolation boundaries are implemented slightly differently, with the former requiring the latest snapshot to be read each time, and the latter requiring the version of the row data at the beginning of the transaction.

Consistency lock read

While the consistency of non-locked reads provides better concurrency with snapshots, there are times when you need to ensure strong data logic consistency, and you need to use the consistency of the locked version to lock reads.

To lock a read, we need to use a locked Select statement. InnoDB supports two types of lock Select:

select.for update
select.in share mode
Copy the code

The first one will put an X lock on the row read, and the second one will put an S lock on the row read. When using these two statements, you need to manually enable transaction commit.

Self growth and locking

To implement the +1 operation of the auto-increment primary key, you need to use the auto-Inc Locking mechanism. This lock is a table lock that is automatically released after the COMPLETION of the SQL statement inserted by auto-increment to improve performance, rather than ending the transaction.

Later, a self-growing implementation mechanism for lightweight mutex was introduced to improve performance.

Foreign keys and locks

To insert or update a foreign key, you first need to query the records in the parent table, that is, Select the full table. In this case, an S lock is placed on the parent table.

The algorithm of the lock

Three algorithms for row locking

InnoDB has three algorithms for row locking:

• Record Lock: Locks a single Record.
• Gap Lock: Locks a range but does not include the current record.
• Next Key Lock: Lock a range containing the current Record, equivalent to Record Lock+Next Key Lock.

RecordLock locks the index. If the column following the current WHERE has no index, then the primary key will be locked. The next-key Lock locks a range in order to solve the phantom read problem of repeatable reads, i.e. the amount of data read before and after is inconsistent.

When selecting a range, if the column following where has a unique index, a Record Lock is used instead of a next-key Lock to improve system concurrency. The same is true for primary key indexes; For secondary indexes, however, range locking is used to prevent other operations from adding or deleting within the range.

In addition, InnoDB also adds GapLock to the next key of the secondary index to prevent phantom reading problems.

When a range lock is performed, the primary key intervals contained in the locked interval are also locked together.

Now let’s clarify the scope of these three locks:

InnoDB introduced range locking to solve phantom reading, so let’s go a little further here.

• Why does row locking not solve phantom reading? Because row locks cannot lock rows that do not exist, inserts create rows that do not exist.

• Why lock the interval after the key? Because the insert operation is inserted after the current row when the insert key is the same, there is also a interval lock behind it, and of course the preceding interval needs to be locked.

So for a Select… For Update locks :(prev_id, curr_id] + (curr_id, next_id).

reference

1

2

3

For an insert operation, it determines whether the next value of the insert key is locked, and blocks if it is.

Solve the phantom read

A magic read is when a transaction inserts a new value into the Select key range during multiple Select sessions.

InnoDB’s default isolation level Repeatable Read supports next-key Lock to avoid magic reads.

The lock problem

Dirty read

Dirty data is not the same as dirty pages. Dirty pages are data that has not been flushed to disk, and dirty data are SQL operations that have not been committed.

A dirty read is when one transaction reads an uncommitted operation from another transaction.

Dirty reads violate isolation.

Unrepeatable read

During the time it takes one transaction to read a data set, another transaction performs a DML operation on the data set, causing the data read from the previous transaction to change (the total amount of data does not change).

Non-repeatable reads violate consistency because it is clear that one transaction has updated the data while another transaction is reading it.

Lost update

In short, the update made by transaction A is not committed, and transaction B makes another update that overwrites transaction A’s update, resulting in the loss of transaction A’s update.

The solution can be to use serial isolation levels.

A deadlock

A simple way to resolve a deadlock is to set a timeout and then roll back. The problem with this is that if a transaction takes a lot of undo, or a lot of weight, then a timeout rollback may not make sense.

So most engines today use wait graphs to determine deadlocks, just like the OS implementation. In simple terms, record the resources required for each transaction, and then use the pointer A->B to indicate that transaction A requires A lock for transaction B. Once a loop occurs, a deadlock exists.

This is a relatively active deadlock detection mechanism, every time a deadlock occurs, undo small transactions. The improved algorithm is judged by DFS-> loop implementation loop.

Lock escalation

If you add 10,000 locks to rows, it’s not as fast as locking tables directly. This is called lock escalation, but InnoDB does not support this, so no tables are omitted.