I am struggling to insert new values into a SQL server table which has a primary key (CANCELLATION_REF) which is an integer, but is NOT an identity column (does not auto-generate id). I therefore have to generate my own primary key values.

As I understand from the docs, a common solution is to use HILO generation strategy - however there will be other applications which also concurrently insert into this table, which are out of my control, so it seems to me that HILO will fail in this situation, as will increment.

I have looked through the docs and I can't find a suitable generation strategy other than 'Assigned', in which case I will have to query the database myself to find the next available PK value - this seems inefficient and error prone (what happens if an insert occurrs between me querying the DB for a PK and using this value to insert my own record?)

Any advise much appreciated.

Hibernate version: 2.1.0.1001

Mapping documents:
public class CancellationMap: ClassMap<CancellationDTO>
{
public CancellationMap()
{
WithTable("Cancellation");
Id(x => x.CANCELLATION_REF).SetGeneratorClass("????");
Map(x => x.CancellationName).TheColumnNameIs("Cancellation_Name");
}
}


Code between sessionFactory.openSession() and session.close():
Session.Save(new CancellationDTO { CancellationName = "Test" });

Make it dependent of the DB, using a sequence.

Gonzao,

I am working with SQL Server 2000 which unfortunately does not work with sequences. I tried using sequence but got the error 'could not instantiate id generator' - 'Dialect does not support sequences'

Can you create a custom table for generating ID's?

In that case, just use the new org.hibernate.id.MultipleHiLoPerTableGenerator
detailed in the documentation (5.1.7 and following)

It should take care of concurrency problems, avoiding a stiff performance penalty at the same time.