Shulou(Shulou.com)06/02 Report--

How to deeply understand all kinds of mysql locks, I believe that many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Overview of locks

A lock is a mechanism for a computer to coordinate multiple processes or threads and access a resource

In the database, in addition to the contention of traditional computer resources (such as cpu, RAM, Icano, etc.), data is also a resource shared by many users. If the consistency of data concurrent access is guaranteed, validity is a problem that all databases must solve, and lock conflicts are also an important factor affecting the performance of database concurrent access. From this point of view, locks are particularly important for databases. It is also more complex to classify the granularity of the database operation: the entire table row lock is locked during the operation: the type read lock (shared lock) for the data operation of the current operation row is locked during the operation: for the same data, multiple reads can be performed simultaneously without affecting the write lock (exclusive lock): before the operation is completed It blocks other write and read locks mysql locks

Compared with other databases, the locking mechanism of Mysql is relatively simple, and its most prominent feature is that different storage engines support different locking mechanisms.

Different storage engines support lock level storage engine table level lock row level lock page lock MyISAM does not support InnoDB support MEMORY support does not support BDB whether support lock introduction

It is difficult to generalize which lock is better, and it is necessary to analyze which lock is more appropriate according to the specific application scenario.

Lock type features table-level locks are biased to MyISAM storage engine with low overhead and fast locking; deadlocks do not occur; locking granularity is large, the probability of sending lock conflicts is the highest, and the concurrency is the lowest. Row-level locks tend to the InnoDB storage engine with high overhead and slow locking; deadlocks occur; locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of concurrency is the highest. Page lock overhead and locking time are between table lock and row lock; deadlocks occur; locking granularity is between table lock and row lock, and the concurrency is average. How to add a MyISAM table lock MyISAM automatically locks all tables involved before executing the query statement (select), and automatically adds write locks to the tables involved before performing update operations (update, delete, insert, etc.). This process does not require user intervention, so users generally do not need to directly use the LOCK TABLE command to display locking, unlocking and reading locks on MyISAM tables: lock table table_name read;-- unlock unlock tables Add write lock: lock table table_name write; add write lock the current session can read and write operations, other sessions will be blocked (waiting) state read lock limit write, write lock limit read and write lock competition 1. Read operations on the MyISAM table will not block other users' read requests to the same table, but will block write requests to the same table 2. The write operation to MyISAM will block other users' read and write operations to Table 1 table. 3. MyISAM lock scheduling is write priority. Not suitable as a master table, a large number of updates from other threads after writing the lock will make it difficult for the query to get the lock and may cause permanent blocking. Lock usage view lock usage show open tables; view table lock show status like 'table_locks%' Table_locks_immediate: the number of times a table lock can be acquired. Each time the lock is acquired immediately, the value is added 1 Table_locks_waited: the number of times the lock needs to wait cannot be acquired immediately. If you wait for each time, you can add 1 to the value to judge the serious table level lock contention. InnoDB lock

The biggest difference between InnoDB and MyISAM has some highlights: one is to support transactions, and the other is to adopt row-level locks.

Row lock shared lock: also known as read lock, multiple transactions can share a lock, but can only read, not write exclusive lock: also known as write lock, lock does not share, the transaction that cannot acquire the lock cannot read and write if update, delete and insert statements are performed InnoDB automatically adds exclusive locks to the dataset involved. Adding locks to queries without any lock display for different select statements adds a shared lock: select * from table_name where... Lock IN SHARE MODE add exclusive lock: select * from table_name where... FOR UPDATE lock upgrade index invalidation, row lock upgrade table lock where after no index is also upgraded to table lock gap lock

InnoDB will also add a lock to data that does not have a gap, which is called a gap lock.

Lock contention show status like 'innodb_row_lock%'; Innodb_row_lock_current_waits is currently waiting for the number of locks the total length of Innodb_row_lock_time locks the average length of Innodb_row_lock_time_avg locks the maximum length of Innodb_row_lock_time_max locks the total number of times the Innodb_row_lock_waits system has waited since it was started

The innoDB storage engine implements the row lock. Although the performance consumption of the locking mechanism may be higher than the table lock, it is much better than the MyISAM table lock in terms of overall concurrency processing capacity. When the system concurrency is relatively high, the overall performance of InnoDB and MyISAM will have obvious advantages.

Optimization suggests that all data retrieval can be done through indexes as far as possible, avoid upgrading non-indexed row locks to table locks, reasonably design indexes, minimize the scope of locks, minimize index conditions and index ranges, avoid gap locks and control transaction size as far as possible, reduce locking resources and time length as far as possible, use low-level transaction isolation (business needs to meet requirements) to read the above content. Have you learned how to understand the various locks of mysql in depth? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.