Entry

When do I need to use lock tables?

May 23rd, 2000 06:12
Vincent Vatter, Donny Simonton,

Here's the standard textbook example:

You have two salesman selling widgets, of which 50 are in stocj.  
Salesman #1 sells 5 widgets, so his database client goes and 
gets the number of widgets in stock (50).  But before salesman 
#1's client can update the inventory to 45, salesman #2 sells 1 
widget and his client goes in and gets the inventory (50).  Then 
slesman #1's client updates to inventory to 45, but salesman #2's 
client updates the inventory to 49, so in total 6 widgets were sold 
but only 1 was deducted from the inventory.

As you can see, it would have been better to lock the label, get the 
inventory, update the inventory, and then unlock the table.  Of 
course, another way would be of the form:

UPDATE inventory SET number = number - 5 WHERE inventory_id 
= widget_id;