In the previous article, we introduced global and table locks in MySQL.

Today, we are going to focus on downlocking, a problem that often puzzles us in daily development and interviews.


1. Base of row locking

Since both global and table locks have a significant impact on add, delete, alter, and query performance, it’s natural to think,

All you need to do is lock the modified rows, which is the row lock.

In a transaction, transaction 1 updates a row with primary key 1, so transaction 2 cannot operate until the transaction releases the lock.

In addition, there is a lot of people easy to confuse the concept, is the row lock when release?

To understand this, you need to understand what two-stage locking is.

What is two-phase locking? Let me give you an example.

So what happens when transaction 2 is executed?

If you understand the meaning of two-phase locking, you know that transaction 2’s UPDAT statement blocks until transaction 1 commits.

So, here again, the meaning of two-phase locking.

In InnoDB transactions, row locks are added as needed, but are not released immediately after the statement is finished, but at the end of the transaction.

Note that there is another way to add a write lock to a SELECT statement, in addition to the update statement

Select… . for update

2. Advanced row locking

2.1 What is illusory

During interviews, interviewers often like to ask about the transaction isolation level of the database.

I want you to be able to answer four levels of isolation and what they mean.

Ask a little more, and you’ll be asked what is dirty reading, what is phantom reading, and which isolation level will solve what problem.

First of all, what is illusory?

The same transaction is queried twice in the transaction, with different results.

The difference is that dirty reads are for updates, where data on the same row is inconsistent.

Note that the illusion appears in the scene

First: The isolation level of the transaction is repeatable reads and is the current read

Second: phantom reads refer only to newly inserted rows. In a range query, new rows appear in a later query.

2.2 How to solve illusionary reading

If you can answer all of these questions, the interviewer may continue to ask how hallucinations occur and how they are solved.

Even if we had row locks for all rows covered by the update, we would still not have resolved the newly inserted records because they did not exist and therefore cannot be locked.

So what to do? To solve this problem, InnoDB had to introduce a new Lock, a Gap Lock.

A gap lock locks the space between two values.

Here’s an example:

In four records, ID= 0,10,20,30, the following five gap ranges are generated

A gap lock locks these five gap ranges to prevent new records from being inserted.

Note that row lock conflicts are between rows, between row locks. The conflict with gap locking is the operation of “insert data into gap”. Gap locking itself does not cause conflict.

Gap locking and row locking are called next-key locks.

Each next-key lock opens front and closes back. The gap lock itself opens front and back.

Small tips

In standard transaction isolation levels, repeatable reads only address dirty reads, not phantom reads. But in InnoDB, next-key lock is used to solve the illusion problem.

3. Optimized application of row locking

3.1 Optimized application of two-stage lock

In the basics above, we explained what two-phase locking is.

So, what is the reference for our business development?

Now that we know that a row lock cannot be released until the entire transaction is committed, if we need to lock more than one row in a transaction, we need to put the statement that is most likely to cause lock conflicts, or lock the most rows, as far back as possible.

For example, little A purchases A product of merchant B online, and this purchase action can be simplified into three operations:

1) X was deducted from the balance of Little A’s bank account;

2) Merchant B’s bank account balance increases by X;

3) Add a transaction record;

Here, two update operations and an INSERT operation are involved. To ensure atomicity, three actions are placed in one transaction.

So how do you prioritize these three statements? If you don’t think about it carefully, you might just pick 123 or 213 at random.

Think about it?

Obviously, the most likely conflict here is step 2), where multiple users may purchase merchant B’s products at the same time and then need to update the balance of merchant B.

In addition, step 3) is the INSERT operation, which is least prone to lock conflicts.

Therefore, the best order of steps is 3) ->1) -> 2), with the most likely conflicting operations executed last, which greatly improves concurrency compared to 2) ->1) ->3).

3.2 Gap lock problems and optimization

The introduction of gap locking also introduces some new problems, such as reduced concurrency, which can lead to deadlocks.

Because of the introduction of gap locking, it is possible for the same statement to lock a larger range.

So what to do?

Note that gap locking is only effective at the repeatable read level.

So, as long as our business does not require repeatable read guarantees, we can set the isolation level to read commit (which is also the default isolation level of Ali Cloud RDS database), and there will be no gap locks.

Then, to resolve possible data and log inconsistencies, you need to set the binlog format to ROW.

Read commit level + binlog’s ROW format is also standard for corporate databases.

Now, you know why 🙂


Reference:

MySQL tutorial 45


[MySQL]

MySQL > alter table count(*);

2. Why does the total storage size of MySQL become larger?

MySQL > select * from ‘MySQL’;


See the end, the original is not easy, point a concern, point a like it ~

Scan my official account “Ahmaru Notes” to get the latest updates as soon as possible. At the same time free access to a large number of Java technology stack e-books, each large factory interview questions oh.

                                                    Â