0

I have a race condition in the code for my database, and I'm wondering how I can prevent this using either locks or transactions? (And which is best)

I'm using an ORM to update values, however the ORM doesn't support either locks or transactions (though it does support native queries so I'll have to do some of that).

But the code can be summarised to the following:

I wish to update (of an object A) the reference to table X, from id 1 to 2. - The orm keeps these as simplistic numerical ids, no foreign keys.

The maximum number of references to an id in table X is based on the corresponding "spots" column in that table.

So before updating first the amount of free spots is checked, then some further checks are done (calculatedPrimeNumber(1000)). And finally the field is updated.

Or in real terms: I have a tables entries which have a reference to events.

Whenever I wish to update the event of an entry, I check how many free spots there are in the event. (based on events.spots and the amount of entries that have the reference to said entry).

I do some further checks. And then update it.

Now the problem starts when there are multiple requests to update the event data at once: suddenly it can happen that two requests succeed, even though there is only a single spot in the event.

What is the best way to prevent this? Using transactions or how should i lock the database?

So far I can only find the ability to have locks within a single transaction/command. However this would not allow for server level checks as in:

entries = rawQuery('SELECT * FROM public."entry" WHERE id=' + newEventId)
events = rawQuery('SELECT * FROM public."event" WHERE id=' + newEventId)
if (events[0].spots < entries.length) {
    //stop handling, return error that updating cannot happen
}
//DO SOMEFURTHER CHECKS
rawQuery('UPDATE public."entry" SET "event"=' + newEventId + ' WHERE id=' + entry.id)
Improve this question
3
  • 1
    I think you should read about concurrency control in Postgres. Somehow you're mentioning "locking" and "transactions" as if they were mutually exclusive, while in fact they are related but orthogonal concepts. Besides, whether you (or your ORM) want it or not, you are already using both transactions and locks, because that's how Postgres (and most SQL databases) operates -- every statement executes inside a transaction, and locks are taken automatically as appropriate for the isolation level in effect.
    – mustaccio
    Jun 12, 2019 at 14:32
  • @mustaccio while reading that it seems the orm I use, difference a lot in how transactions are defined. - Because from those pages I keep seeing that transactions have to exist within a single sql query, and cannot span multiple queries. (BEGIN and COMMIT on different calls to the database, potentially seconds/minutes apart).
    – paul23
    Jun 12, 2019 at 14:54
  • You're seeing wrong. A transaction will span as many statements as necessary, up to and including an infinite number of them (given sufficient hardware resources of course). BEGIN and COMMIT determine transaction boundaries explicitly; note though that transactions can also be implicit.
    – mustaccio
    Jun 12, 2019 at 14:56

1 Answer 1

Reset to default
0

I think that you can prevent this problem by putting FOR UPDATE at the end of your SELECT statements. I am not sure because your description is somewhat confusing.

To avoid deadlocks, add an ORDER BY id to the queries as well.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.