Re: Different results between PostgreSQL and Oracle for "for update" statement

On 11/20/20 3:25 PM, Andy Fan wrote:> On Fri, Nov 20, 2020 at 9:37 PM
Andreas Karlsson <andreas(at)proxel(dot)se
> <mailto:andreas(at)proxel(dot)se>> wrote:
>
> On 11/20/20 9:57 AM, Andy Fan wrote:
> > Thank you for your attention. Your suggestion would fix the
> issue.  However
> > The difference will cause some risks when users move their
> application
> > from Oracle
> > to PostgreSQL. So I'd like to think which behavior is more
> reasonable.
>
> I think PostgreSQL's behavior is more reasonable since it only locks
> the
> rows it claims to lock and no extra rows. This makes the code easy to
> reason about. And PostgreSQL does not re-evaluate sub queries after
> grabbing the lock which while it might be surprising to some people is
> also a quite nice consistent behavior in practice as long as you are
> aware of it.
>
> I admit my way is bad after reading your below question, but I
> would not think *it might be surprising to some people* is a good signal
> for a design.  Would you think "re-evaluate the quals" after grabbing the
> lock should be a good idea? And do you know if any other database uses
> the postgres's way or Oracle's way?   I just heard Oracle might do the
> re-check just some minutes before reading your reply and I also found
> Oracle doesn't lock the extra rows per my test.

Re-evaluating the sub queries is probably a very bad idea in practice
since a sub query can have side effects, side effects which could really
mess up some poor developer's database if they are unaware of it. The
tradeoff PostgreSQL has made is not perfect but on top of my head I
cannot think of anything less bad.

I am sadly not familiar enough with Oracle or have access to any Oracle
license so I cannot comment on how Oracle have implemented their behvior
or what tradeoffs they have made.

Andreas