Hi,

I've got a question relating to NHibernate's 1.2.0GA transaction handling.

I've encountered the following issue:
I have a table and an object that that is mapped on to it using NHibernate. The promary key for the table is generated using increment generator.

<id name="Id" type="Int64" column="Id">
<generator class="increment" />
</id>

Now there is an ocasional time when i need to delete all of the rows from the table and load a new set of values into it. I need this to be done as part of a single transation - so incase something goes wrong the original values are retained.

First i loop though all of the current object in the table deleting them. Then i call a Flush() method on the session object. Then when it comes to the code that saves the rist new object to the database - a timeout exception occurs. The exception it self comes from the method that attempts to generate the new Id for the object.

Anyhow, code is below:
public static void BatchLoad(IList<string[]> values, Lookup look)
{
ISession session = StaticReference.SessionFactory.OpenSession();
ITransaction tran = null;
try
{
tran = session.BeginTransaction();
IList<LookupValue> valuesToDelete = GetLookupValues(look);
foreach (LookupValue val in valuesToDelete)
{
session.Delete(val);
}
session.Flush();

foreach (string[] arr in values)
{
LookupValue newLookup = new LookupValue();
newLookup.DateActiveFrom = Convert.ToDateTime("1/1/2007");
newLookup.DateActiveTo = Convert.ToDateTime("1/1/2100");
newLookup.FromValue = arr[0];
newLookup.IsActive = true;
newLookup.Lookup = look;
newLookup.ToValue = arr[1];

session.Save(newLookup); // Timeout error occurs on this line.
session.Flush();
}

tran.Commit();
}
catch (System.Exception ex)
{
if (tran != null) tran.Rollback();
throw;
}
finally
{
session.Flush();
session.Close();
}
}

As always, any help will be greatly appreciated.

Thanks in advance,
Nick Goloborodko

The ID generator "increment" does a SELECT MAX(ID) on your table. When you do the deletes, part of your table is locked in the transaction. The ID generator is then waiting for the transaction to complete, thus cause the timeout.

You may be able to work around it if you can somehow do the insert before the deletes.