Thanks for the reply.

> > For the other databses that I work with (IBM, ORACLE), when you

execute

> > select max(foo) from bar for update of foo
> > where foo is of type NUMERIC(18,0). It locks the column foo until a
> > This does not seem to be the case with firebird though the document
> > seems to suggest that it is supported.

>
> Why would you want such a lock in this case ?
> Do you fear that other transactions might change one of the values
> you're summing while the sum is being done ?

A web based deployment via Java servlet allows hundreds (if not
thousands of entry into the DB).

As a service offering, if someone needs to "added in" to the DB (with
all their related data) then I need to get the next max id for a
table, add the person and add all the related data of that person for
the next id. That is why I needed to lock the table (since the web
service cannot be shut, users will continue with read access but
write/update will be denied till I finish my process).

>
> Just run your statement inside a default Firebird transaction (which is
> of type "Concurrency" and you'll get consistent results for your
> max(foo), without seeing the other transactions commits, yet without
> blocking those other transactions to write and commit.
>
> If you want to get the MAX(foo) to then increment it and obtain a new
> unique greater value for a new insert, then drop this scheme. Better

use

> a GENERATOR and a trigger on INSERT to do that job. GENERATORs are
> designed for such uses and their value increments accross all
> transactions (they live outside of transactions in other words).
>

I have not looked at Generators before. I have a server side faced to
separate the SQL from other Java code (so that DB migration is easier
to achieve). I guess if I can use a Generator via JDBC/SQL then I
guess thats the only way for Firebird. Select for Update was what I
was used to and well, I quite like it.
:-(

Cheers
-raj