[GENERAL] Using xmin and xmax for optimistic locking
От | Rakesh Kumar |
---|---|
Тема | [GENERAL] Using xmin and xmax for optimistic locking |
Дата | |
Msg-id | MWHPR2201MB15651B320A20EB2F1E6F4A0C8C5E0@MWHPR2201MB1565.namprd22.prod.outlook.com обсуждение исходный текст |
Ответы |
Re: [GENERAL] Using xmin and xmax for optimistic locking
Re: [GENERAL] Using xmin and xmax for optimistic locking |
Список | 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 ...
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.
В списке pgsql-general по дате отправления: