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

Re: Referential Integrity and SHARE locks

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Referential Integrity and SHARE locks
Date: 2007-02-02 10:14:15
Message-ID: 1170411255.3101.75.camel@coppola.muc.ecircle.de (view raw or flat)
Thread:
Lists: pgsql-hackers
On Fri, 2007-02-02 at 10:51, Simon Riggs wrote:
[snip]
> Why do we need a SHARE lock at all, on the **referenc(ed)** table?
> 
> It sounds like if we don't put a SHARE lock on the referenced table then
> we can end the transaction in an inconsistent state if the referenced
> table has concurrent UPDATEs or DELETEs. BUT those operations do impose
> locking rules back onto the referencing tables that would not be granted
> until after any changes to the referencing table complete, whereupon
> they would restrict or cascade. So an inconsistent state doesn't seem
> possible to me.
> 
> What am I missing?

Well, here we do have a patch (deployed on production servers) which
does not put the shared lock on the referenced table, and it lets in
occasionally rows in the referencing tables which do not have parent
rows in the referenced table. I'm not sure what is the mechanism, but it
does happen, I can assure you. It happens rare enough that is not
disturbing for us, compared to the deadlocks which happen without the
patch - that's another matter...

In our application we never update any key ids, so only deletes/inserts
come in play, and I guess it happens when a referenced row is deleted
just between a newly inserted child row checks that the parent row
exists and the row is really inserted. Or something like that...

Cheers,
Csaba.



In response to

Responses

pgsql-hackers by date

Next:From: Richard HuxtonDate: 2007-02-02 10:25:37
Subject: Re: Referential Integrity and SHARE locks
Previous:From: Richard HuxtonDate: 2007-02-02 10:09:24
Subject: Re: Function proposal to find the type of a datum

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