rajsubramani wrote:

> 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
> commit is executed (auto commit is false). ORACLE also allows a
> WAIT/NOWAIT parameter at the end which hangs/throws exception
> (repectively) if anyone tries to insert or update column foo.
>
> 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 ?

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).

--
Olivier Mascia