Shulou(Shulou.com)06/01 Report--

This article will explain in detail about the lock introduction in the mysql database. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Mysql locking mechanism is a rule designed by the database to ensure the consistency of data and make all kinds of shared resources orderly when they are accessed concurrently. Each storage engine of mysql uses three types of locking mechanisms: table-level locking, row-level locking, and page-level locking.

Lock detailed explanation

To put it simply, the database locking mechanism is a rule designed by the database to make all kinds of shared resources orderly when they are accessed concurrently in order to ensure the consistency of the data.

For any kind of database, there needs to be a corresponding locking mechanism, so MySQL is no exception. Due to the characteristics of its own architecture, MySQL database has a variety of data storage engines, and each storage engine aims at different application scenarios. In order to meet the needs of their own specific application scenarios, the locking mechanisms of each storage engine are optimized for the specific scenarios they face, so the locking mechanisms of each storage engine are also quite different.

Each storage engine of MySQL uses three types of locking mechanisms: table-level locking, row-level locking, and page-level locking.

Detailed introduction:

1. Page level locking (page-level)

Page-level locking is a unique locking level in MySQL, and it is not very common in other database management software.

The characteristic of page-level locking is that the locking granularity is between row-level lock and table-level lock, so the resource overhead required to obtain lock and the concurrent processing power that can be provided are also between the above two. In addition, page-level locking is the same as row-level locking, deadlocks occur.

In the process of database resource locking, with the decrease of the granularity of locking resources, the amount of memory needed to lock the same amount of data is more and more, and the implementation algorithm will become more and more complex. However, with the decrease of the granularity of locking resources, the possibility of lock waiting for application access requests decreases, and the overall concurrency of the system increases.

Page-level locking is mainly used by the BerkeleyDB storage engine.

2. Table-level locking (table-level)

Table-level locking is the largest granularity locking mechanism among MySQL storage engines. The most important feature of the locking mechanism is that the implementation logic is very simple and the negative impact of the system is the least. So the speed of acquiring and releasing locks is very fast. Because table-level locks lock the entire table at once, it is a good way to avoid the deadlock problem that bothers us.

Of course, the biggest negative effect of large locking granularity is that the probability of locking resource contention will be the highest, resulting in magnanimity greatly reduced.

Table-level locking is mainly used by some non-transactional storage engines such as MyISAM,MEMORY,CSV.

3. Row-level locking (row-level)

The biggest feature of row-level locking is that the granularity of locked objects is very small, and it is also the smallest locking granularity implemented by major database management software at present. Because the locking granularity is very small, the probability of locking resource contention is the smallest, which can give applications as much concurrent processing ability as possible and improve the overall performance of some applications that need high concurrency.

Although it has great advantages in concurrent processing ability, row-level locking also brings a lot of disadvantages. Because the granularity of locking resources is very small, more needs to be done each time the lock is acquired and released, and the consumption is naturally greater. In addition, row-level locking is also the most prone to deadlocks.

Row-level locking is mainly used by the InnoDB storage engine.

Deadlock

MyISAM table locks are deadlock free, because MyISAM always gets all the locks you need at once, either satisfying them or waiting, so there is no deadlock. But in InnoDB, except for the transaction composed of a single SQL, the lock is acquired step by step. When two transactions need to acquire the exclusive lock held by the other to continue to complete the transaction, this kind of cyclic lock waiting is a typical deadlock.

In the transaction management and locking mechanism of InnoDB, there is a special mechanism to detect the deadlock, which will detect the deadlock in a short time after the deadlock occurs in the system. When InnoDB detects a deadlock in the system, InnoDB selects the smaller transaction of the two transactions that caused the deadlock to roll back and forth, and the other larger transaction completes successfully.

Summary:

The characteristics of the three locks of MySQL can be roughly summarized as follows:

Table-level lock: low overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict and the lowest concurrency

Row-level locks: high overhead and slow locking; deadlocks occur; lock granularity is the smallest, lock conflict probability is the lowest, and concurrency is the highest.

Page lock: the overhead and locking time are between table lock and row lock; deadlocks occur; lock granularity is between table lock and row lock, and the concurrency is general.

Applicable: from the perspective of locks, table-level locks are more suitable for applications where queries are the main, 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 concurrent updates of a small amount of different data according to index conditions and concurrent queries, such as some online transaction processing (OLTP) systems.

This is the end of the introduction of locks in the mysql database. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.