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.

For your situation, you can use increment instead of setting it to a specific value. That way if two separate processes both want to increase the numStock they will not race each other. `SET numStock = numStock + 1` — raw-bin hood, Nov 29 '21 at 00:31
In your scenario your must setup some retry mechanism to retry if nothing is updated. In such case what if itemID is passed incorrectly then retry mechanism will keep on retrying unless there is some maximum number of retries specified. Use of transactions is definitely a better idea. — skondgekar, Aug 23 '22 at 04:54

score 13 · Accepted Answer · edited Sep 08 '15 at 12:41

13

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.

edited Sep 08 '15 at 12:41

idmean

14,540
9
54
83

answered Mar 24 '12 at 08:56

Jens Schauder

77,657
34
181
348

2

Can DB race conditions occure when there's single DB connection? – arrowd Mar 02 '15 at 06:39
2

Some databases include the option to run asynchronous processes, with those features you can even with a single connection get race conditions. Of course depending on your perspective those process might count as separate connections. ... – Jens Schauder Mar 02 '15 at 06:42

Carlos · Answer 2 · 2018-08-27T15:55:35.483

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?

edited Aug 27 '18 at 15:55

answered Aug 27 '18 at 15:18

Carlos

352
1
3
13

score 2 · Answer 3 · answered Oct 10 '21 at 17:24

Every interaction with databases is a transaction - either implicit when you just write a single statement or explicit when you use BEGIN / COMMIT / ROLLBACK.

Then you use a transaction isolation level which defines which phenomena can occur. Typical phenomena are called dirty read, non-repeatable read, phantom read. Typical isolation levels are READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE.

Let's look at your specific example (time flows down):

T1                T2
-----------------------------
x := r[numStock]
                  y := r[numStock]
w[numStock] = x-1
                  w[numStock] := y-1

Hence the write of T2 was on stale data. This is a lost update. Some databases, such as Postgres, prevent lost updates. When the transaction is tried to be committed in the REPEATABLE_READ isolation level or higher, an exception is thrown:

ERROR: could not serialize access due to concurrent update

However, I've heard that the InnoDB engine in MySQL does not have a check for lost updates (source).

The mentioned transaction isolation levels specify which problems you want to prevent. They don't make any statement about how it is achieved. There is optimistic concurrency control (snapshot isolation) and pessimistic concurrency control (locks).

I'll soon also publish an article about those topics :-)

Database race conditions

3 Answers3

Linked