Is the row version available in SQL?
Hi,
I'm reading about MVCC here: https://www.postgresql.org/docs/current/static/mvcc.html <https://www.postgresql.org/docs/current/static/mvcc.html>.
In some cases I can use these transaction isolation modes, but in other cases, I may want to manage the versions and concurrency manually. Is there a way to select the “version” of a row, or is that data hidden to ordinary SQL statements?
I’m thinking about a sequence of actions, which are logically a transaction, but start on one thread and complete later on another thread. I don’t think I can tie up a JDBC database connection for that long. I can add a `version bigint` column to my table, and the final update will look like:
update ... where id = ? and version = the_version_selected_earlier_in_a_different_thread
… and if that gets 0 rows, it can handle the conflict.
Rob
On Wed, Jun 28, 2017 at 4:54 PM, Rob Nikander <rob.nikander@gmail.com>
wrote:
Hi,
I'm reading about MVCC here: https://www.postgresql.
org/docs/current/static/mvcc.html.In some cases I can use these transaction isolation modes, but in other
cases, I may want to manage the versions and concurrency manually. Is there
a way to select the “version” of a row, or is that data hidden to ordinary
SQL statements?I’m thinking about a sequence of actions, which are logically a
transaction, but start on one thread and complete later on another thread.
I don’t think I can tie up a JDBC database connection for that long. I can
add a `version bigint` column to my table, and the final update will look
like:update ... where id = ? and version = the_version_selected_earlier_
in_a_different_thread… and if that gets 0 rows, it can handle the conflict.
For that, you could use xmin. That tracks the transaction where the row
first became visible.
Best Wishes,
Chris Travers
Rob
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more
On Wed, Jun 28, 2017 at 9:54 AM, Rob Nikander <rob.nikander@gmail.com> wrote:
Hi,
I'm reading about MVCC here:
https://www.postgresql.org/docs/current/static/mvcc.html.In some cases I can use these transaction isolation modes, but in other
cases, I may want to manage the versions and concurrency manually. Is there
a way to select the “version” of a row, or is that data hidden to ordinary
SQL statements?I’m thinking about a sequence of actions, which are logically a transaction,
but start on one thread and complete later on another thread. I don’t think
I can tie up a JDBC database connection for that long. I can add a `version
bigint` column to my table, and the final update will look like:update ... where id = ? and version =
the_version_selected_earlier_in_a_different_thread… and if that gets 0 rows, it can handle the conflict.
What you are describing is called 'optimistic locking'. If you google
that term you will see lots of tutorials on the approach. Here's an
interesting write up on it by our good friends at 2ndquadrant:
https://blog.2ndquadrant.com/postgresql-anti-patterns-read-modify-write-cycles/
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Jun 28, 2017, at 11:39 AM, Chris Travers <chris.travers@gmail.com> wrote:
For that, you could use xmin. That tracks the transaction where the row first became visible.
On Jun 28, 2017, at 4:34 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
What you are describing is called 'optimistic locking’. […]
https://blog.2ndquadrant.com/postgresql-anti-patterns-read-modify-write-cycles/
Thanks, both answers helped.
Rob
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general