How to handle multiple servlet requests to update DB value

Question

I will be getting multiple Servlet requests form clients to update some entries in a DB. currently I use hibernate with Postgre. What I do is I read the DB for current value and then add one to that value and save on each request. But if two people read the DB on same time and add it it will not count as I want. I got some suggestions to make it "synchronized". But there can be a considerable delay for some users if number of requests get bigger.

Is there an faster way to update a value by +1, without reading and writing it? If the best practice is to use synchronizing then how to make it faster (less waiting time)?

Answer 1

You could try with a LOCK, check this: http://www.postgresql.org/docs/8.1/static/sql-lock.html

For doing everything (faster) in one query, check this: Increment a value in Postgres

Answer 2

You can use Spring transaction management (see docs here).

Spring config:

<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
  <property name="dataSource" ref="dataSource"/>
</bean>

Annotate your hibernate read and write methods with @Transactional like

@Transactional(value = "txManager" ...
public int incrementValue(){

Then define isolation property according to your needs. Look at TransactionDefinition for different isolation options. For example, TransactionDefinition.ISOLATION_READ_COMMITTED