Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Table of contents

1. Briefly describe the lock of the database

2. Brief description of gap lock

3. How is row-level locking implemented in InnoDB

4. Under what circumstances will the database deadlock

5. Briefly describe the solution to database deadlock

---

1. Briefly describe the lock of the database

Locks are a key feature that distinguishes database systems from file systems. Locks are used to manage concurrent access to shared resources. Below we take the InnoDB engine of the MySQL database as an example to briefly introduce the characteristics of locks.

If a transaction T1 has already obtained a shared lock on row r, then another transaction T2 can immediately obtain a shared lock on row r, because reading does not change the data in row r. This situation is called lock compatibility. However, if another transaction T3 wants to obtain an exclusive lock on row r, it must wait for transactions T1 and T2 to release the shared lock on row r. This situation is calledIncompatible lockThe following figure shows the compatibility of shared locks and exclusive locks. It can be seen that X locks are not compatible with any locks, while S locks are only compatible with S locks. It should be noted that S locks and X locks are row locks, and compatibility refers to the compatibility of locks on the same record (row).

        Lock granularity：The InnoDB storage engine supports multi-granularity locking, which allows transactions to have row-level locks and table-level locks at the same time. In order to support locking operations at different granularities, the InnoDB storage engine supports an additional locking method called intention lock. Intention locks divide locked objects into multiple levels. Intention locks mean that transactions want to lock at a finer granularity.

The InnoDB storage engine supports intention locks in a concise design. Its intention lock is a table-level lock. The main purpose of the design is to reveal the lock type requested for the next row in a transaction. It supports two types of intention locks:

1. Intentional shared lock (IS Lock): The transaction wants to obtain shared locks for certain rows in a table.

2. Intention exclusive lock (IX Lock): The transaction wants to obtain exclusive locks on certain rows in a table.

Since the InnoDB storage engine supports row-level locks, intention locks will not block any requests except full table scans. Therefore, the compatibility between table-level intention locks and row-level locks is as follows:

        Locking Algorithm: The InnoDB storage engine has three row lock algorithms, which are:

1. Record Lock: lock on a single row record.

2. Gap Lock: Gap lock, lock

3. Next-Key Lock: Gap Lock+Record Lock, locks a range and the record itself.

Record Lock always locks the index record. If no index is set when the InnoDB storage engine table is created, the InnoDB storage engine will use the implicit primary key to lock. Next-Key Lock is a locking algorithm that combines Gap Lock and Record Lock. Under the Next-Key Lock algorithm, InnoDB uses this locking algorithm for row queries. The locking technology that uses Next-Key Lock is called Next-Key Locking, and its design is not intended to solve the Phantom Problem. Using this locking technology, a range is not locked, which is an improvement on the Predict Lock.

        About DeadlockDeadlock refers to a phenomenon in which two or more transactions are waiting for each other due to competition for resources during execution. Without external force, the transaction will not be able to proceed.

        Lock escalation: Lock escalation refers to reducing the granularity of the current lock. For example, the database can upgrade 1,000 row locks of a table to one page lock, or upgrade a page lock to a table lock.

The InnoDB storage engine does not have the problem of lock escalation. This is because it does not generate row locks based on each record. Instead, it manages locks based on each page accessed by each transaction, using a bitmap approach. Therefore, whether a transaction locks one or multiple records in a page, its overhead is usually the same.

2. Brief description of gap lock

The InnoDB storage engine has three row lock algorithms, and Gap Lock is one of them. Gap Lock is used to lock a range but does not include the record itself. Its purpose is to prevent multiple transactions from inserting records into the same range, which will cause phantom read problems.

3. How is row-level locking implemented in InnoDB

InnoDB row-level locks are implemented by locking the index items on the index. InnoDB uses row-level locks only when retrieving data through index conditions, otherwise, InnoDB uses table locks.

When locking certain rows in a table, different transactions can use different indexes to lock different rows. In addition, regardless of whether a primary key index, unique index, or common index is used, InnoDB will use row locks to lock data.

4. Under what circumstances will the database deadlock

Deadlock refers to a phenomenon in which two or more transactions are waiting for each other due to competition for resources during execution. Without external forces, the transaction will not be able to proceed. The following table demonstrates a classic deadlock situation, that is, A waits for B, and B waits for A. This deadlock problem is calledAB-BA deadlock。

5. Briefly describe the solution to database deadlock

The simplest way to solve the deadlock problem is timeout. That is, when two transactions are waiting for each other, when one waiting time exceeds a certain threshold, one of the transactions is rolled back and the other waiting transaction can continue.

In addition to the timeout mechanism, current databases also generally use the wait-for graph method for deadlock detection. Compared with the timeout solution, this is a more proactive deadlock detection method. The InnoDB storage engine also uses this method. The wait-for graph requires the database to save the following two types of information:

1. Lock information list;

2. Transaction waiting list;

A graph can be constructed from the above linked list. If there is a loop in this graph, it means there is a deadlock, so resources are waiting for each other. This is a more active deadlock detection mechanism. When each transaction requests a lock and waits, it will determine whether there is a loop. If so, there is a deadlock. Generally speaking, the InnoDB storage engine chooses to roll back the transaction with the smallest amount of undo.