Thread: Transaction size
Is it possible that there is some limitation to the number of statements in a single transaction? I'm trying to debug a problem where a program locks up during an insert[*]. Actually, there are 3 simultaneous transactions going on, all doing inserts of about 3000 records each. The problem occurs when I'm about 400 records from the end of the transaction. As they're inserts, and therefore not even touching the same data, I'm quite certain it's not some kind of row locking issue (does that even happen at all with MVCC?). I'm just fishing around, it may just as well turn out to be a threading problem in my program, but I've been staring at it for days now and it doesn't seem to make more sense then it did at the begin... Any insights much appreciated. [*]: It prints debug output before the query is executed, and it should after. BTW: My thanks to whoever changed the color of the links in the docs to the current color. The contrast/readability is much better now (compared to last week).
Alban Hertroys <alban@magproductions.nl> writes:
> Is it possible that there is some limitation to the number of statements
> in a single transaction?
2^32, and if you'd exceeded it, you'd get a very specific error message
saying so.
> As they're inserts, and therefore not even touching the same data, I'm
> quite certain it's not some kind of row locking issue (does that even
> happen at all with MVCC?).
I'm not. In particular this could be a foreign key locking issue ---
does the target table have foreign keys, and if so could inserts from
different transactions be referencing the same master row?
regards, tom lane
Tom Lane wrote: > Alban Hertroys <alban@magproductions.nl> writes: > >>Is it possible that there is some limitation to the number of statements >>in a single transaction? > > 2^32, and if you'd exceeded it, you'd get a very specific error message > saying so. Well, that's a relief. At least it means I'm not running into some limit of my favourite database. >>As they're inserts, and therefore not even touching the same data, I'm >>quite certain it's not some kind of row locking issue (does that even >>happen at all with MVCC?). > > I'm not. In particular this could be a foreign key locking issue --- > does the target table have foreign keys, and if so could inserts from > different transactions be referencing the same master row? 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). Regards, Alban Hertroys.
Alban Hertroys <alban@magproductions.nl> writes:
> Tom Lane wrote:
>> Alban Hertroys <alban@magproductions.nl> writes:
>>> As they're inserts, and therefore not even touching the same data, I'm
>>> quite certain it's not some kind of row locking issue (does that even
>>> happen at all with MVCC?).
>>
>> I'm not. In particular this could be a foreign key locking issue ---
>> does the target table have foreign keys, and if so could inserts from
>> different transactions be referencing the same master row?
> 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.
regards, tom lane
Tom Lane wrote: > Alban Hertroys <alban@magproductions.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.