Обсуждение: How to speed up Exclusive Locking

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

On Tue, 5 Dec 2006 10:18:21 +0000, David Goodenough <david.goodenough@btconnect.com> wrote:

[...]

> 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;
>

So why selecting '1' for each row after locking the relation before? I don't
know HA-JDBC but this looks really useless. Remove the SELECT and use
the LOCK TABLE command within the transaction which does the sync for you.

Bernd

On Tuesday 05 December 2006 10:57, Bernd Helmle wrote:
> On Tue, 5 Dec 2006 10:18:21 +0000, David Goodenough
> <david.goodenough@btconnect.com> wrote:
>
> [...]
>
> > 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;
>
> So why selecting '1' for each row after locking the relation before? I
> don't know HA-JDBC but this looks really useless. Remove the SELECT and use
> the LOCK TABLE command within the transaction which does the sync for you.
I will give it a try.
>
> Bernd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

David Goodenough wrote:
> On Tuesday 05 December 2006 10:57, Bernd Helmle wrote:
>> On Tue, 5 Dec 2006 10:18:21 +0000, David Goodenough
>> <david.goodenough@btconnect.com> wrote:
>>
>>> The statements issued to lock each table is:-
>>>  LOCK TABLE table IN EXCLUSIVE MODE; SELECT 1 FROM table;
>> So why selecting '1' for each row after locking the relation before? I
>> don't know HA-JDBC but this looks really useless. Remove the SELECT and use
>> the LOCK TABLE command within the transaction which does the sync for you.

> I will give it a try.

It could be that the HA-JDBC code expects some selected value back. In
which case a simple "SELECT 1" should be fine. I have to agree with
Bernd that selecting all rows and then throwing away the results strikes
me as particularly a braindead behaviour from the library, presumably it
makes some sort of sense for locking a limited number of rows.

--
   Richard Huxton
   Archonet Ltd

On Tuesday 05 December 2006 12:03, Richard Huxton wrote:
> David Goodenough wrote:
> > On Tuesday 05 December 2006 10:57, Bernd Helmle wrote:
> >> On Tue, 5 Dec 2006 10:18:21 +0000, David Goodenough
> >>
> >> <david.goodenough@btconnect.com> wrote:
> >>> The statements issued to lock each table is:-
> >>>  LOCK TABLE table IN EXCLUSIVE MODE; SELECT 1 FROM table;
> >>
> >> So why selecting '1' for each row after locking the relation before? I
> >> don't know HA-JDBC but this looks really useless. Remove the SELECT and
> >> use the LOCK TABLE command within the transaction which does the sync
> >> for you.
> >
> > I will give it a try.
>
> It could be that the HA-JDBC code expects some selected value back. In
> which case a simple "SELECT 1" should be fine. I have to agree with
> Bernd that selecting all rows and then throwing away the results strikes
> me as particularly a braindead behaviour from the library, presumably it
> makes some sort of sense for locking a limited number of rows.
HA-JDBC only ever locks a whole table.  As far as I can see it does not
use the ResultSet (and JDBC large ResultSets are never a good idea), so I
have asked the question on its forum why it is there.

Testing with psql a simple LOCK seems pleasantly fast.

David

David Goodenough <david.goodenough@btconnect.com> writes:
> 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.

No, but it's *reading* each row to execute the "SELECT 1 FROM table".

            regards, tom lane