Re: [GENERAL] Foreign Keys and Deadlocks

2011-11-10 Thread Csaba Nagy
Hi David, On Wed, 2011-11-09 at 09:52 -0800, David Kerr wrote: So, aside from removing the PKs do i have any other options? Sure you have: order the inserts by primary key inside each transaction. Then you will not get deadlocks, but inserting the same key again will fail of course (but that's

Re: [GENERAL] Foreign Keys and Deadlocks

2011-11-10 Thread David Kerr
On Thu, Nov 10, 2011 at 09:09:06AM +0100, Csaba Nagy wrote: - Hi David, - - On Wed, 2011-11-09 at 09:52 -0800, David Kerr wrote: - So, aside from removing the PKs do i have any other options? - - Sure you have: order the inserts by primary key inside each transaction. - Then you will not get

Re: [GENERAL] Foreign Keys and Deadlocks

2011-11-09 Thread Alvaro Herrera
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.

Re: [GENERAL] Foreign Keys and Deadlocks

2011-11-09 Thread David Kerr
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

Re: [GENERAL] Foreign Keys and Deadlocks

2011-11-09 Thread Alvaro Herrera
Excerpts from David Kerr's message of miƩ nov 09 14:52:01 -0300 2011: On Wed, Nov 09, 2011 at 11:11:23AM -0300, Alvaro Herrera wrote: - 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

Re: [GENERAL] Foreign Keys and Deadlocks

2011-11-05 Thread Csaba Nagy
Hi David, On Thu, 2011-11-03 at 15:30 -0700, David Kerr wrote: I suspect that it has to be a transaction, and that further up in the TX is an update to one of the reference tables in each TX. This is your cause - updating the referenced table in the same transaction. That will want an

Re: [GENERAL] Foreign Keys and Deadlocks

2011-11-04 Thread David Kerr
On Thu, Nov 03, 2011 at 03:30:20PM -0700, David Kerr wrote: - Howdy, - - We have a process that's deadlocking frequently. It's basically multiple threads inserting data into a single table. - - That table has FK constraints to 3 other tables. - - I understand how an FK check will cause a

[GENERAL] Foreign Keys and Deadlocks

2011-11-03 Thread David Kerr
Howdy, We have a process that's deadlocking frequently. It's basically multiple threads inserting data into a single table. That table has FK constraints to 3 other tables. I understand how an FK check will cause a sharelock to be acquired on the reference table and in some instances that