Thread: [GENERAL] Using xmin and xmax for optimistic locking
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 ...
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.
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.
On Mon, Feb 20, 2017 at 07:27:34PM +0000, Rakesh Kumar wrote: > 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. Works fine. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
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
On Mon, Feb 20, 2017 at 03:44:49PM -0500, Tom Lane wrote: > > 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. Also a consideration: table.*::text may become quite unwieldy if there's one or more BYTEA columns in the table. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> Also a consideration: table.*::text may become quite unwieldy
> if there's one or more BYTEA columns in the table.
One other thought here --- if you do want to go with the "no other
updates" semantics, it still seems like it should be sufficient to
compare xmins. Comparing the xmax values would add nothing to that,
except that it would reject if another update had been attempted and
then failed, which seems undesirable.
regards, tom lane
On Mon, Feb 20, 2017 at 04:22:51PM -0500, Tom Lane wrote: > One other thought here --- if you do want to go with the "no other > updates" semantics, it still seems like it should be sufficient to > compare xmins. Comparing the xmax values would add nothing to that, > except that it would reject if another update had been attempted and > then failed, which seems undesirable. Right, we have been doing that (xmin only) in GNUmed for years in order to detect concurrent updates to our medical record. Works like a charm. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346