2

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?

  • 1
    Note that the accepted answer does not "rollback transaction if row changed", rather it prevents the row to change (until commit). To test if a row has changed between 2 points in time you can simply put it into a record variable and perform a row-wise comparison later. – Daniel Vérité Feb 28 '15 at 23:12
5

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.

1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.