deadlock with truncate and foreing keys

From: Alexey Nalbat <nalbat(at)price(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Cc: developers(at)price(dot)ru
Subject: deadlock with truncate and foreing keys
Date: 2008-02-18 10:53:24
Message-ID: 200802181353.25012.nalbat@price.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Hello.

I've encountered deadlock with first transaction updating information
field in the primary table, and second trasaction reloading secondary
table using TRUNCATE and INSERT. Here is simple example:

create table t1 ( id integer primary key, name text );
create table t2 ( id integer references t1 );
insert into t1 values ( 1 );
insert into t2 values ( 1 );

Then two concurrent transactions start.

/* 1 */ begin;
/* 1 */ truncate t2;
/* 2 */ begin;
/* 2 */ update t1 set name='foo' where id=1;
/* 1 */ insert into t2 values ( 1 );

Here we have deadlock.

ERROR: deadlock detected
DETAIL: Process 21581 waits for ShareLock on transaction 464; blocked by process 21619.
Process 21619 waits for AccessShareLock on relation 16456 of database 16385; blocked by process 21581.
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."t1" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"

When substituting TRUNCATE with DELETE, second transaction is not
waiting for first one, and there is no deadlock error. But I prefer to use
TRUNCATE because it is much faster. (In fact I use COPY instead of
INSERT to speed up reloading of secondary table.)

Is it bug? And if so, will it be fixed?

Thank you. Sorry for bad english.

--
Alexey A. Nalbat

Price Express
http://www.price.ru/
http://www.tyndex.ru/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message T.J. Adami 2008-02-18 12:02:13 Re: Use index for upper(customername) like 'ABC%'
Previous Message Dave Page 2008-02-18 10:11:19 Re: msvcr80.dll and PostgreSQL 8.3 under Windows XP

Browse pgsql-hackers by date

  From Date Subject
Next Message Pattu, Susanta Kumar 2008-02-18 13:03:56 Error"Failed to run initdb: 128!"
Previous Message Christian Robottom Reis 2008-02-17 22:51:01 Re: CVS repository invalid revision