Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to understand MySQL lock". In daily operation, I believe many people have doubts about how to understand MySQL lock. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubt of "how to understand MySQL lock"! Next, please follow the editor to study!

First, the understanding of MySQL locks

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.

Second, 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 has been 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.

Third, 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, which default to row-level locks.

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

Row-level lock: the finest-grained lock 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: a lock with the largest lock granularity in MySQL, which means locking the entire table of the current operation. It is easy to implement, consumes less resources, 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: 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 locks and row locks; deadlocks occur; lock granularity is between table locks and row locks, 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 known as write lock, when the user wants to write data, add 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.

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

InnoDB completes the row lock based on the 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.

6. There are three algorithms for locking the InnoDB storage engine.

1.Record lock: lock on a single row record

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

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

7. Relevant knowledge points

Innodb uses next-key lock for row queries

Next-locking keying in order to solve the problem of Phantom Problem phantom reading

Downgrade next-key lock to record key when the index of the query contains unique attributes

Gap locks are designed to prevent multiple transactions from inserting records into the same scope, which can lead 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) A. Set the transaction isolation level to RCB. Set the parameter innodb_locks_unsafe_for_binlog to 1

What is a deadlock? 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 solutions to 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 prone to deadlocks, you can try to use upgrade locking granularity to reduce the probability of deadlocks through table-level locking.

If the business is not handled well, you can use distributed transaction locks or optimistic locks.

What are the optimistic and pessimistic locks of the database? 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: use the locking mechanism in the database

Optimistic locks: assuming that there are no concurrency conflicts, only check for violations of data integrity when committing operations. Lock the transaction when you modify the data, and lock it by Version. Implementation: generally use the version number mechanism or CAS algorithm to achieve.

Usage scenarios of two kinds of locks

From the above introduction of the two kinds of locks, we know that each has its own advantages and disadvantages, and one should not be considered better than the other. For example, optimistic locks are suitable for situations where there are few writes (multi-read scenarios), that is, when conflicts really rarely occur, this saves the overhead of locks and increases 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 on "how to understand MySQL locks" 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.