Database update deadlock, database update

Database update deadlock, database update

A common deadlock scenario is a scenario where concurrent batch update is performed:

Update cross_marketingset gmtModified = NOW (), pageview = pageview + # extpageview # WHERE marketingId = # marketingId #

 

For the first call, when marketingId is used for the second call, the order of incoming values of marketingId is: 1, 2, 5, and 3. the row is locked for each update. Then, when the first call is performed, the sequential lock row is found to have been locked by the second call after Update 3 and preparation for the new 5. At this time, the second call is just like getting the lock 3 after the new 5 preparation, but it is found to be occupied by the first call, so a deadlock occurs. Therefore, we need to sort batch update and delete operations in a fixed order and then perform operations. In the previous example, if the marketingId is sorted from small to large, it will become: for the first call, the order of marketingId input values: 1, 3, 5, 12, the order of marketingId input values: 1, 2, 3, 5 to avoid deadlock.