Re: Is the row version available in SQL?

On Wed, Jun 28, 2017 at 9:54 AM, Rob Nikander <rob(dot)nikander(at)gmail(dot)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