Mott Leroy <mott@acadaca.com> writes:
> Will postgres lock on all rows as it goes through this loop? Or can you
> give me a better idea of what I can expect to be locked in my example?
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 ...
regards, tom lane