Postgresql – In postgres, is using a lock better than transaction when reading data

lockingpostgresql

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)

Best Answer

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.

Related Solutions

MySQL – How to Implement Optimistic Locking Correctly

Your developer is mistaken. You need either SELECT ... FOR UPDATE or row versioning, not both.

Try it and see. Open three MySQL sessions (A), (B) and (C) to the same database.

In (C) issue:

CREATE TABLE test(
    id integer PRIMARY KEY,
    data varchar(255) not null,
    version integer not null
);
INSERT INTO test(id,data,version) VALUES (1,'fred',0);
BEGIN;
LOCK TABLES test WRITE;

In both (A) and (B) issue an UPDATE that tests and sets the row version, changing the winner text in each so you can see which session is which:

-- In (A):

BEGIN;
UPDATE test SET data = 'winnerA',
            version = version + 1
WHERE id = 1 AND version = 0;

-- in (B):

BEGIN;
UPDATE test SET data = 'winnerB',
            version = version + 1
WHERE id = 1 AND version = 0;

Now in (C), UNLOCK TABLES; to release the lock.

(A) and (B) will race for the row lock. One of them will win and get the lock. The other will block on the lock. The winner who got the lock will proceed to change the row. Assuming (A) is the winner, you can now see the changed row (still uncommitted so not visible to other transactions) with a SELECT * FROM test WHERE id = 1.

Now COMMIT in the winner session, say (A).

(B) will get the lock and proceed with the update. However, the version no longer matches, so it will change no rows, as reported by the row count result. Only one UPDATE had any effect, and the client application can clearly see which UPDATE succeeded and which failed. No further locking is necessary.

See session logs at pastebin here. I used mysql --prompt="A> " etc to make it easy to tell the difference between sessions. I copied and pasted the output interleaved in time sequence, so it's not totally raw output and it's possible I could've made errors copying and pasting it. Test it yourself to see.

If you had not added a row version field, then you would need to SELECT ... FOR UPDATE to be able to reliably ensure ordering.

If you think about it, a SELECT ... FOR UPDATE is completely redundant if you're immediately doing an UPDATE without re-using data from the SELECT, or if you're using row versioning. The UPDATE will take a lock anyway. If someone else updates the row between your read and subsequent write, your version won't match anymore so your update will fail. That's how optimistic locking works.

The purpose of SELECT ... FOR UPDATE is:

To manage lock ordering to avoid deadlocks; and
To extend the span of a row lock for when you want to read data from a row, change it in the application, and write a new row that's based on the original one without having to use SERIALIZABLE isolation or row versioning.

You do not need to use both optimistic locking (row versioning) and SELECT ... FOR UPDATE. Use one or the other.

MySQL InnoDB MVCC vs Locking – Key Differences Explained

MVCC applies to isolation levels read-committed and repeatable read (default).

You don't need to specify anything for both of these features to work together. Maybe one way to think about it, is that row level locking is important so that you can update multiple rows at a time, and MVCC is so that the updates don't affect read operations at all.

Best Answer

Related Solutions

MySQL – How to Implement Optimistic Locking Correctly

MySQL InnoDB MVCC vs Locking – Key Differences Explained

Related Question