Shulou(Shulou.com)05/31 Report--

This article introduces you to the principle of MySQL locking mechanism, the content is very detailed, interested friends can refer to it, I hope it can help you.

Database locking mechanism is simply a rule designed by the database to ensure the consistency of data and to make various shared resources orderly accessed concurrently. For any kind of database, there needs to be a corresponding locking mechanism, so MySQL is no exception.

MySQL database due to its own architecture characteristics, there are many kinds of data storage engines, each storage engine for the application scenario characteristics are not the same, in order to meet the needs of their specific application scenarios, each storage engine locking mechanism is optimized for the specific scenario they face, so the locking mechanism of each storage engine is also quite different.

MySQL storage engines use three types (levels) of locking mechanisms: table-level locking, row-level locking, and page-level locking.

Table-level locking

Table-level locking is the most granular locking mechanism among MySQL storage engines. The biggest characteristic of this locking mechanism is that the logic is very simple and the negative impact on the system is minimal. So lock acquisition and lock release are fast. Because table-level locks lock the entire table at once, we can avoid deadlock problems that plague us.

Of course, the biggest negative impact of locking granularity is that the probability of locking resource contention will be the highest, resulting in a large discount.

Table-level locking is mainly used by MyISAM, MEMORY, CSV and other non-transactional storage engines.

Row-level locking

The biggest characteristic of row-level locking is that the granularity of the locked object is very small, and it is also the smallest locking granularity realized by all major database management software at present. Because lock granularity is very small, the probability of lock resource contention is also minimal, which can give applications as much concurrency as possible and improve the overall performance of some applications that need high concurrency.

Although it has a large advantage in concurrency, row-level locking also brings many disadvantages. Since the granularity of locking resources is small, it takes more to acquire and release locks each time, and the cost is naturally greater. In addition, row-level locking is also the most prone to deadlock.

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

page-level locking

Page-level locking is a unique locking level in MySQL and is not common in other database management software. Page-level locking is characterized by a granularity between row-level and table-level locking, so the resource overhead required to acquire the lock and the concurrency it provides are also somewhere in between. In addition, page level locking, like row level locking, can cause deadlocks.

In the process of database resource locking, as the granularity of locking resource decreases, the amount of memory required to lock the same amount of data is more and more, and the implementation algorithm will become more and more complex. However, as the granularity of locked resources decreases, the likelihood that an application's access request will encounter a lock wait decreases, and the overall concurrency of the system increases.

The primary user of page-level locking is the Berkeley DB storage engine.

What is the principle of MySQL locking mechanism to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.