Re: FOR SHARE vs FOR UPDATE locks

Michael Paesold <mpaesold@gmx.at> writes:
> Tom Lane wrote:
>>> I'm tempted to just error out in this scenario rather than allow the
>>> lock upgrade.  Thoughts?

> Although this seems to be a technically hard problem, the above sentence 
> does not sound like the PostgreSQL way to solve problems (rather like 
> MySQL). ;-)

No, the MySQL way is to let it do something that's easy, fast, and sort
of works most of the time ;-)

> Now seriously, isn't this a perfectly feasible scenario? E.g. the outer 
> transaction acquires a shared lock because of foreign key constraints, and 
> the sub transaction later wants to update that row?

Yeah, it's not implausible.  But the only way I can see to implement
that is to upgrade the outer xact's shared lock to exclusive, and that
doesn't seem real cool either.

More to the point, we are up against a release deadline, and so the only
options we have today are for things that are simple, bulletproof, and
don't lock us out of doing something smarter later.  Hence my suggestion
to throw an error.
        regards, tom lane