Database story: Pessimistic lock and optimistic lock

Wong Choy and Xiao Qiang live in an online mall system, is a pair of son line Cheng Haoqi friends.

Monday just to work, Wong Choy received the leader phone said, to put a commodity inventory reduction of 20, Wong Choy dare not snub, quickly put inventory out to a look, oh, now there are 1000.

At the same time, Xiao Qiang also received a phone call to reduce the inventory of the same goods by 30, he saw, oh, now there are 1000.

Wong Choy calculates the latest inventory value of 980, save!

Xiao Qiang also calculates the latest inventory value of 970, save!

Wong Choy's data is covered by the cockroach!

Leader A look, originally sold 50 goods, now stock only buckle 30, so continue on chaos.

Wong Choy and Xiao Qiang, each dozen 20 Big board, long long memory!

"Xiao Qiang said:" Brother, or we still think of a way, and then go on to be beaten to death. ”

Wong Choy said: "In this way, each time we visit the inventory before, we have to lock, add a lock, prohibit others to enter the access, can only wait for the person holding the lock to release." ”

Tuesday, the leader let Wong Choy again to reduce inventory 20, Wong Choy This time extremely careful, first put inventory to lock, and then slowly modify.

Xiao Qiang also received the order to reduce inventory, but Wong Choy has locked the inventory, can not operate, Xiao Qiang had to go to block the Workshop tea chat, and then to the ready to wait for the workshop scheduling operation.

Finally wait to be able to execute again, Xiao Qiang see, this inventory how still locked!? Had to go again to block the workshop tea.

Leadership a look, Xiao Qiang you what is the matter, always tea chat?! Do you still work?

Xiao Qiang argues that Wangongo has been locked in stock and I can't operate it.

Leadership regardless of this, the cockroach and Wong Choy 20 dozen.

(Note: This way of locking is pessimistic lock , pessimistic lock as its name, every time to fetch and write data when the data will be modified by others, so the data lock, put locked state, not let others access. The downside is that if the lock is held too long, other users will have to wait a long time. ）

Wong Choy said: "Brother, this time brother to not live you ah, processing slowly some, but elder brother just beaten when thought a good way: optimistic lock ." ”

Xiao Qiang said: "Come on, you, your butt is going to be beaten up and optimistic?" ”

"You listen to me, we are next to that inventory field, plus a version of the field, such as the beginning of the time (stock = 1000, version =1), each time you go to read not only to read the inventory, but also to read the version number, until you modify the inventory, Be sure to check the version number when you write back and see if it is the same as when you read it. ”

"What if it's not the same?" "Xiao Qiang asked

"Then discard this write operation, re-read the inventory and version number, re-come." ”

"What if it is the same?" ”

"Then be assured to write back the new inventory value." Add the version number to 1 "

"I seem to understand a little, we try, but you have to think well, I do not want to hit the board again." ”

In Wednesday, Wong Choy was ordered to subtract 30 from his inventory, and he read it first (stock = 1000, version =1); The cockroach also has to change inventory, he will be the inventory minus 50, so he also read (inventory = 1000, version =1).

Wong Choy calculates a new inventory value of 970, the write-back succeeds, and now the version becomes (stock = 970, version =2).

Xiao Qiang also calculates a new inventory of 950, also ready to write back, he nervously to see the latest version number, has become version 2, according to the previous agreement, had to re-read.

The cockroach reads again (stock = 970, version =2), calculates the latest cache value 920 (970 minus 50), tries to update again, did not expect to be robbed by others first, now the version number has become 3, the latest data is (inventory = 900, Version = 3).

Alas, had to re-come, calculate the latest cache value 850 (900 minus 50), the third time finally updated successfully, the latest inventory is (inventory = 850, Version = 4)

Leaders see Wong Choy and Xiao Qiang busy, a moment, and inventory value also did not mess off, satisfied place nodded: good comrade Ah.

(Note: This is the so-called optimistic lock , wong Choy and Xiao Qiang this optimism a little, I feel that in general there will not be too many people to modify inventory, so there is no lock, rest assured to operate, only in the last update to see if the conflict. This approach is suitable for scenarios where there is not much conflict, and if there is a lot of conflict, the data contention is intense, which leads to constant attempts, but degrades performance. ）

The two kinds of locks have advantages and disadvantages, not to think of one better than the other, like the optimistic lock for less write, that is, the conflict really rarely occurs, this can save the lock overhead, increase the overall system throughput. However, if there is frequent conflict, the upper application will continue to retry, which is to reduce the performance, so in this case, pessimistic locking is more appropriate.

Database story: Pessimistic lock and optimistic lock