On Fri, 20 Aug 2004, Ulrich Wisser wrote:
> but how long is the exclusive lock taken? For the whole transaction?
Until transaction end.
> My jobs are quite big, and transactions usually take several minutes.
> I can not afford have all my other jobs stalled for that time.
Generally right now your options are:
Remove the constraint
Edit the code to remove the FOR UPDATE clauses used in the constraint
Defer the constraint (thus the locks are only taken at the end for
pretty much the time to check the constraints). This however means
that you're accumulating the deferred constraint information which
takes RAM.
None of these are complete fixes. The complete fix is changing the
constraint to use a different locking mechanism (that doesn't currently
exist). Another partial fix that's been described to me would be to see if
strengthening the locks on actions on the referenced table would allow
weakening of the locks on the referencing table.