Three locking mechanisms of MySQL _ MySQL

Three locking mechanisms of MySQL: bitsCN.com

Three locking mechanisms of MySQL

The database locking mechanism is a rule designed by the database to ensure data consistency and enable the access of various shared resources to be orderly during concurrent access.

MySQL storage engines use three types of locking mechanisms:

1. row-level locking --- MyISAM, Memory, CSV

* The minimum granularity of the locked object and the minimum probability of resource contention have a great advantage in concurrent processing capability.

* 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, NDB Cluster

* The lock object has the largest granularity, and the logic is simple. it can quickly obtain and release locks. The entire table is locked at one time, which 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 locks:

Current read-lock queue (lock-> read) --- stores all threads holding the read lock and stores the lock in the chronological order obtained.

Pending read-lock queue (lock-> read_wait) --- stores threads waiting to lock resources

Current write-lock queue (lock-> write) --- stores all the threads holding the write lock and stores the lock in the chronological order obtained.

Pending write-lock queue (lock-> write_wait) --- stores the thread waiting to lock the resource

* MySQL locks up to 11 types of tables, which are defined by a system enumeration (thr_lock_type). The values are described as follows:

IGNORE --- internal interaction when a lock request occurs. no information is stored in the lock structure and queue.

UNLOCK --- release the interaction lock type of the lock request

READ --- normal READ lock

WRITE --- normal 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 of Concurrent Insert is not allowed.

WRITE_ALLOW_WRITE --- when the storage engine locks itself, mysqld allows other threads to get read or write locks, because even if resources conflict, the storage engine will know how to deal with them.

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 Read services throughout the process.

WRITE_CONCURRENT_INSERT --- the locking method used when the Concurrent Insert operation is in progress. when the lock is in progress, no read lock requests except READ_NO_INSERT will be blocked.

WRITE_DELAYED --- lock type when insert delayed is used

WRITE_LOW_PRIORITY --- display the declared low-level locking mode, which is generated by setting LOW_PRIORITY_UPDAT = 1.

WRITE_ONLY --- 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 --- BerkeleyDB

The lock granularity is between row-level locks and table-level locks. the required resource overhead and concurrency capabilities are also in the same way as row-level locks.

BitsCN.com