Обсуждение: getting deadlocks on full table lock

I am getting a deadlock which I can't explain...although maybe
somebody else can...here's teh situation:

I have a plpgsql function which increments a sequence when called.
when the sequence hits a certain threshold, the sequence is reset and
a materilized structure (small table) is updated.  The only thing that
writes to this materilized table is the update function.  When the
function executes repeatedly from multiple backends, all backends
repeatedly deadlock on AccessExclusiveLock and abort.

I only get the deadlock on truncate of lock table..., not on delete * from.

Here is a simplified version of the functions:
create function worker() returns void as
$$
  begin
    -- read some stuff off of mat_table
   perform update_mat() where nextval('mat_seq') > 100;
  end;
$$ language plpgsql;

create function update_mat() returns void as
$$
  truncate mat_table;  -- or lock table mat_table
  insert into mat_table select stuff from real_tables;
  select setval('mat_seq', 1, false),
$$ language sql;

If I call worker quicly from multiple backends (not in transaction), I
get tons of deadlocks.  Each process says it and another process is
waiting on mat_table.  My understanding is that that each backend
should wait on a lock if it is not available. It feels like the locks
are not getting released.

using delete from...prevents deadlocks but then I have to deal with
mvcc table bloat.  what am I doing wrong?

Merlin

"Merlin Moncure" <mmoncure@gmail.com> writes:
> I am getting a deadlock which I can't explain...although maybe
> somebody else can...here's teh situation:

TRUNCATE requires exclusive lock, and won't release it until end of
transaction.  Since you've already read something from the target
table before attempting TRUNCATE, you already have AccessShareLock
and are attempting to acquire AccessExclusiveLock.  Classic deadly
embrace.

            regards, tom lane

On 3/27/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Merlin Moncure" <mmoncure@gmail.com> writes:
> > I am getting a deadlock which I can't explain...although maybe
> > somebody else can...here's the situation:
>
> TRUNCATE requires exclusive lock, and won't release it until end of
> transaction.  Since you've already read something from the target
> table before attempting TRUNCATE, you already have AccessShareLock
> and are attempting to acquire AccessExclusiveLock.  Classic deadly
> embrace.

duh.  Anyways, I moved the update() above read part and this
eliminated most of the deadlocks...but I still got a few.  Throwing a
'lock table mat_table' before truncate killed them all though.

Merlin