Re: Foreign Keys and Deadlocks - Mailing list pgsql-general

On Wed, Nov 09, 2011 at 11:11:23AM -0300, Alvaro Herrera wrote:
-
- 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@postgresql.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.

Ok, well that's good to know. I had planned on testing my script w/o FKs but it slipped
my mind.

So, aside from removing the PKs do i have any other options? (we use Hibernate and
i don't think that I'll be able to removet he Primary Keys, and a serial primary key
probably isn't great for this table because it's sort of a staging area (so it gets
written to and wiped out frequently)

Would you consider this a problem in Pg or is it unavoidable?

Thanks

Dave