MySQL database lock mechanism

The MySQL database lock mechanism is called a lock. To ensure data consistency, it is a rule that allows concurrent access to shared resources to become orderly. Different MySQL storage engines have different lock mechanisms or lock implementations. In general, three lock levels are used: row-level and page-level) table-level locks gradually reduce the granularity of resources locked in sequence. As the granularity of locked resources decreases, the number of memories required to lock the same data increases, algorithms are becoming more and more responsible, but at the same time, applications may encounter lock waits, and the overall distribution of the system is also increasing; Table-level locks, the maximum granularity of the storage engine lock level, the implementation is simple, the speed of obtaining and Releasing locks is fast, and the deadlock is also avoided. However, it also brings about the lock resource competition problem, leading to a low concurrency; table-level locks are divided into read locks and write locks. MySQL maintains these two locks through four queues. Two stores the read and write information currently being locked, and two stores the information waiting for read and write, the four queues are: read-> lock \ read_wait-> lock-> write-> lock/write_wait-> lock; read lock, the resource to be locked in the current request is not written or locked. There is no higher priority write lock wait in the write lock wait queue. Immediately go to read-> lock. If not, go to read_wait-> lock; write lock, the resource to be written in the current request is not locked by writing, and is not in the write lock wait queue. Then, check whether the resource is in the read wait queue. If yes, enter the write wait queue. If no, enter the current write queue. Table-level locks are usually used by some non-transactional storage engines, such as MyISAM, Memory, CSV, and other page-level locks. After all, there is a special lock level in MySQL, the granularity is between Row-level locks and table-level locks. The burden of obtaining and releasing lock resources is also between Row-level locks and table-level locks. Concurrency is also between Row-level locks and table-level locks, like row-level locks, page-level locks may also experience deadlocks. The primary reason is that the storage engine of BerkeleyDB is the locking method. Row-level locks are the locks with the minimum granularity implemented by RMDB, the minimum probability of resource competition is that it can provide as much concurrent processing as possible to provide application performance. But at the same time, because of the small granularity, the maximum consumption is required to obtain and release the lock. In addition, row-level locks are the most prone to deadlocks. Row-level locks are implemented by the storage engine rather than MySQL, such as the distributed storage engine NDBCluster of InnoDB and MySQL; innoDB row-level locks are also divided into two types: Shared locks and exclusive locks. InnoDB also introduces the concept of intention locks (Table-level locks), so there are intention-to-share locks and intention-to-exclusive locks, therefore, InnoDB actually has four types of locks: Shared locks (S), exclusive locks (X), intention shared locks (IS), and intention exclusive locks (IX ); if some resources already have a shared lock, you can add other shared locks to these resources, but cannot add exclusive locks. If some resources already have an exclusive lock, therefore, no other exclusive and shared locks can be added to these resources. You can lock the lock only after the current lock is released and the lock resource is obtained. However, you can add an intent lock to the lock, that is, if the waiting transaction wants to add an exclusive lock, you can add an intention exclusive lock to the table where the row is locked. If the waiting transaction wants to add a shared lock, the intention share lock can be added to the table where the row is locked. The InnoDB Lock implementation and Orac The locks of le vary greatly. In general, Oracle locks data based on the transaction slots on the physical block where a row of records is located, innoDB locking is achieved by marking the lock information on the airspace space before and after the first index of the data record, therefore, InnoDB locks are called "Next Key Locking". A major weakness of the GAP lock is that when a range of Key values are locked, even if some key values do not exist, they are still locked. As a result, records of such key values cannot be inserted. Of course, this situation only occurs in the default transaction isolation level repeatable-read of InnoDB. If the transaction isolation level of InnoDB is reduced to read commited, this will not happen. The explanation provided by InnoDB is that the gap lock can prevent Phantom reads, but in fact the gap lock can only block some phantom reads, but not all. Another major risk of Locking through indexes is that when Query cannot use indexes, row-level locks will be upgraded to table-level locks and the entire data table will be locked, this reduces the concurrency performance. Deadlocks. Row-level locks may cause deadlocks, and InnoDB is no exception. InnoDB has a mechanism for Detecting deadlocks, provided that the storage engines involved in the deadlock scenario are all InnoDB. If InnoDB detects a deadlock, transaction rollback with the minimum number of data rows will be affected. So is there any way to avoid the trouble caused by InnoDB gap locks? There are three methods: 1. Reduce concurrency to avoid resource competition, but this will reduce the performance of the application to a certain extent; 2. Modify the default transaction isolation level of InnoDB, modify repeatable-read to read commited. Of course, modifying the transaction isolation level brings about another risk that the transaction cannot be read repeatedly. 3. When querying data, you must use indexes, avoid full table scan. when inserting data, use the increasing index field (that is, the value of each inserted index field must be increased). Related documents: http://www.bkjia.com/database/201202/118620.htmlhttp://www.bkjia.com/database/201202/120824.html