Sample Header Ad - 728x90

Make sure that only one transaction updates a row

3 votes
1 answer
174 views
I have users table
CREATE TABLE tasks(
    id UUID PRIMARY KEY,
    status TEXT REFERENCES statuses, -- available statuses are 'NEW', 'PENDING', 'PROCESSING', 'COMPLETE'
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NULL
);
I want to make sure that only single process will change status from PENDING to PROCESSING in case of multiple concurrent updates attempts, the rest should observe zero updated rows. Task status change is driven by processing external HTTP request, it is important to only single request result in success, the rest should detect change and return 409 Conflict, we don't have control over how many concurrent request we can get. Update statement is first thing that is done as part of bigger transaction, after update in case process observe that it updated tasks row (updated row count is 1), it later insert/update some data in other tables etc. I've tested following update query
UPDATE tasks 
  SET status = 'PROCESSING'
  WHERE id = '00000000-0000-0000-0000-000000000000' AND status = 'PENDING'
using Java JDBC, multiple threads, connections etc. and I observe that only single process make update when using default isolation level READ COMMITTED. When I was thinking about this problem I thought I need either FOR UPDATE in UPDATE statement or SERIALIZABLE isolation level on transaction, but in actual tests I wasn't able to catch inconsistency in case of multiple updates. My question: is it enough to filter by id and status to make sure only single process will perform update, or I need additional actions to exclude unexpected behaviour in general?
Asked by sebarys (33 rep)
Oct 31, 2024, 03:05 PM
Last activity: Nov 1, 2024, 04:58 PM