Mysql lock mechanism is a rule designed by the database to ensure the consistency of data and to make various shared resources orderly when accessed concurrently. Each MySQL storage engine uses three types of locking mechanisms: table-level locking, row-level locking, and page-level locking.
Detailed explanation of lock
(Recommended tutorial: mysql tutorial)
The database lock mechanism is simply a rule designed by the database to ensure the consistency of data and to make various shared resources orderly when accessed concurrently.
Any kind of database requires a corresponding locking mechanism, so MySQL is naturally no exception. Due to the characteristics of its own architecture, the MySQL database has multiple data storage engines. Each storage engine has different application scenario characteristics. In order to meet the needs of their specific application scenarios, the locking mechanism of each storage engine is designed for The designs are optimized for the specific scenarios they face, so the locking mechanisms of each storage engine are also quite different.
MySQL storage engines use three types (levels) of locking mechanisms: table-level locking, row-level locking and page-level locking.
Detailed introduction:
1. Table-level locking (table-level)
Table-level locking is the largest granularity among MySQL storage engines locking mechanism. The biggest feature of this locking mechanism is that the implementation logic is very simple and brings 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 biggest negative impact of large locking granularity is that the probability of contention for locking resources will be the highest, which will greatly reduce the efficiency.
Table-level locking is mainly used by some non-transactional storage engines such as MyISAM, MEMORY, and CSV.
2. Row-level locking (row-level)
The biggest feature of row-level locking is that the granularity of the locked object is very small, which is also the locking granularity achieved by current major database management software. the smallest. Since the locking granularity is very small, the probability of contention for locking resources is also minimal, which can give the application as much concurrent processing capability as possible and improve the overall performance of some application systems that require high concurrency.
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, more things need to be done each time to acquire and release the lock, which naturally causes greater consumption. In addition, row-level locking is also the most prone to deadlock.
The InnoDB storage engine mainly uses row-level locking.
3. Page-level locking (page-level)
Page-level locking is a unique locking level in MySQL and 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 capability it can provide are also between the above. between the two. In addition, page-level locking and row-level locking will cause deadlock.
In the process of implementing resource locking in the database, as the granularity of the locking resources decreases, more and more memory is consumed to lock the same amount of data, and the implementation algorithm will also become more and more complex. The more complex it is. However, 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 also increase.
The main use of page-level locking is the BerkeleyDB storage engine.
Summary:
The characteristics of these three locks in MySQL can be roughly summarized as follows:
Table-level locks: low overhead, fast locking; no Deadlocks will occur; locking granularity is large, the probability of lock conflicts is the highest, and concurrency is the lowest;
Row-level locks: high overhead, slow locking; deadlocks will occur; locking granularity is the smallest, lock conflicts occur The lowest probability and the highest concurrency;
Page lock: The cost and locking time are between table locks and row locks; deadlocks will occur; the locking granularity is between table locks and row locks, and concurrency Average degree.
Applicable: From a lock perspective, table-level locks are more suitable for queries-based applications with only a small amount of data updated according to index conditions, such as Web applications; while row-level locks are more suitable for applications with a large number of Concurrently update a small amount of different data according to index conditions, and at the same time have concurrent query applications, such as some online transaction processing (OLTP) systems.
The above is the detailed content of What does mysql lock mean?. For more information, please follow other related articles on the PHP Chinese website!