MySQL lock analysis into depth

This is the second day of my participation in the First Challenge 2022

👨💻 blog home page: author home page 👍 If you think the article is good, you can click to like it and follow us

What is a MySQL lock? What other types of locks are there?

A MySQL lock means that only one thread can access a resource at a time. In addition to the use of traditional computing resources (such as CPU, RAM, I/O, etc.) in a database, data is also a resource shared by many users. How to ensure the consistency and effectiveness of data access is a problem that all databases must solve lock conflict is also an important factor affecting the performance of database concurrent access.

The classification of the lock

Distinguish between types of data operations (read and write)

Read lock (shared lock) : Multiple read operations can be performed simultaneously for the same data without affecting each other.

Write lock (exclusive lock) : It blocks other write locks and read locks until the current write operation is complete.

From the granularity of operations on data

Table level lock: table level lock is the largest lock granularity in MySQL. It means that the entire table of the current operation will be locked.

Row-level lock: Row-level lock is the most fine-grained lock in MySQL. It means that only the row of the current operation is locked.

Page-level locking: page-level locking is the middle of row-level locking and table-level locking in MySQL, which locks one set of adjacent records at a time.

In terms of concurrency (optimistic and pessimistic locking is an idea)

Pessimistic lock: Conservative (pessimistic) attitude towards data being modified by external (including other current transactions of the system and transactions from external systems), so data is locked during the entire process of data processing. Pessimistic locking can be interpreted as assuming the worst case scenario, every time you try to fetch data it will be modified, so every time you fetch data it will be locked.

Optimistic lock: Optimistic lock assumes that data does not cause conflicts in general. Therefore, data conflicts are officially detected only when data is submitted for update. If a conflict is found, an error message is returned and services are tried again. You can think of optimistic locking as assuming that the best case scenario is that every time you pick up data, it won’t be modified by someone else, so it won’t be locked.

Gap lock and intention lock

Gap lock: In conditional queries, such as where ID >100, InnoDB will lock the index entries of existing data records that meet the criteria; Records whose key values are in the condition range but do not exist are called “gaps”, which are mainly used to prevent phantom reads.

Intention lock: Intention lock IS classified into intention shared lock (IS) and intention exclusive lock (IX). Intention lock IS to indicate that a transaction IS locking or will lock a row in a table.

Row locks versus table locks

Table level lock is a lock with the largest granularity in MySQL. It means to lock the entire table of the current operation. It is simple to implement. The most common MYISAM and INNODB both support table-level locking. It is characterized by low overhead and fast locking. No deadlocks occur; The locking granularity is large, the probability of locking conflict is high, and the concurrency is low.

Row-level lock is the most fine-grained lock in MySQL, which means that only the current operation row is locked. Row-level locking can greatly reduce conflicts in database operations. Moreover, the locking granularity is the smallest, but the locking cost is also the largest. It is characterized by large overhead, slow locking, and even deadlock; The locking granularity is the lowest, the probability of locking is the lowest, and the concurrency is the highest. InnoDB locks rows by locking index entries. InnoDB uses row-level locks only when index conditions are used to retrieve data. Otherwise InnoDB will use table locks.

InnoDB deadlock

Deadlocks occur when two or more transactions hold and request locks on each other in a circular dependency relationship. Deadlocks can also occur when multiple transactions simultaneously lock the same resource. In a transactional system, deadlocks are real and cannot be completely avoided. So what’s the solution? InnoDB automatically detects transaction deadlocks, immediately rolls back one of the transactions, and returns an error. It selects the simplest (and therefore least costly) transaction to roll back based on a mechanism.

How do I avoid deadlocks

The essence of locking is that resources compete with each other and wait for each other. It is often caused by the inconsistent sequence of locking two or more sessions.

How to avoid it effectively?

1, in the program, when operating multiple tables, try to access in the same order (to avoid waiting loop)

2. When batch operating single table data, sort the data first (to avoid waiting loop). A thread id: 1,10,20 is locked in sequence, and B thread id: 20,10,1

Select for UPDATE ==> INSERT ==>update = INSERT into update on duplicate key

4, try to use indexes to access data, avoid operations without WHERE conditions, to avoid the impact of gap locks on concurrency. 1,10,20 equivalent to where id in(1,10,20) range query id>1and id<20

5. Avoid running multiple scripts for reading and writing the same table at the same time, and pay special attention to statements with large data flows for locking operations; We often have timed scripts so that they don’t run at the same time.

Clearance lock

A gap lock definition: A gap lock is a lock on a gap between index records

The RR isolation level ensures that the read records are locked (record lock) and the read range are locked. New records that meet the query conditions cannot be inserted (clearance lock) and no phantom read exists.

Locking rules:

1, the basic unit of lock is next-key lock, next-key lock is the front open and the back closed interval

2. Only objects accessed during the search will be added to the lock

3, next key lock is degraded to behavior lock

4. The next-key lock degrades to a gap lock when the last value does not meet the equivalence condition when the equivalent query on the index is traversed right

Equivalent query gap lock

Build table test

CREATE TABLE `test` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`) ) ENGINE=InnoDB; Insert into t values (0, 0), (6,6,6), (10,10,10), (15,15,15), (20,20,20), (30,30,30);Copy the code

Select * from test where id=7; select * from test where id=7; select * from test where id=7; According to rule 3, this is an equivalent query, and if id=10 does not satisfy the query condition, the next key lock will degenerate into a gap lock, and eventually the lock range is (5,10), so SessionB tries to insert a gap lock with id=8, but SessionC works.

Non-unique index equivalent lock

According to rule 1, the lock unit is next-key lock, so (0,6) is assigned a next-key lock. Since a is a common index, the access to the record a=5 cannot be stopped immediately. You need to traverse right until a=10. According to rule 2, everything accessed must be locked, so (5,10) must be next-key locked. According to rule 4, the equivalence judgment is traversed to the right, and the last value does not meet the equivalence condition a=6, so it degenerates into a gap lock (5,10). According to rule 2, only objects accessed are locked, but this query uses an override index that does not access the primary key index, so there are no locks on the primary key index, which is why SessionB succeeds. In this case, the lock in share mode only covers the index. If the lock is for update, the system will assume that you are going to update the index next, and the system will automatically lock the primary key rows that meet the condition. In this case, SessionB will block. If you want to use lock in share mode to lock rows from being updated, you have to bypass the optimization of overwriting indexes and add columns in the query field that do not exist in the index.

How to paginate MySQL database?

In the back-end development, in order to prevent the memory and disk IO overhead caused by loading too much data at one time, it often needs to be displayed in pages. This time needs to use the MySQL LIMIT keyword. But do you think LIMIT paging is all right? LIMIT can cause deep paging problems when there is a large amount of data.

Explain select * from user where age>10 and age<90000000 order by age desc LIMIT 8000000000,10000;

Possible values and meanings for the Extra column of the execution plan

1, Using WHERE: indicates that the optimizer needs to query data back to the table through the index

2, Using index: that is, overwrite index, indicating that direct access to the index is enough to obtain the required data, without the need to index back to the table, usually through the query field to create a joint index

3. Using index condition: a new feature added after version 5.6 is index push-down, which is a major optimization for MySQL to reduce the number of table returns.

MySQL will recall all data to memory for sorting, which consumes more resources.

The solution

With primary key index optimization, Explain SELECT * from user where ID >{maxId} age>10 and age<90000000 ORDER by age desc LIMIT 8000000000,10000; .

Through Elastic Search, Search engine optimization, e-commerce companies like Taobao basically put all their products into ES Search engines (which is impossible to put into MySQL and impossible to put into Redis). But even with the ES search engine, it’s possible to have deep paging problems, so what do you do? The answer is through the cursor scroll

🤞 author Xiao SAN is just graduated from the full stack engineer, wrote the technical article is basically in the process of learning notes sorted out, you can give little brother a little praise if you like after reading. 🎁 fan benefits: learning materials, resume templates are all click to receive