Native ID generator not reset with Transaction Rollback

Question

I am using a Firebird database and NHibernate for object mapping. I have a table called Product with the typical fields like ID and ProductName, and I'm using Firebird's native ID generator to auto-generate the ID for me. When I was doing some testing, I had some code similar to the below:

session.Save(product);

try
{
    transaction.Commit();
}
catch
{
    //couldn't save
    transaction.Rollback();
}
finally
{
    session.Close();
}

The idea was to try and save the product, and if it didn't work, rollback the changes. When I tested this, the record was indeed not saved, but the native ID generator had been changed. For example, the generator was supposed to start at value 1. When I tried to add a 'bad' record using the code above, the record wouldn't save but the generator would jump to value 2. So, is there a way for me to also rollback any changes to the ID generator?

Thanks in advance.

Almost all databases with such generators behave the same. Otherwise, you'd have a massive convoy, and any later transaction which uses the generator cannot commit (or maybe cannot even progress past the point of asking for a number) until all earlier transactions have committed. Be *very* sure that you actually want this before trying to achieve it. Alternatively, accept that gaps may exist in the sequence. — Damien_The_Unbeliever, Jan 04 '14 at 09:06
Also, if you actually care about the numeric values of such auto-generated values, you're probably doing something wrong. They should be treated as opaque blobs that you happen to be able to store in numeric columns. They should certainly never be exposed to the users of your system, so what "value" they actually have should be irrelevant. — Damien_The_Unbeliever, Jan 04 '14 at 09:16
@Damien_The_Unbeliever I see. I thought that the DB would only use the generator if the SQL Insert was executed without errors, and therefore the gap is caused by NHibernate. I must have assumed wrongly. When is the generator used, then? Is it during session.Save? — JW Lim, Jan 04 '14 at 09:20
@JWLim The generator is used when the insert is executed in a `BEFORE INSERT` trigger; on transaction commit wouldn't make sense because it would be impossible to use the result of an insert (eg the generated id) in another statement within that same transaction. — Mark Rotteveel, Jan 04 '14 at 10:35

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

This is not the behaviour of the NHibernate, but DB Engine.

Please, check this question: SQL Identity (autonumber) is Incremented Even with a Transaction Rollback

For testing purposes you can use different generators, e.g. Increment, which are managed by NHiberante and after rollback and restart ... the value is reset. Check 5.1.4.1. generator, an extract:

increment

generates identifiers of any integral type that are unique only when no other process is inserting data into the same table. Do not use in a cluster.

Native ID generator not reset with Transaction Rollback

1 Answers1