TQ
dev.com
Blog about software development
29 May 2017 - by 'Maurits van der Schee'
One of the most misunderstood topics in software development is most likely concurrency. There are three kinds of operations on data rows: reads, blind (full) writes and dependent (or partial) writes. In a high concurrency web application you typically do not need to lock for the first kind. Only need to lock the write operation itself in the second case and you need to lock the row for the entire duration between read and write in the third case.
The "price list" example is a (database table representing a) diner's menu with items with a name and a price. There are two ladies running the diner, named Alice and Carol. Alice takes care of the serving of the customers and Carol is doing the cooking in the kitchen. Whenever an item is not selling Alice lowers the price or Carol changes the way it is cooked slightly and reflects that in the name. The price lists are printed regularly, but nevertheless in the case of a change it takes a while before the customers see that on the menu.
Alice and Carol are using a computer with database software that uses digital forms. If you want to adjust an item on the price list you request a item change form for a specific item. The form is filled with the actual data. You can then change the data and click on "Save". Remember that there are two terminals: one in the kitchen and one on the counter.
1) Partial updates, without locks
You can always request an item change form, and when you change a field and submit, then only the changed fields are written to the database.
Problem: Alice lowers the price and Carol changes the name and the result is an item with a lower price and another name.
2) Full updates only, without locks
You can always request an item change form, and when you change a field and submit, then all the item's fields are written to the database.
Problem: Alice lowers the price and Carol changes the name and the last one press submit "wins", without necessarily having seen the other persons adjustment.
3) Optimistic locking, second update may fail when saving
You can always request an item change form, and when you change a field and submit, then all the item's original fields must match the fields in the database before the update or the update will fail.
Problem: Alice lowers the price and Carol changes the name and the last one press submit "fails" and all input was lost.
4) Pessimistic locking, second update cannot start
You can only request an item change form when nobody else has one open, and when you change a field and submit, that submit must happen within 5 minutes after requesting the item change form or the update will fail.
Problem: When Alice lowers the price Carol cannot change the name, but when Alice does not make her change fast enough her input is "lost".
5) Exclusive locking, menus cannot be printed
You can only request an item change form when nobody else has one open, and as long as it is open you cannot print menus, you need to submit the form within 5 minutes or the update will fail.
Problem: When Alice and Carol change the menu often you can almost never print the menus and customers have to wait.
You never want to hold locks longer or for a wider scope than necessary. In the above case we cannot change the lock time, but if we are for instance keeping a count of the number of hamburgers in stock, we may not do this through the "item change form", but through a "stock decrement form". This way we may not have to lock the item during entry of the data and Alice and Carol can enter their hamburger orders concurrently.
Most business software nowadays use the "full update, no locks" method, but sometimes this is not such a good fit. When it is not you should probably consider "optimistic locking" with either a version column or the full row state. If that does not ensure enough consistency it is probably a good idea to look at "pessimistic locking", but only if your do not have many updates. For high traffic I recommend to look at entering (blind) "change events" instead of going the locking route as locks can be a real threat for availability.
PS: Liked this article? Please share it on Facebook, Twitter or LinkedIn.
Subscribe