Re: Foreign key quandries - Mailing list pgsql-hackers

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@rbt.ca>

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