Skip site navigation (1) Skip section navigation (2)

Re: Foreign key quandries

From: Rod Taylor <rbt(at)rbt(dot)ca>
To: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Foreign key quandries
Date: 2003-03-01 07:05:51
Message-ID: 1046502342.26763.78.camel@jester (view raw or flat)
Thread:
Lists: pgsql-hackers
Gah, hit wrong key combination and the email sent early.

Anyway, after that 'sleep' mess at the bottom is:
T1 or T2: Sleeping too long -- lets run deadlock detection code
T1 or T2: Kill off random participant of deadlock.

The other participant is then allowed to continue their work.

On Sat, 2003-03-01 at 02:03, Rod Taylor wrote:
> On Sat, 2003-03-01 at 00:44, Stephan Szabo wrote:
> > On 1 Mar 2003, Rod Taylor wrote:
> > 
> > > I'm not sure I understand the question. The case as described simply has
> > > to deadlock because your approaching the same values with conflicting
> > > tasks from opposite directions.
> > 
> > Well, the problem is that two cases (one of which I think deadlock is
> > unnecessary in) are very similar.
> 
> I see.  Now I see what your asking about.
> 
> > As I see it:
> > 
> > T1: insert 2
> > T2: delete 2
> > T1: insert 2/update 2 (non-key fields)
> >  shouldn't need to deadlock.
> 
> > T1: insert 2
> > T2: delete 2 & 3
> >    * delete 2's check blocks before
> >       checking 3
> > T1: insert 3
> >  should not need to deadlock I think
> 
> > T1: insert 2
> > T2: delete 3
> > T2: delete 2
> >  (or delete 2 & 3 where 3's check goes then 2's check blocks)
> > T1: insert 3
> >  does need to deadlock
> > 
> > In the second case, both deletes have happened so the row the insert wants
> > to check against is marked for deletion, but since it's going to be
> > checking for the 3 row in the future, and will error if T1 commits, I
> > think it's safe for it to go through.
> > 
> > I'm trying to find a way to differentiate the second and third case given
> > that I'm running inside a constraint check on insert 3. It'd be easy if
> > transaction 1 could see that it's going to be checking for the 3 row
> > later, but I think that'd involve keeping around alot of information about
> > the rows that are affected in some shared way which could get really
> > large.
> 
> Isn't the differentiation going to happen automatically?
> 
> In case 2:
> 
> T1: create fk tuple (uncommitted) -> value 2
> T2: delete pk tuple value 2
> T2:	scan through fk table, find uncommitted tuple value 2 ... sleep
> T2:	scan through fk table, find uncommitted tuple value 2 ... sleep
> T2:	scan through fk table, find uncommitted tuple value 2 ... sleep
> T1: create fk tuple (uncommitted) -> value 3
> T1: commit
> T2:	scan through fk table, find tuple value 2 ... its committed
> T2: <run cascade procedure on tuples found in fk table for value 2>
> T2: continue scan through fk table, find tuple value 3 ... its committed
> T2: <run cascade procedure on tuples found in fk table for value 3>
> T2: All is well -- return control to user.
> 
> In case 3:
> T1: create fk tuple (uncommitted) -> value 2
> T2: delete pk tuple value 3
> T2:	scan through fk table, value 3 not found
> T2: delete pk tuple value 2
> T2:	scan through fk table, find uncommitted tuple value 2 ... sleep
> T2:	scan through fk table, find uncommitted tuple value 2 ... sleep
> T2:	scan through fk table, find uncommitted tuple value 2 ... sleep
> T1: create fk value 3
> T1:	scan through pk table, find uncommitted tuple value 3 ... sleep
> T2:	scan through fk table, find uncommitted tuple value 2 ... sleep
> T1:	scan through pk table, find uncommitted tuple value 3 ... sleep
> T2:	scan through fk table, find uncommitted tuple value 2 ... sleep
-- 
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

In response to

Responses

pgsql-hackers by date

Next:From: Stephan SzaboDate: 2003-03-01 07:38:23
Subject: Re: Foreign key quandries
Previous:From: Rod TaylorDate: 2003-03-01 07:03:01
Subject: Re: Foreign key quandries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group