I am trying to automatically assign account numbers in an accounting system by finding the highest account number in a given range and then adding one to it.

Basically, select max(number) from account where number >= :min and number <= :max and business = :business.

However, what I've noticed is that two accounts added simultaneously may get the same number. Obviously I'm not getting a write lock on the query for the last account number in use.

I'm using PostgreSQL 8 and glassfish via the JPA EntityManager API.

What I don't know is - is there any way to request a lock on this query so that the second add has to wait for the first one to complete before it can try to calculate the last used account number?


Thanks!