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

MySQL how to write update to avoid table locks, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Today, I will talk about another interview question. Why is it recommended that the where condition in MySQL's update statement have a primary key?

Friends who see this problem, I believe many people have doubts, my where can not be updated without the primary key?

No, it can be updated and used. But I don't recommend it. Because most of us use MySQL using the innodb storage engine, it supports transactions. If your where condition does not add a primary key, then the row-level lock of innodb may become a table-level lock. If you upgrade to a table-level lock, concurrency will be greatly reduced.

Upgrading a row lock to a table lock is related to the isolation level of the transaction, because the isolation of the transaction is achieved by locking, and improper locking is bound to affect concurrency.

Different locks support different concurrency. What kind of lock is added in the end also has a lot to do with the index, so it can be said that what kind of index determines how much concurrency is supported.

There are three types of commonly used indexes: primary key, unique index, and general index. I will not elaborate on the primary key, with the most efficient index attribute; the only index means that the repetition rate of the attribute value is 0, which can generally be used as the business primary key, such as the order number; the ordinary index differs from the former in that the repetition rate of the attribute value is greater than 0, which cannot be used as the only specified condition, such as the name of the purchasing user. What I want to talk about today is "the impact of general indexes on concurrency".

Without an index, I won't talk about it. It must be a disaster for concurrency. Deadlocks are probably common.

Why do I recommend adding a primary key to the where condition in update?

Because the primary key is a unique index, you can use other unique indexes, but in a general table, only the primary key is unique. So, I suggest that when you update, just remember to add the primary key.

You just need to remember that the primary key and unique index are row locks, and other indexes are not necessarily row locks, but probably table locks. In this way, the probability of deadlock is very high, and concurrency decreases accordingly.

Let's take a look at the general index through a simple example.

The related table creation statements, indexes, and data are as follows:

Then cancel the transaction auto commit set autocommit = off

When the repetition rate of creation time in our table is relatively high. Open two windows and two transactions respectively.

To demonstrate, you can add more data, such as 100000 items for 03-01 and 03-02.

Execute the SQL in the two windows in turn, and you will find that the update in one of the windows failed. Tip:

It seems that the two transactions do not interfere with each other, but the failure to update the data you locked in one of the transactions should indicate that a table lock was thrown at this time. This is in the case of non-primary key index or unique index, and the number of index data repetition is relatively high, your update occurs gauge lock. The concurrency ability will be greatly reduced!

You can try to see if this will happen if you use the primary key or unique index at this time.

In our e-commerce system, there is a lot of such code. Some inexplicable anomalies often occur in some popular products and activities such as second kill, discount, discount and so on, resulting in a great discount to the user experience.

In the above test data, you delete all of them, and then add some data, in which if the create_time repetition rate is 0, you will find that both transactions can be successful. This shows that they should use row-level locks at this time, which is more efficient.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.