In the chapter "Using optimistic locking" of the book "PG Cookbook Second Edition"
it is mentioned how the app can first fetch row from the table in the form
select a.*::text from table a where ...
Then do the work and then when it comes to committing do it as
update table
set ....
where table.*::text = (saved from select).
If the row was changed between the time it was first read and updated, the
update will do touch any rows as the ::text will be different.
Why can't we use xmin and xmax columns to achieve the same.
select a.*,xmin,xmax into ... from table A
do your work
update table
set ...
where pky = blahblah
and xmin = 2907587
and xmax = 0 ;
I tested it and it works. what I did was to select xmin and xmax and then sleep for a min.
In the meantime, I update the same row in another session.
After 1 min the update session failed to update any row because the combination of xmin
and xmax was no longer true.
I was under the impression that xmin/xmax can not be used in the where clause for business logic as described above.
Am I missing anything ? If this works, it can make optimistic locking lot easier due to generic coding using xmin/xmax.