MySQL lock common knowledge points & interview questions summary

Excerpted from "MySQL Common Knowledge Points & Interview Questions Summary"

Do you understand table-level locks and row-level locks? What's the difference?

MyISAM only supports table-level locking, and one lock locks the entire table, which is very poor in the case of concurrent writing.

InnoDB supports not only table-level locking, but also row-level locking. The default is row-level locking. The granularity of row-level locks is smaller, and only related records can be locked (locking one or more rows of records), so InnoDB has higher performance for concurrent write operations.

Comparison of table-level locks and row-level locks :

• Table-level lock: The lock with the largest locking granularity in MySQL is a lock for non-indexed fields. It locks the entire table of the current operation. It is simple to implement, consumes less resources, locks quickly, and will not die. Lock. It has the largest locking granularity, the highest probability of triggering lock conflicts, and the lowest concurrency. Both MyISAM and InnoDB engines support table-level locks.
• Row-level lock: The lock with the smallest locking granularity in MySQL is the lock for the index field, and only locks the record of the current operation. Row-level locks can greatly reduce conflicts in database operations. Its locking granularity is the smallest and the concurrency is high, but the overhead of locking is also the largest, and the locking is slow and deadlocks will occur.

What are the precautions for the use of row-level locks?

InnoDB's row locks are for index fields, and table-level locks are for non-index fields. When we execute the UPDATE , DELETE statement, if the field in the WHERE condition does not hit the index or the index fails, it will result in scanning the whole table to the table. All records are locked. This is often encountered in our daily work development, we must pay more attention! ! !

However, in many cases, even if an index is used, it is possible to perform a full table scan because of the MySQL optimizer.

What about shared locks and exclusive locks?

Whether it is a table-level lock or a row-level lock, there are two types of shared locks (Share Lock, S lock) and exclusive locks (Exclusive Lock, X lock):

• Shared lock (S lock) : Also known as a read lock, a transaction acquires a shared lock when reading records, allowing multiple transactions to acquire it at the same time (lock compatibility).
• Exclusive locks (X locks) : Also known as write locks/exclusive locks, transactions acquire exclusive locks when modifying records, and multiple transactions are not allowed to acquire them at the same time. If an exclusive lock has been added to a record, other transactions cannot add any type of lock to this transaction (lock incompatibility).

Exclusive locks are not compatible with any locks, shared locks are only compatible with shared locks.

	S lock	X lock
S lock	Do not conflict	conflict
X lock	conflict	conflict

Due to the existence of MVCC, InnoDB will not add any locks for general SELECT statements. However, you can explicitly add shared locks or exclusive locks with the following statement.

 # 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

What does intent lock do?

If a table lock needs to be used, how to judge that the records in the table have no row lock? It is definitely not possible to traverse line by line, and the performance is too poor. We need to use something called an intent lock to quickly determine whether a table lock can be used on a table.

Intentional locks are table-level locks, and there are two types:

• Intention Shared Lock (IS Lock) : The transaction intends to add a shared lock (S lock) to some tables in the table, and the IS lock of the table must be obtained before adding a shared lock.
• Intention Exclusive Lock (IX lock) : The transaction intends to add an exclusive lock (X lock) to some records in the table, and the IX lock of the table must be obtained before adding an exclusive lock.

The intent lock is maintained by the data engine itself, and the user cannot manually operate the intent lock. Before adding a shared/exclusive lock to a data row, InooDB will first obtain the corresponding intent lock of the data table where the data row is located.

Intent locks are compatible with each other.

	IS lock	IX lock
IS lock	compatible	compatible
IX lock	compatible	compatible

Intentional locks and shared locks and exclusive locks are mutually exclusive (here refers to table-level shared locks and exclusive locks, intentional locks are not mutually exclusive with row-level shared locks and exclusive locks).

	IS lock	IX lock
S lock	compatible	mutually exclusive
X lock	mutually exclusive	mutually exclusive

The description corresponding to the book "InnoDB Storage Engine for MySQL Technology" should be a typo.

What types of row locks does InnoDB have?

MySQL InnoDB supports three row locking methods:

• Record Lock : Also known as a record lock, it is a lock on a single row record.
• Gap Lock : Locks a range, excluding the record itself.
• Next-key Lock : Record Lock+Gap Lock, locks a range, including the record itself. Record locks can only lock existing records. To avoid inserting new records, you need to rely on gap locks.

InnoDB's default isolation level REPEATABLE-READ (repeatable) can solve the phantom read problem, mainly in the following two situations:

• Snapshot read : The MVCC mechanism ensures that no phantom read occurs.
• Current read : Use Next-Key Lock to lock to ensure no phantom read.