Database race conditions

Question

I've heard about many application developers having a bit of trouble in regards to race conditions in database processing. A typical example goes something like this:

• User 1 selects a field, say, numStock, which is 3
• User 2 also selects numStock, which is still 3
• User 1 decrements numStock (in the app), and sets it to 2 in the database.
• User 2 also decrements numStock (in the app), and sets it to 2 in the database.

In this example, the numStock field should have become 1, but it was set to 2 instead due to the race between users.

So of course locks can be used, but I've thought of another way of handling this - passing all row details as WHERE criteria. Let me explain...

In the example above, the SQL codes might look like this:

//select

SELECT itemID, numStock FROM items WHERE itemID = 45

//update

UPDATE items SET numStock = 2 WHERE itemID = 45

My idea for resolving the race:

//select

SELECT itemID, numStock FROM items WHERE itemID = 45

//update

UPDATE items SET numStock = 2 WHERE itemID = 45 AND numStock = 3

Thus, the query checks if the data has changed since it SELECT-ed the data. So my question is: (1) Would this [always] work? and (2) is this a better option compared to database locking mechanisms (eg, MySQL Transactions)?

Thanks for your time.

Answer 1

This strategy works and known as 'optimistic locking'. Thats because you do your processing assuming it will succeed and only at the end actually check if it did succeed.

Of course you need a way to retry the transaction. And if the chances of failure are very high it might become inefficient. But in most cases it works just fine.

Answer 2

What about making a select and an update in a single statement:

UPDATE counters SET value = (@cur_value := value) + 1 WHERE name_counter = 'XXX';

and then

SELECT @cur_value;

Does this strategy can solve a Race Condition?