Thread: [GENERAL] Is the row version available in SQL?
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.
Rob
On Wed, Jun 28, 2017 at 4:54 PM, Rob Nikander <rob.nikander@gmail.com> wrote:
For that, you could use xmin. That tracks the transaction where the row first became visible.
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.
Best Wishes,
Chris Travers
Rob
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
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
> 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