does sql statement ensure atomicity in postgres

user_9007093 0
Viewed 249

I have a simple bug in my program that uses multi user support. I'm using knex to build sql queries, and I have a pseudocode that depicts the scenerio:

const value = queryBuilder().readDataFromTheDatabase();//executes this
//do some other work and get value
queryBuilder.writeValueToTheDatabase(updateValue(value));

This piece of code is being use in sort of a middleware function. And as you can see, this is a possible race condition i.e. when multiple users access the thing, one of them gets a stale value when they try to execute this at roughly the same amount of time.

My solution

So, I was think a possible solution would be create a single queryBuilder statement:

queryBuilder().readAndUpdateValueInTheDatabase();

So, I'll probably have to use a little bit of plpgsql. I was wondering if this solution will be sufficient. Will the statement be executed atomically? i.e. When one request reads and doesn't finish his write, does another request wait around to both read and write or just waits to write but, reads the stale value?

knex.js acid plpgsql postgresql
3 Answers

I think what you are looking for here is isolation, not atomicity. You could set all transactions to the highest isolation level, serializable (which is higher than the usual default level). With that level, if data that a transaction read (and presumably relied upon) is changed, then when it tries to commit it might get a serialization failure error. I say "might", because the system could conclude the situation would be consistent with the data change having happened after the commit, in which case the commit is allowed to stand.

user_17212390

To avoid a race condition with such a setup, you must run both the read and the write in the same database transaction.

There are two ways to do that:

  1. Use the default READ COMMITTED isolation level and lock the rows when you read them:

    SELECT ... FROM ... FOR NO KEY UPDATE;

    That locks the rows against concurrent modifications, and the lock is held until the end of the transaction.

  2. Use the REPEATABLE READ isolation level and don't lock anything. Then your UPDATE will receive a serialization error (SQLSTATE 40001) if somebody modified the row concurrently. In that case, you roll the transaction back and try again in a new REPEATABLE READ transaction.

The first solution is usually better if you expect conflicts frequently, while the second is better if conflicts are rare.

Note that you should keep the database transaction as short as possible in both cases to keep the risk of conflicts low.

user_64643080

Transaction in PostgreSQL use an optimistic locking model when accessing to tables, while some other DBMS do pessimistic locking (IBM Db2) or the two locking model (MS SQL Server).

Optimistic locking snapshot the data on which you are working, and the modifications are done on the snapshot until the transaction ended. When the transaction finishes, the snapshot modifications are postponed on the real database (table rows), but if some other user had made a change between the moment of the snapshot capture and the commit, then the commit cannot apply and the COMMIT is rejected as a ROLLBACK.

You can try to raise the ISOLATION LEVEL (REPEATABLE READ or SERIALIZABLE) to avoid the trouble.

user_126598720
Related