How to rollback transaction if row changed?

Question

I am using Postgres 9.3.

When I update row in a table my flow looks like this:

1. Fetch old row (ex. SELECT * FROM tbl WHERE id = 1)
2. Validate new data in scope of old data (old row fetched in point 1) - this is done by my app outside of postgres.
3. Update row with new data (ex. UPDATE tbl SET .... WHERE id = 1)

The problem is that old data may change during point 2 so validation, even if passed may be outdated during point 3 (updating row). I know I could perform update like this UPDATE .... WHERE id = 1 AND column1='oldValue' to ensure certain fields didn't change, however my validation (also comparising new and old values) is very complex and I can't "write" it in SQL UPDATE statement.

So I thought about something like this:

1. BEGIN
2. SELECT * FROM tbl WHERE id = 1
3. Perform validation on my side
4. UPDATE SET=... WHERE id = 1
5. COMMIT

And I would like to COMMIT fail if row with id = 1 changed during transaction execution. Is it possible to use transaction like this? If not what is other solution?

Answer 1

Not sure why you are not updating directly with additional predicates in the where clause, but if that is not possible you can select for update as in:

SELECT * FROM tbl WHERE id = 1 FOR UPDATE

This will lock the selected row and prevent updates of it while you do your validation.

Answer 2

You could put a lock on the table while you do the validation.

BEGIN WORK;
LOCK TABLE tbl IN exclusive mode;
select * from tbl where id = 1;
--Do your validation 
update tbl set .. where id = 1;
COMMIT WORK;