Three locking mechanisms of MySQL

Source: Internet
Author: User
Tags lock queue

The three locking mechanisms of MySQL database locking mechanism is a rule designed by the database to ensure data consistency and enable the access of various shared resources in an orderly manner during concurrent access. MySQL storage engines use three types of locking mechanisms: 1. row-level locking --- MyISAM, Memory, and CSV * have the minimum granularity of locked objects and the minimum probability of resource contention. row-level locking has a great advantage in concurrent processing capabilities. * The disadvantage is that there are many things to be done to obtain and release locks each time, resulting in high consumption and the most prone to deadlocks. 2. table-level locking --- Innodb and NDB Cluster * have the maximum granularity of objects to be locked. The logic is simple and the process of obtaining and Releasing locks is fast. The entire table is locked at one time, it can avoid deadlocks. * The disadvantage is that resource contention has the highest probability, resulting in a high degree of concurrency reduction. * There are two types: read locks and write locks. * Four lock queues are used to maintain these two types of locks: Current read-lock queue (lock-> read) --- stores all the threads holding the read lock and stores the Pending read-lock queue (lock-> read_wait) in the order of time the lock is obtained) --- store the Current write-lock queue (lock-> write) thread waiting to lock the resource --- store all threads holding the write lock, pending write-lock queue (lock-> write_wait) is stored in the order of time when the lock is obtained. --- the thread that is waiting to lock the resource * MySQL can implement up to 11 types of table locks, defined by a system enumeration volume (thr_lock_type), each value is described as follows: IGNORE --- internal interaction when a lock request occurs, there is no information stored in the lock structure and queue UNL OCK --- release lock request interaction use lock type READ --- General READ lock WRITE --- General WRITE lock READ_WITH_SHARED_LOCKS --- used in Innodb, produced by the following method: SELECT... lock in share mode READ_HIGH_PRIORITY --- high-priority read LOCK READ_NO_INSERT --- the LOCK WRITE_ALLOW_WRITE of Concurrent Insert is not allowed --- when the storage engine handles the LOCK by itself, mysqld allows other threads to get read or write locks, because even if resources conflict, the storage engine will also know how to handle WRITE_ALLOW_READ --- this lock occurs when the TABLE is DDL (alter table ...) mySQL allows other threads to obtain read locks, because MySQL implements this function by recreating the entire table and then RENAME it. The original table can still provide the read service WRITE_CONCURRENT_INSERT --- the locking method used when the Concurrent Insert is in progress. When the lock is in progress, no read lock requests except READ_NO_INSERT will be blocked WRITE_DELAYED --- the lock type WRITE_LOW_PRIORITY when insert delayed is used --- the declared Low-Level Lock mode is displayed, WRITE_ONLY is generated by setting LOW_PRIORITY_UPDAT = 1. When a lock exception is interrupted during the operation, the system needs to CLOSE the TABLE. In this process, the lock type is WRITE_ONLY 3. page-level locking --- the granularity of BerkeleyDB locking is between Row-level locking and table-level locking, and the resource overhead and concurrency capabilities are also between the two, like row-level locks, deadlocks occur.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.