"Alvin Hung" <alvin_hung@hotmail.com> writes:
> We have a need to coordinate multiple app-servers that are using the same
> database. Basically, we are thinking about using a table row as the
> coordination mechanism. When an app-server needs to perform an action that
> must be exclusive, it will try to lock that table row first. It will wait on
> that lock if someone else has it.
If there aren't very many distinct locks that you need to have, it'd be
better to dedicate a dummy table for each lock you need, and obtain the
lock with LOCK TABLE commands. This has both speed and flexibility
advantages --- there's only one kind of row lock, but there are multiple
kinds of table locks, so you could have e.g. both shared and exclusive
locking.
The LOCK TABLE mechanism doesn't scale real well to thousands of
different locks, however, so there are cases where you'd want to do it
with row locks.
regards, tom lane