From: | David Kerr <dmk(at)mr-paradox(dot)net> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Pg General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Foreign Keys and Deadlocks |
Date: | 2011-11-09 17:52:01 |
Message-ID: | 20111109175200.GA92992@mr-paradox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bob Pawley | 2011-11-09 17:53:37 | Re: DB Dump |
Previous Message | Guillaume Lelarge | 2011-11-09 17:49:39 | Re: Create Tables As Specific Role |