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.
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 |