How to get a little more concurrency / performance out of a database

The database should always be the bottleneck in any large scale web application. As such, it is becoming increasingly important to minimize concurrency and to eek out every last bit of performance from the database.

There are a set of techniques for improving concurrency in sql code. The one I have personally seen most widely applicable is best seen with an inventory example. Suppose that you are buying 10 copies of Jim’s book from Amazon. The logic starts out with a predicate i.e. does the store have 10 copies of Jim’s book in stock. If it does then reduce the quantity by 10. Pseudo code would be as follows.


Select quantity_on_hand from Inventory where ISBN = 'IUWHSUY' FOR UPDATE;
If quantity_on_hand >= 10
Update Inventory set quantity_on_hand = quantity_on_hand - 10 where ISBN = 'IUWHSUY';


While this is pretty efficient it can be improved upon by combining the predicate i.e. ‘if quantity_on_hand >= 10’ with the transform i.e. ‘set quantity_on_hand = quantity_on_hand – 10’ into a single SQL statement.

The resulting single sql statement is as follows


Update Inventory set quantity_on_hand = quantity_on_hand - 10 where quantity_on_hand >= 10


The application then checks the return of the update call to see how many rows were effected. If it was 1 then all is well (there was enough stock and the inventory level has been reduced), if it is zero then there aren’t enough items in stock.

This pattern comes up over and over again and the single sql statement is ALWAYS preferable to sending off two separate sql requests i.e. one that checks whether some condition is satisfied and then another to perform the update.

Update:Removed reference to ‘field calls’ which was pointed out to me actually refers to pushing this support further into the db manager, the above pattern still holds though, just not called a field call