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

This article is mainly about the specific relationship between mysql lock and index. If you are interested, let's take a look at this article. I believe it is of some reference value to you after reading the specific relationship between mysql lock and index.

At ordinary times, when we use mysql locks, we seldom consider the efficiency of locks when we first come into contact with the database, and we generally only want to achieve the purpose of preventing concurrency, but with the increase of the amount of data, we will find that there are a lot of sql we have written very optimized, but sometimes it is still very slow, it is difficult to find the reason, at this time we should consider whether the lock of mysql is caused.

Let's first create a new data table:

Here our primary key is indexed by default; add some data here

Then we start two processes to test:

First add a where condition that does not involve a lock on the index:

Then we update the data of this row in the second window, and we will find that the operation will be stuck.

Then we commit the transaction and find that the data in the second window will be executed immediately.

There doesn't seem to be any problem from the above, which does achieve what we want, but you can add the same lock and try to update other data such as the one I executed below:

I tested the above three cases with the same process, and they will all get stuck, so the problem arises. When we lock the name=' test name', we may just want to lock the id=133 and id=134 lines. We do not want to lock 135Lines, but we cannot access them either, because our lock is a table lock, so let's try another case where indexes are used when using locks:

The index is used when the lock is used above, but we still get stuck when updating the data. Here, we will think that the index is useless, but if you encounter the same lock, you can also use the index when updating the data. You can see the effect:

We will find that this is not stuck in the lock, just update it.

Let's sum up: if our lock uses an index, it's a row lock, and if we don't use an index, it's a table lock, but the data we operate on must use a lock.

Let's talk about why this is so:

First of all, we know that if there is no index, we select or locate the data in the form of a full table scan, so that a table lock will be formed, and if there is an index, it will be located directly to the specified row, that is, a row lock will be formed, but it should be noted that if you update the data, if you do not use the index, the whole table will be scanned, and when you scan to the locked row, it will also be locked. So it can't achieve the desired effect.

Are there any specific links between mysql locks and indexes mentioned above that are helpful to you? If you want to know more about it, you can continue to follow our industry information section.

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.