I have an application running on a Tomcat cluster talking to a cluster of
Postgresql DBs using HA-JDBC. If one of the members drop out of the cluster
it is necessary to get that member back into sync with the rest of the
cluster, and I have an application specific piece of code that does that.
All the records have an updated timestamp in them which makes life easier.
The first bits of the sync are done without locking the source tables, and
I do these until I find less than some suitable threshold of records needing
to be updated. Then I lock the source tables and do the final sync.
The statements issued to lock each table is:-
LOCK TABLE table IN EXCLUSIVE MODE; SELECT 1 FROM table;
(I am not quite sure why the SELECT 1 FROM table is there, it came with
HA-JDBC as the code for the Postgresql dialect).
I notice that this seems to take a time that is dependant on the size
of the table, which seems odd - almost as though it is locking each row
rather than the whole table at once. I am using 8.1 by the way just in
case this is something that has changed in 8.2.
Taking locks on the 7 tables takes over five minutes, which is much longer
that I would have hoped. Is there anything I can do to speed this up?
The rest of the application components never do explicit locking, they select
for read only, or select for update, insert, update and delete all inside a
transaction.
Regards
David