Re: [GENERAL] Using xmin and xmax for optimistic locking

Rakesh Kumar <rakeshkumar464@outlook.com> writes:
> 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.

Well, that doesn't do quite the same thing: the cookbook query will
proceed if there was a no-op update in between (or maybe even two updates
that canceled each other out).  If you look at xmin then you won't proceed
in such cases.  I could imagine either behavior being "right" depending on
your application needs.

            regards, tom lane