Re: deadlock with truncate and foreing keys

"Alexey Nalbat" <nalbat(at)price(dot)ru> writes:

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

The reason TRUNCATE is so much faster is because it's quite different from
DELETE. That difference is why there's a deadlock here. So essentially it's
not a bug, it's an inherent feature of TRUNCATE.

I think what's going on here is that in transaction 1 the TRUNCATE locks t2
and then the INSERT tries to take a lock on a record in t1 to enforce the
referential integrity. In transaction 2 you've locked that record already to
do the update and are waiting on a lock on t2 to enforce the referential
integrity (actually on a specific record but it amounts to the same thing here
I think). So you have two transactions taking two locks in different orders.

I think a LOCK TABLE against t1 (and any other referencing tables) before
doing the truncate would effectively solve the problem by enforcing the lock
order being t1 then t2. Effectively it would mean waiting until any pending
updates to t1 are committed and then blocking any subsequent updates before
starting to rewrite t2. Assuming the truncates are rare compared to the
updates this is probably the best combination.

Alternatively putting a LOCK TABLE against t2 in transaction 2 might also
solve the problem at the expense of serializing all the updates.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning