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/
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 |
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 |