Using xmin and xmax for optimistic locking

From: Rakesh Kumar <rakeshkumar464(at)outlook(dot)com>
To: Pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Using xmin and xmax for optimistic locking
Date: 2017-02-20 19:27:34
Message-ID: MWHPR2201MB15651B320A20EB2F1E6F4A0C8C5E0@MWHPR2201MB1565.namprd22.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Karsten Hilbert 2017-02-20 19:47:12 Re: Using xmin and xmax for optimistic locking
Previous Message John R Pierce 2017-02-20 17:23:48 Re: Slow queries on very big (and partitioned) table