Re: How to get updated order data

On Fri, Apr 8, 2022 at 2:26 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Thu, 2022-04-07 at 16:16 +0300, Andrus wrote:
> > Orders are in table
> >
> > create table order (
> > dokumnr int primary key,
> > packno char(10)
> > );
> > insert into order dokumnr values (123);
> >
> > One user sets pack number using
> >
> > update order set packno='Pack1' where dokumnr=123
> >
> > 3 seconds later other user retrieves pack number using
> >
> > select packno from order where dokumnr=123
> >
> > However, other user gets null value, not Pack1 as expected. After some
> time later, correct value Pack1 is returned.
> >
> > How to get updated data from other user immediately?
> > 3 seconds is long time, it is expected that select suld retrieve update
> data.
> >
> > There are lot of transactions running concurrently. Maybe update
> command is not written to database if second user retrieves it.
> >
> > How to flush orders table so that current results are returned for
> second user select ?
> >
> > Using
> > PostgreSQL 13.2, compiled by Visual C++ build 1914, 64-bit
> > and psqlODBC driver.
>
> That cannot happen, unless
>
> a) the UPDATE runs in a transaction that hasn't been committed
>
> b) the SELECT is running on a standby server, and there is replication lag
>

There's also:

c) The SELECT runs in a transaction stat *started* before the transaction
that a runs in. (Assuming it then retries with a new transaction later,
that is)

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>