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

This article will explain in detail why InnoDB update should try to use the index as far as possible, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Start a discussion from a problem

Question: innodb row-level locking is achieved by locking index records. If the where column of the update statement is not indexed, will update lock the entire table even if there is only one record?

The answer is yes. I found a table that created a self-increasing primary key from the current local database to demonstrate. Open two terminal windows to verify whether the b side will be locked when the a side executes the update statement. First, the structure of the table is shown in the following figure.

In the test table stu_score, id is the primary key index, and the other columns are not indexed. Now test whether the entire table is locked or only rows that meet the criteria are locked if the where does not use the index when executing the update statement. Be careful not to forget to start a transaction.

As can be seen from the screenshot above, when the update statement of where name= "wujiuye" is executed on the A side, although there is only one record satisfying the name= "wujiuye", the innodb locks the whole table, and the update statement that updates other rows on the B side is blocked before the transaction is committed.

Let's take a look at an example of update that uses an index.

As shown in the screenshot above, on the a side, because the update statement only modifies this record of id=1008, and id is the primary key, so innodb only locks this line of records of id=1008, so on the b side, only changes are made to the rows of id=1007, so only the records of id=1007 are locked.

You can also verify whether a row lock is added and execute update statements on the same row record between different transactions on the an and b sides.

Obviously, side an executes the update where id=1008 first, so it first acquires the lock of the row of id=1008 records, and then when the update where id=1008 is executed on the side b, because the lock is already occupied by other transactions, it can only enter the waiting state. When the a side commits the transaction, the b side will acquire the lock to continue execution, or the b side will wait for the timeout (the transaction default lock timeout is 50s) and the transaction will fail and the transaction will be rolled back.

If you execute the update statement of where name= "wujiuye" on the b side, it will enter the block because it needs to obtain a table lock and will lock the entire table. You can see the experimental results below.

Using row locks can lead to deadlocks. Let's take a look at an example.

The row lock of id=1008 is obtained by doing update operation on the row record of id=1008 on the a side. (the lock is only added when needed, but the lock is released only when the transaction commits. This is the concept of two-phase lock. ) then update the row records of id=1007 to obtain the row lock of id=1007. At this time, there is no problem that a transaction holds the row lock of id=1008, and b transaction holds the row lock of id=1007. However, when the a-side continues to perform write operations on the locked records of the b-side, it will be blocked, and if the b-side also wants to perform a write operation on the locked records of the a-side, then a deadlock state will occur.

However, after the verification of the above experiments, innodb has helped us eliminate deadlocks by default, so when I executed the last sentence of update on the b side, I immediately reported an error, suggesting that deadlock found when trying to get lock; found a deadlock when it tried to acquire the lock.

Summary

Two-phase locking protocol: in InnoDB transactions, row locks are added when needed, but not immediately when they are not needed, but not until the end of the transaction.

How does it help us to know this? In the case of concurrency, is it better for records of that kind of multi-user shared operations to be locked as little as possible? For example, when purchasing an item, can multiple users purchase the same item at the same time, so that the same row record of the same item table is accessed in multiple transactions, and the purchase payment is only modified to the corresponding record of the current user in each transaction, so how to optimize it? Remember, the lock is added only when it is needed, so the line lock on the commodity record is locked when the inventory is reduced. in order to reduce the locking time, is it possible to first perform the user's deduction operation and add the order record operation? finally, perform the inventory reduction operation, and then commit the transaction, and then perform the inventory reduction operation when you are ready to commit the transaction, so as to reduce the locking time and submit the concurrency efficiency.

If you don't understand the above example, I found an example on the Internet. Suppose you are in charge of implementing an online movie ticket trading business, and customer A wants to buy movie tickets at cinema B. To simplify, this business needs to involve the following operations:

(1) deduct the movie ticket price from the balance of customer An account

(2) increase the ticket price of this movie to the account balance of Cinema B

(3) record a transaction log.

In other words, to complete the transaction, we need to update two records and insert one record. Of course, in order to ensure the atomicity of the transaction, we need to put these three operations in one transaction.

So, how would you arrange the order of these three statements in the transaction? Imagine that if another customer C wants to buy a ticket at cinema B at the same time, then the conflict between the two transactions is sentence 2. Because they want to update the balance of the same cinema account, they need to modify the same line of data.

According to the two-phase locking protocol, no matter how you arrange the statement order, all the row locks required by the operation are released when the transaction is committed. So, if you put statement 2 at the end, for example, in the order of 3, 1, 2, then the cinema account balance has the least lock time. This minimizes lock waiting between transactions and improves concurrency.

About why InnoDB update should try to use the index to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.