CODEWITHSUNDEEP
postgresqltransactions
Sergey Alaev
Sergey Alaev

Reputation: 3972

Is update by select atomic for READ_COMMITED in postgres?

Is following query atomic within READ_COMMITED transaction?

update my_table
 set
   owner = ?,
 where id = (
   select id from my_table
   where owner is null
   limit 1
 ) returning *

I run tests on local postgres instance and it seems to be atomic, but is this always the case?

Upvotes: 1

Views: 383

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

Each SQL statement in READ COMMITTED isolation level takes a snapshot of the database, so it and all its subqueries see a consistent version of the database.

But you are not safe from “lost updates”: it is possible that a concurrent transaction modifies a row between the start of the statement and the time the row is updated, so it could be that the row that is actually updated does not have owner set to NULL any more.

If you need to avoid that, add a FOR UPDATE clause in the subquery.

Upvotes: 2

Related Questions