How to rollback transaction if row changed?

I am using Postgres 9.3.

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

• Fetch old row (ex. SELECT * FROM tbl WHERE id = 1)

• Validate new data in scope of old data (old row fetched in point 1) - this is done by my app outside of postgres.

• 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:

• BEGIN

• SELECT * FROM tbl WHERE id = 1

• Perform validation on my side

• UPDATE SET=... WHERE id = 1

• 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?

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.

StackExchange Database Administrators Q#94081, answer score: 7