Re: Using xmin and xmax for optimistic locking

Rakesh Kumar <rakeshkumar464(at)outlook(dot)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