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

This article introduces the relevant knowledge of "the basic principle of mysql lock mechanism". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

Today nothing to learn about the advantages and disadvantages of mysql three lock mechanism:

MySQL storage engines use three types (levels) of locking mechanisms: row-level locking, page-level locking, and table-level locking. Let's first analyze the characteristics of MySQL's three types of locking and their respective advantages and disadvantages.

● Row-level lock

The biggest characteristic of line-level locking is that the granularity of the locked object is very small, which is also the smallest locking granularity realized by all major 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.

● Table-level lock

As opposed to row-level locking, table-level locking is the most granular locking mechanism in MySQL's 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.

● 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, with the decrease of resource granularity, 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.

MySQL table-level locking is divided into two main types, one is read locking, and the other is write locking. In MySQL, these two types of locks are maintained through four queues: two for read and write locks currently in progress, and two for read-write locks awaiting progress, as follows:

·Current read-lock queue (lock->read)

·Pending read-lock queue (lock->read_wait)

·Current write-lock queue (lock->write)

·Pending write-lock queue (lock->write_wait)

read lock

A new client request to acquire a read-locked resource must satisfy two conditions:

1. The resource requested to be locked is not currently write-locked;

There is no higher priority write lock waiting in the Pending write-lock queue.

If the above two conditions are met, the request will be immediately approved and the relevant information will be stored in the Current read-lock queue, and if either of the above two conditions is not met, it will be forced to enter the Pending read-lock queue waiting for the release of resources.

write lock

When a client requests a write lock, MySQL first checks to see if information exists in the Current write-lock queue that locks the same resource. If the Current write-lock queue does not exist, check the Pending write-lock queue again. If it is found in the Pending write-lock queue, you also need to enter the waiting queue and suspend your thread to wait for the lock resource. On the other hand, if the Pending write-lock queue is empty, check the Current read-lock queue again, and if there is a lock, you also need to enter the Pending write-lock queue to wait. Of course, you may also encounter the following two special situations:

1. The type of lock requested is WRITE_DELAYED;

2. The type of lock requested is WRITE_CONCURRENT_INSERT or TL_WRITE_ALLOW_WRITE, while Current read lock is the lock type of READ_NO_INSERT.

When these two special cases are encountered, the write lock will be obtained immediately and entered into the Current write-lock queue. If there is already a write lock locking the same resource in the Current write-lock queue at the beginning of the first detection, then you can only enter the waiting queue to wait for the release of the corresponding resource lock. The priority rules for read requests and write lock requests in the write wait queue are mainly determined by the following rules:

1. WRITE write locks in the Pending write-lock queue block all read locks except READ_HIGH_PRIORITY;

2. READ_HIGH_PRIORITY Requests for a read lock can block all pending write locks in the write-lock queue;

3. Except for WRITE write locks, any other write lock in the Pending write-lock queue has lower priority than a read lock.

A write lock appears after the Current write-lock queue, blocking all locked requests except for the following:

1. A WRITE_CONCURRENT_INSERT write lock request may be allowed with the permission of some storage engines

2. When the write lock is WRITE_ALLOW_WRITE, all read and write lock requests except WRITE_ONLY are allowed

3. When the write lock is WRITE_ALLOW_READ, all read lock requests except READ_NO_INSERT are allowed

4. When the write lock is WRITE_DELAYED, all read lock requests except READ_NO_INSERT are allowed

5. When the write lock is WRITE_CONCURRENT_INSERT, all read lock requests except READ_NO_INSERT are allowed

The content of "mysql lock mechanism basic principle" is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.