On 11/20/20 3:25 PM, Andy Fan wrote:> On Fri, Nov 20, 2020 at 9:37 PM
Andreas Karlsson <andr...@proxel.se
<mailto:andr...@proxel.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
