CODEWITHSUNDEEP
sqlpostgresql
Nepo Znat
Nepo Znat

Reputation: 3270

Is an update operation with an IS NULL check safe against race conditions?

I have a nullable field in my database called accessed_at which should be only set once. It should not be possible to update this field after it has been filled out.

To achieve this behaviour, I've added a simple IS NULL check in my update operation to confirm this field is empty (if it's not empty, the update operation will not update this field).

UPDATE
   "payment" 
SET
   "accessed_at" = '2021-01-16T09:39:03.487833+00:00'::timestamptz 
WHERE
   (
      "payment"."accessed_at" IS NULL 
      AND "payment"."id" = 1
   )

What happens if two clients execute this update operation at the same time. Does the database perform these operations one after the other or in parallel so it could happen that Client1 updates the field and Client2 also updates the field (they overwrite each other)?

Upvotes: 0

Views: 80

Answers (2)

Frendy Lio Can
Frendy Lio Can

Reputation: 21

When Client1 Updates the Field, it will lock the selected row from your table "payment". Thus, even if the two clients execute the update at the same time, you will have your expected output as Client 2 is waiting for Client 1 to finish updating.

Upvotes: 0

user330315
user330315

Reputation:

The IS NULL condition is a good way to prevent the second update to overwrite the first.

One of the two transactions will be the first (even if it's just nanoseconds earlier) and that will lock the row (and only that row) for further modifications. So the second transaction will wait until the lock is released.

Once the first transaction is committed, the lock is released and the second transaction can proceed. However it will re-evaluate the WHERE condition again, not finding any rows and will not update anything, i.e. it will not overwrite what the first transaction put into the accessed_at column.

The above is true if using the default isolation level read committed.

If the second transaction was using serializable it would receive an error as soon as the first transaction commits.

Upvotes: 3

Related Questions