Re: Transaction size

Tom Lane wrote:
> Alban Hertroys <alban(at)magproductions(dot)nl> writes:
>>It does have a reference to a table with statusses, but those are rather
>>static. I suppose an integrity check is comparable to doing a select
>>with respect to locking strategies? (Meaning that it wouldn't be the
>>cause of my problem).
>
> No, unfortunately it's more like a SELECT FOR UPDATE and it does take a
> lock on the referenced row (with an eye to ensuring that the referenced
> row can't go away before the new referencing row is committed).
> I suspect this is indeed the cause of your problem.

I read this after solving the problem, but this was indeed happening
(though I didn't know that while solving it). We had cleaned up some
superfluous commits, but were a bit too enthousiastic about it and
removed the commits at the ends of the threads involved.

This resulted in a DB operation that never ended (until the thread would
have ended, which never happened because of this), because the commit
didn't take place, resulting on other threads waiting for this lock to
be released.

I now don't just understand why the threads never ended, I also
understand why it locked up at that particular point.

Thank you very much for your wisdom.

Regards,
Alban Hertroys.