Search Postgresql Archives

Re: Foreign Keys and Deadlocks

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux