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/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
