I had an idea on a possible way to increase performance under query
loads with a lot of short-term locking. I haven't looked at the
implementation of this at all, so if someone could tell me why this
wouldn't work, that would save me some time ;-)
AFAIK, current Postgres behavior when processing SELECT queries is like
this:
(1) for each tuple in the result set, try to get an AccessShareLock on it(2) if it can't acqure the lock,
waituntil it can
(3) read the data on the previously locked row and continue onward
i.e. when it encounters a locked row, it waits for the lock to be
released and then continued the scan.
Instead, why not modify the behavior in (2) so that instead of waiting
for the lock to be released, Postgres would instead continue the scan,
keeping a note that it has skipped over the locked tuple. When it has
finished the scan (and so it has the entire result set, except for the
locked tuples), it should return to each of the previously locked
tuples. Since most locks are relatively short-term (AFAIK), there's a
good chance that during the time it took to scan the rest of the table,
the lock on the tuple has been released -- so it can read the value and
add it into the result set at the appropriate place without needing to
do nothing while waiting for the lock to be released.
This is probably stupid for some reason: can someone let me know what
that reason is? ;-)
Cheers,
Neil
--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC