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

This article mainly introduces "what is the lock of MySQL". In the daily operation, I believe that many people have doubts about what the lock of MySQL is. The editor consulted all kinds of materials and sorted out a simple and easy-to-use method of operation. I hope it will be helpful to answer the doubt of "what is the lock of MySQL?" Next, please follow the editor to study!

When the database has concurrent transactions, data inconsistencies may occur, and some mechanisms are needed to ensure the order of access. Locking mechanism is such a mechanism.

Just like a hotel room, if people go in and out at will, there will be multiple people grabbing the same room, and the person who applies for the key can check in and lock up the room. Others can use it again only when he has finished using it.

The relationship between isolation level and lock

At the Read Uncommitted level, reading data does not require a shared lock, so that it does not conflict with the exclusive lock on the modified data

At the Read Committed level, the read operation requires a shared lock, but the shared lock is released after the statement is executed

At the Repeatable Read level, the read operation requires a shared lock, but the shared lock is not released before the transaction is committed, that is, the shared lock must be released after the transaction is completed.

SERIALIZABLE is the most restrictive isolation level because it locks the entire range of keys and holds the lock until the transaction completes.

What are the database locks according to the granularity of the lock? Locking Mechanism and InnoDB locking algorithm

In relational databases, database locks can be divided into row-level locks (INNODB engine), table-level locks (MYISAM engine) and page-level locks (BDB engine) according to the granularity of locks.

Locks used by the MyISAM and InnoDB storage engines:

MyISAM uses table-level locks (table-level locking).

InnoDB supports row-level locks (row-level locking) and table-level locks. The default is row-level locks.

Comparison of row-level locks, table-level locks and page-level locks

Row-level locks row-level locks are the finest locks in Mysql, indicating that locks are applied only to the rows of the current operation. Row-level locks can greatly reduce conflicts in database operations. The locking granularity is the smallest, but the locking overhead is also the largest. Row-level locks are divided into shared locks and exclusive locks.

Features: high cost, slow locking, deadlock will occur; locking granularity is the smallest, the probability of lock conflict is the lowest, and the degree of concurrency is the highest.

Table-level lock is the largest lock granularity in MySQL, which means locking the whole table of the current operation. It is easy to implement, low resource consumption, and is supported by most MySQL engines. Both MYISAM and INNODB, the most commonly used, support table-level locking. Table-level locking is divided into table shared read lock (shared lock) and table exclusive write lock (exclusive lock).

Features: low overhead, fast locking; no deadlock; large locking granularity, the highest probability of lock conflict and the lowest concurrency.

Page-level lock is a lock whose locking granularity is between row-level lock and table-level lock in MySQL. The speed of table-level lock is fast, but the conflict is more, the row-level conflict is less, but the speed is slow. So take the eclectic page level and lock the adjacent set of records one at a time.

Features: overhead and locking time are between table lock and row lock; deadlock occurs; lock granularity is between table lock and row lock, and the concurrency is general.

What kind of locks does MySQL have according to the types of locks? Doesn't locking like the above hinder concurrency efficiency a little bit?

In terms of the types of locks, there are shared locks and exclusive locks.

Shared lock: also known as read lock. When the user wants to read the data, add a shared lock to the data. Multiple shared locks can be added at the same time.

Exclusive lock: also called write lock. When the user wants to write the data, add an exclusive lock to the data. Only one exclusive lock can be added, and he and other exclusive locks and shared locks are mutually exclusive.

Using the above example, there are two kinds of user behavior, one is to see the house, it is acceptable for multiple users to look at the house together. One is a real one-night stay, during which neither those who want to check in nor those who want to see a house are allowed.

The granularity of locks depends on the specific storage engine. InnoDB implements row-level locks, page-level locks, and table-level locks.

Their locking overhead is from large to small, and their concurrency ability is also from large to small.

How is the row lock of the InnoDB engine in MySQL implemented?

Answer: InnoDB completes row locking based on index

Example: select * from tab_with_index where id = 1 for update

For update can complete the row lock according to the condition, and id is a column with an index key. If id is not an index key, then InnoDB will complete the table lock, and concurrency will be impossible.

There are three algorithms for locking the InnoDB storage engine

Record lock: lock on a single row record

Gap lock: a gap lock that locks a range, excluding the record itself

Next-key lock:record+gap locks a range that contains the record itself

Related knowledge points:

Innodb queries for rows use next-key lockNext-locking keying to solve the Phantom Problem phantom reading problem when the index of the query contains unique attributes, the next-key lock is downgraded to a record keyGap lock designed to prevent multiple transactions from inserting records into the same scope, which leads to phantom reading problems. There are two ways to explicitly close the gap lock: (except for foreign key constraints and uniqueness checking, only record lock is used in other cases) A. Set the transaction isolation level to RC B. What is a deadlock if the parameter innodb_locks_unsafe_for_binlog is set to 1? How to solve it? Deadlock refers to the phenomenon that two or more transactions occupy each other on the same resource and request to lock each other's resources, resulting in a vicious circle. Common ways to solve deadlocks 1. If different programs access multiple tables concurrently, try to agree to access the tables in the same order, which can greatly reduce the chance of deadlock. 2. In the same transaction, try to lock all the resources needed at once to reduce the probability of deadlock; 3. For business parts that are very easy to produce deadlocks, you can try to use upgrade locking granularity to reduce the probability of deadlocks through table-level locking; what are the optimistic and pessimistic locks that can be used in distributed transaction locks or databases that use optimistic locks if the business is not handled well? How did it happen? The task of concurrency control in database management system (DBMS) is to ensure that when multiple transactions access the same data in the database at the same time, the isolation and unity of transactions and the unity of the database will not be destroyed. Optimistic concurrency control (optimistic lock) and pessimistic concurrency control (pessimistic lock) are the main technical means of concurrency control. Pessimistic locks: assume that concurrency conflicts occur, shielding all operations that may violate data integrity. When the data is queried, the transaction is locked up until the transaction is committed. Implementation: optimistic locking using the locking mechanism in the database: assuming that no concurrency conflicts will occur, only check whether the data integrity is violated when submitting the operation. Lock the transaction when you modify the data, and lock it by version. Implementation: le will generally use the version number mechanism or CAS algorithm to achieve. The usage scenarios of the two locks are introduced from above. We know that each of the two locks has its own advantages and disadvantages, and one should not be considered better than the other. For example, optimistic locks are suitable for situations with less writes (multi-read scenarios), that is, when conflicts really rarely occur, this can save the overhead of locks and increase the overall throughput of the system. However, in the case of overwriting, conflicts often occur, which will cause upper-layer applications to continue to retry, which degrades performance, so it is more appropriate to use pessimistic locks in scenarios with multiple writes. At this point, the study of "what is the lock of MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.