Database Race Conditions

I have heard that many application developers are a little worried about race conditions in database processing. A typical example looks like this:

  • User 1 selects a field like numStock which is 3
  • User 2 also selects numStock, which is still 3
  • User 1 decreases numStock (in the app) and sets it to 2 in the database.
  • User 2 also shrinks numStock (in the app) and installs it into database 2.

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

So of course you can use locks, but I thought of another way to handle this - passing all the row data as WHERE criteria. Let me explain ...

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

//choose

SELECT itemID, numStock FROM items WHERE itemID = 45

      

// update

UPDATE items SET numStock = 2 WHERE itemID = 45

      

My idea for race resolution:

//choose

SELECT itemID, numStock FROM items WHERE itemID = 45

      

// update

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

      

So the query checks if the data has changed since it fetched the data. So my question is: (1) Will this [always] work? and (2) is this a better option over database locking mechanisms (e.g. MySQL transactions)?

Thank you for your time.

+3


source to share


1 answer


This strategy works and is called "optimistic locking". This is because you do your processing in the belief that it will succeed, and only at the end do you actually check if it succeeded.



Of course you need a way to retry the transaction. And if the chances of failure are very high, it can become ineffective. But most of the time it works fine.

+5


source







All Articles