samedi 28 février 2015

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:



  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?





Aucun commentaire:

Enregistrer un commentaire