Re: Foreign Keys and Deadlocks

Excerpts from David Kerr's message of vie nov 04 13:01:29 -0300 2011:

> I did more digging and found some good discussions on the subject in general, but
> most of the examples out there contain explicit updates (which is why i was confused)
> but it looks like it's being addressed.
>
>
> http://justatheory.com/computers/databases/postgresql/fk-locks-project.html
> http://www.mail-archive.com/pgsql-hackers(at)postgresql(dot)org/msg158205.html
> http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/
>
> Attached is the script to reproduce it with only inserts (for postarities sake)

Actually, your script as presented has nothing to do with foreign keys.
The cause for the lock and the deadlock is not in the tuple lock code,
but in the primary key uniqueness check. You can duplicate your issue
with a single one-column table:

Session one:

alvherre=# create table pk (a int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «pk_pkey» para la tabla «pk»
CREATE TABLE
alvherre=# begin;
BEGIN
alvherre=# insert into pk values (1);
INSERT 0 1

Session two:

alvherre=# begin;
BEGIN
alvherre=# insert into pk values (2);
INSERT 0 1
alvherre=# insert into pk values (1);
<blocks>

Now go back to session one and

alvherre=# insert into pk values (2);
ERROR: se ha detectado un deadlock
DETALLE: El proceso 17430 espera ShareLock en transacción 710; bloqueado por proceso 17495.
El proceso 17495 espera ShareLock en transacción 709; bloqueado por proceso 17430.
SUGERENCIA: Vea el registro del servidor para obtener detalles de las consultas.

This case is not helped by the patch I'm working on. As far as I can
see, if you got rid of the PK in table a in your example script, things
should work just fine. There is no way to cause FK-induced deadlocks
with only inserts in 8.1 and later.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support