Re: relation ### modified while in use

Alex Pilosov <alex(at)pilosoft(dot)com> writes:
> On Mon, 23 Oct 2000, Tom Lane wrote:
>> begin;
>> select * from foo; -- gets AccessShareLock
>> LOCK TABLE foo; -- gets AccessExclusiveLock
>> ...
>> end;
>>
>> this will work currently because the SELECT releases AccessShareLock
>> when done, but it will deadlock if SELECT does not release that lock.

> Probably a silly question, but since this is the same transaction,
> couldn't the lock be 'upgraded' without a problem?

No, the problem happens when two transactions do the above at about
the same time. After the SELECTs, both transactions are holding
AccessShareLock, and both are waiting for the other to let go so's they
can get AccessExclusiveLock. AFAIK any concept of "lock upgrade" falls
afoul of this basic deadlock risk.

We do have a need to be careful that the system doesn't try to do
lock upgrades internally. For example, in
LOCK TABLE foo;
the parsing step had better not grab AccessShareLock on foo in
advance of the main execution step asking for AccessExclusiveLock.

regards, tom lane