We're updating the issue view to help you get more done. 

Pooled optimizer identifiers clash with INSERT rows calling sequence directly

Description

Giving the following identifier:

@Id
@GenericGenerator(name = "sampleGenerator", strategy = "enhanced-sequence",
parameters = {
@org.hibernate.annotations.Parameter(name = "optimizer", value = "pooled"),
@org.hibernate.annotations.Parameter(name = "initial_value", value = "1"),
@org.hibernate.annotations.Parameter(name = "increment_size", value = "2")
}
)
@GeneratedValue(strategy = GenerationType.TABLE, generator = "sampleGenerator")
private Long id;

When executing the following code:

doInTransaction(new TransactionCallable<Void>() {
@Override
public Void execute(Session session) {
for (int i = 0; i < 5; i++) {
session.persist(new SequenceIdentifier());
}
session.flush();
assertEquals(5, ((Number) session.createSQLQuery("SELECT COUNT FROM sequenceIdentifier").uniqueResult()).intValue());
insertNewRow(session);
insertNewRow(session);
assertEquals(7, ((Number) session.createSQLQuery("SELECT COUNT FROM sequenceIdentifier").uniqueResult()).intValue());
List<Number> ids = session.createSQLQuery("SELECT id FROM sequenceIdentifier").list();
for(Number id : ids) {
LOGGER.debug("Found id: {}", id);
}
for (int i = 0; i < 3; i++) {
session.persist(new SequenceIdentifier());
}
session.flush();
return null;
}
});

I get the following error:

Caused by: java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation; SYS_PK_10104 table: SEQUENCEIDENTIFIER

The last inserted id is 11, which was assigned by the database sequence upon inserting a row with a manual JDBC call.

When the pooled optimizer runs out of low values it calls the sequence and gets the max hi value of 13, so it will try to insert 11 and 12, which will clash with the manually inserted rows.

Isn't this optimizer supposed to offer a HI/LO implementation that manages to work with external systems which are unaware of Hibernate internal identifier strategies?

Environment

None

Status

Assignee

Andrea Boriero

Reporter

Vlad Mihalcea

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

4.3.5

Priority

Major