Introduction to MySQL database locking mechanism-Mysql Tutorial-php.cn

This article brings you an introduction to the MySQL database locking mechanism. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Introduction to MySQL locking mechanism

The database locking mechanism simply means that the database uses various shared resources to ensure the consistency of the data. A rule designed to make concurrent access orderly.
Any kind of database requires a corresponding locking mechanism, so MySQL is no exception.
MySQL database has a variety of data storage engines due to its own architectural characteristics. Each storage engine has different application scenario characteristics. In order to meet the requirements of their specific application scenarios, According to the requirements, the locking mechanism of each storage engine is optimized and designed for the specific scenarios they face. Therefore, the locking mechanisms of various storage engines are also quite different.
In general, MySQL storage engines use three types (levels) of locking mechanisms: row-level locking, page-level locking and table-level locking.

Row-level locking (row-level)

Row-level lock, generally refers to exclusive lock, that is Locked rows cannot be modified or deleted, and can only be selected by other sessions.
Exclusive lock is also called write lock, or X lock for short. As the name suggests, exclusive lock cannot coexist with other locks. If a transaction acquires an exclusive lock on a data row, other transactions will No other locks can be acquired on the row.
The biggest feature of row-level locking is that the granularity of the locked object is very small, which is also the smallest locking granularity achieved by currently major database management software.
Because the locking granularity is very small, the probability of lock resource competition is also small, which can give the application as much concurrent processing capability as possible and improve the overall performance of some high-concurrency application systems. .
Although it has great advantages in concurrent processing capabilities, row-level locking also brings many disadvantages.
Since the granularity of locking resources is very small, there are a lot of things that need to be done each time to acquire and release the lock, and the consumption will naturally be greater.
In addition, row-level locking is also the most prone to deadlock.

Table-level locking (table-level)

Table-level locking directly locks the entire table. While locked, other processes cannot write to the table. If you have a write lock, other processes are not allowed to read.
Contrary to row-level locking, table-level locking is the most granular locking mechanism among MySQL storage engines.
The biggest feature of this locking mechanism is that the implementation logic is very simple and has minimal negative impact on the system. So acquiring and releasing locks is very fast.
Since table-level locks lock the entire table at one time, the deadlock problem that plagues us can be avoided.
Of course, the negative impact of large locking granularity is that the probability of resource contention will be very high, resulting in a significant reduction in concurrency.

Page-level locking (page-level)

Page-level locking is a unique type of locking in MySQL Level is not too common in other database management software.
The characteristic of page-level locking is that the locking granularity is between row-level locking and table-level locking, so the resource overhead required to obtain the lock and the concurrent processing capabilities it can provide It is also somewhere between the two above.
In addition, page-level locking and row-level locking can also cause deadlock.

Summary

In the process of resource locking in the database, as the granularity of the locked resources decreases, The amount of memory required to lock the same amount of data is increasing, and the implementation algorithm will become more and more complex.
As the granularity of locked resources decreases, the possibility of application access requests encountering lock waits will also decrease, and the overall concurrency of the system will increase.
In MySQL, table-level locking is used by some non-transactional storage engines such as MyISAM, MEmory, CSv, etc., while row-level locking is mainly used by InnoDB storage engine and NDB Cluster storage. Engine, page-level locking is mainly the locking method of the BerkeleyDB storage engine.

The above is the detailed content of Introduction to MySQL database locking mechanism. For more information, please follow other related articles on the PHP Chinese website!