Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Well, yes, it has to take a lock on the subtransaction XID, which will
>> be held until main transaction commit. I'm not sure we have much of a
>> choice about this --- although it does seem annoying to have a
>> shared-memory-size constraint on how many subtransactions you can have.
> You mean 64 (the number of object locks)?
No, max_locks_per_transaction * max_connections. In practice it seems
to get unhappy at about 10000 locks given default shared-memory sizing.
This is because we leave some overhead slop that the locks table can
expand into before failing altogether.
> Can you clarify why the
> subtransaction is locked in this case and not others?
It's locked in all cases. A *failed* subtransaction will release its
lock, but a non-failed one will hold the lock till main commit.
We could revert to Alvaro's initial design in which subxacts didn't take
out separate locks on their XIDs; this would make XactLockTableWait a
lot slower though, and probably subject to unwanted deadlocks. You
really want to release waiters for a subxact as soon as the subxact
fails, rather than making them wait around for the parent.
Right now I'm not seeing a cure that's not worse than the disease.
regards, tom lane