I am trying to write a simple incremental counter:

I have a very simple object that contains just an int for the database ID and a long for the counter. It has a getNext() method that increments the int and returns it's value.

I also have an EJB (Stateless session bean) that:
- loads this simple object from the database,
- calls the getNext() method,
- saves the object back to the database,
- and returns the counter value.

When I run a single instance of a client program to call the EJB repeatedly I get the predicted output:
1,2,3,4,5,6,7,8,9,10
and so on, with the last value always being stored in the database.

If I run two instances of the client program I expect each client to recieve different values from the EJB, but occasionally both clients receive the same value!

E.G. (Client1 and Client2 are running at the same time.)

Client1
1
3
5
6
9
12

Client 2
2
4
7
8
9 <---- Wrong!
11
13

Does anybody have any ideas why this is happening?
(Even if you can help narrow it to a problem in my EJB or Hibernate code would be great.)


Hibernate version: 2.17

Mapping documents:
<hibernate-mapping>
<class
name="com.test.Counter"
dynamic-update="false"
dynamic-insert="false"
select-before-update="false"
optimistic-lock="version">

<id name="id"
column="ID"
type="java.lang.Long">
<generator class="native"></generator>
</id>

<property
name="counter"
type="long"
update="true"
insert="true"
access="property"
column="counter"/>
</class>
</hibernate-mapping>


Name and version of the database you are using: MySQL 4.1.7

The generated SQL (show_sql=true):

You are trying to use optimistic locking with versioning without having any versioning column. Either add a version column or use pessimistic locking.

Many thanks, I added a version column and it all works fine now.