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
Last activity: Nov 1, 2024, 04:58 PM