Re: Lock while delete a table

On Mon, 2 Jan 2012 11:40:47 -0500
Sergio Sinuco <sergiosinuco(at)datatraffic(dot)com(dot)co> wrote:

> Hi guys. I have a table A with a foreign key that refers a table B. Next, I
> have a function F that updates some rows in B. When I delete table A, a
> deadlock occurs because the function and the process that delete the table
> A are waiting for an AccessExclusiveLock to relation B.
>
> Why does a drop table need an AccessExclusiveLock in tables refered by
> the deleted table?

At 1st sight, I would say that's quite logic:
* you delete A, which contains RIs from B,
* as you're in a concurrent environment, any key from B can be
modified (+cascade) while you delete A,
* thus A could receive RI column modification(s) while deleting,
which is normal, but is a non sens as you *delete* A,
* so the system locks B to avoid such a situation.

JY
--
We are what we pretend to be.
-- Kurt Vonnegut, Jr.