I have several workers, with several connections.

Since the SQL statement is atomic, and using (to my understanding) a single call - does it protect DB from race conditions?

If I have 1000 calls to this user, for this update call which increase counter by 1. Can I be certain it will be increased by 1000 total?

Or is it not enough and I must use a lock or some other method ?

Increase messages_counter by 1: Notice the update is referring itself for increasing the value by 1

UPDATE "users" SET messages_counter = messages_counter 1 WHERE "users"."id" = 123;

CodePudding user response：

There is no race condition here, because an UPDATE will put a row lock on the modified row, so updates from different transactions are automatically serialized. Note that the lock will be held until the end of the transaction, so you want to perform this statement late in the transaction to get decent concurrency.