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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
