A brief introduction to row locks, page locks and table locks in MySQL

MySQL can be subdivided into row locks, page locks, and table locks according to the granularity of locks.

Row lock

1. The locking granularity of row lock is the finest in MySQL. It is applied to the InnoDB storage engine and only adds the current row of the operation. Lock. In the case of concurrency, the probability of lock waiting is low, and a larger number of concurrency is supported, but the overhead is high, locking is slow, and deadlocks may occur.

2. There is a prerequisite for using row locks in InnoDB: indexing is required when retrieving data! Because InnoDB implements row locks by locking the index entries of the index.

3. InnoDB will use table locks when querying without index conditions, which may lead to a large number of lock conflicts when concurrency is large. In addition, row locks are locked on the index. In this case, although different records are accessed, but the same index item is used, lock conflicts may also occur.

Tip: Row locks may not necessarily be used if index retrieval is used, table locks may also be used. Because MySQL compares the costs of different execution plans, InnoDB uses table locks when a full table scan is more efficient than an index. Therefore, it is necessary to analyze lock conflicts in conjunction with the SQL execution plan.

4. Row locks will cause deadlocks, because in row locks, the lock is obtained gradually, which is mainly divided into two steps: locking the primary key index and locking the non-primary key index. For example: when two transactions are executed at the same time, one locks the primary key index and is waiting for other indexes; the other locks the non-primary key index and is waiting for the primary key index. A deadlock will occur. InnoDB can generally detect this kind of deadlock and make one transaction release the lock and roll back, and another acquire the lock to complete the transaction.

Table lock
The locking granularity of table lock is the coarsest in MySQL. It is applied in InnoDB and MyISAM engines and locks the entire current table. It is not suitable for high-concurrency scenarios, but it has low overhead, fast locking, no deadlock, and the highest probability of lock conflict.

Page lock
The granularity of page lock is between row lock and table lock. It is applied to BDB engine. The concurrency is average, and the cost and locking speed are also between row lock. and table lock.

The above is the detailed content of A brief introduction to row locks, page locks and table locks in MySQL. For more information, please follow other related articles on the PHP Chinese website!