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?
Aucun commentaire:
Enregistrer un commentaire