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

Let's learn about which locking mechanisms there are in the MySQL database. I believe you will benefit a lot after reading it. There are not many words in the text. I hope this short article about the locking mechanism in the MySQL database is what you want.

Brief introduction of MySQL locking Mechanism

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 their own specific scenarios. Therefore, the locking mechanisms of various storage engines are also quite different.

In general, MySQL storage engines use three types of locking mechanisms: row-level locking, page-level locking, and table-level locking.

Row-level locking (row-level)

Row-level locks generally refer to exclusive locks, that is, locked rows cannot be modified or deleted, but can only be select by other sessions.

Exclusive lock is also called write lock, or X lock for short. As the name implies, exclusive lock cannot coexist with other locks. For example, if a transaction acquires an exclusive lock of a data row, other transactions can no longer acquire other locks of that row.

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 competition is also small, which can give applications as much concurrent processing ability as possible to improve the overall performance of some applications that need high concurrency.

Although there are great advantages in concurrent processing capabilities, row-level locking also brings a lot of disadvantages.

Because the granularity of locking resources is very small, there is a lot of work 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.

Table level locking (table-level)

Table-level lock, directly lock the entire table, while you lock, other processes cannot write to the table. If you are a write lock, other processes are not allowed to read.

In contrast to row-level locking, 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 negative effect of locking granularity is that the probability of resource contention is also very high, which greatly reduces the degree of concurrency.

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 locking and table-level locking, so the resource overhead required to acquire locks 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.

Summary

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.

With the decrease of the granularity of locking resources, the possibility that the access request of the application will encounter lock waiting will decrease, and the concurrency page of the system as a whole will increase.

In MySQL, table-level locking is used by some non-transactional storage engines such as MyISAM, MEmory, CSv, while row-level locking is mainly used by InnoDB storage engine and NDB Cluster storage engine, and page-level locking is mainly the locking mode of BerkeleyDB storage engine.

After reading this article on which locking mechanisms are available in MySQL database, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

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.