Re: help with locked table(s)/transactions(s)

Tom Lane wrote:

> SELECTs don't lock any rows. INSERTs don't create any lockable rows
> in themselves (other backends can't even see the rows yet). If that's
> all that your transaction is doing, then I think the only explanation
> is that the INSERTs are in a table that has foreign keys (correct?)
> and that the row conflicts are on the referenced table. Before 8.1,
> we take a row lock on the referenced row to ensure that it won't be
> deleted before we can commit the referencing row. If you've got
> other transactions that are actively modifying the referenced table,
> then that's the source of the problem. Worse, you can get conflicts
> just from inserting other referencing rows with the same key values.
> The latter case is fixed in 8.1 by using a share instead of exclusive
> row lock, but that mechanism isn't available in 7.4 ...

Actually, the transaction has UPDATEs as well, but yes, the INSERTS are
on tables with foreign keys.

I'm still confused by the behavior however. The locking behaves as if
its some kind of *table* level lock, because while the function is
executing (a long time), a dozen updates and inserts build up waiting
for some lock to be released. If the loop just occassionally puts a lock
on a few different rows, I don't see how that could cause the
experienced behavior -- presumably the lock on those particular rows is
released after its iteration through the loop. Unless, of course, the
lock isn't given up ...

ps - thanks for noting or recognizing that I'm using 7.4. forgot to
mention that.