preface

  • As the title suggests, we show the prototype of this transaction method, and the code looks like this:

    @Transactional
public void updateGoodsList(List<Goods> goodsList) {
    for(Goods goods : goodsList) { goodsDao.updateById(goods); }}Copy the code

    The logic is very simple. The incoming Goods are iterated, and the corresponding row information in the Goods table is updated based on the ID, and there is also a transaction function. But it is such a simple API, was ali P7 interviewers directly pressed: will this method deadlock situation? (For those of you reading this, it is highly recommended that you think for yourself first.)

MySQL: MySQL: deadlocks

  • First of all, before we answer the question, we should consider the following: in MySQL’s default repeatable read transaction isolation level, when does a deadlock occur?

  • It needs to satisfy two conditions:

    First: Two transactions must exist simultaneously

    Second: Two transactions update two pieces of the same data at the same time, in reverse order.

  • To better understand these two conditions, let’s give an example. Let’s say that the goods are represented by stored information like this

    id goods_name goods_desc
    1 Braised beef noodles Delicious braised beef noodles
    2 Potato chips Eat one, and you’ll have wasted your workout for the day
    3 buckwheat Weight loss to choose it is right!

    And then, I now have two transactions, transaction A and transaction B. 2 UPDATE SQL executed in transaction A

    UPDATE goods SET goods_desc = 'Delicious braised beef noodles in brown sauce. Would you like a bucket? ' WHERE id = 1;
UPDATE goods SET goods_desc = 'Hold on, don't eat, or the day's exercise will be wasted. ' WHERE id = 2;
Copy the code

    Execute two UPDATE SQL entries in transaction B

    UPDATE goods SET goods_desc = 'If you don't eat, how can you lose weight? ' WHERE id = 2;
UPDATE goods SET goods_desc = 'Braised beef noodles in brown sauce, better with ham! ' WHERE id = 1;
Copy the code

    When they are executed, a deadlock will occur, as shown in the figure below:

    If you read the above picture carefully, you will see the two conditions for the deadlock situation described above:

    First: Two transactions must exist simultaneously. This condition is clear: in the example above it is transaction A and transaction B

    Second: Two transactions update two pieces of the same data at the same time, in reverse order. This condition is also clear: transaction A and transaction B both update the data with ID 1 and ID 2. And the update order is completely reversed, transaction A first updates the data with ID 1 and then update the data with ID 2. Transaction B updates the data at id 2 and then at ID 1.

Second, the essence of the regression problem, analyzing whether there is a deadlock problem API

  • Analysis idea: the two conditions of deadlock are closely linked.

  • Let’s see if the first condition is met: When do two transactions occur at the same time when the updateGoodsList method is executed? This is simple. Our projects are usually deployed simultaneously in the Web container, and each request container schedules a thread to handle the request. When we concurrently request the updateGoodsList method, there will be multiple transactions. So this condition is satisfied.

  • Let’s look at the second condition: What happens when two transactions update the same data at the same time, and then update it in the opposite order? (Think here for 3 minutes….) Three minutes later….. Simply, we iterate through the list to update the Goods table. The condition for updating is the id of the goods. What if our list stores an inverse structure? Thread A and thread B request an updateGoodsList method with an argument structure that looks like this:

    So, is it exactly the same as our analysis above? Therefore, this API is deadlocked.

Third, summary

  • Some interviewers may not be looking at your skills directly, but instead will look at them in real situations to test the candidate’s ability to actually solve problems.
  • Feel free to like, bookmark and follow my posts if you find them useful. :laughing:
  • I’m a slow walker, but I never walk backwards