Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] Is the row version available in SQL?

[GENERAL] Is the row version available in SQL?

From
Rob Nikander
Date:
Hi,


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


Re: [GENERAL] Is the row version available in SQL?

From
Chris Travers
Date:


On Wed, Jun 28, 2017 at 4:54 PM, Rob Nikander <rob.nikander@gmail.com> wrote:
Hi,


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.

Re: [GENERAL] Is the row version available in SQL?

From
Merlin Moncure
Date:
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

Re: [GENERAL] Is the row version available in SQL?

From
Rob Nikander
Date:
> 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