In postgres, is using a lock better than transaction when reading data? 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 &lt; entries.length) {
    //stop handling, return error that updating cannot happen
}
//DO SOMEFURTHER CHECKS
rawQuery('UPDATE public."entry" SET "event"=' + newEventId + ' WHERE id=' + entry.id)
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.