nHibernate Data Insert Problem / Mystery

Question

I've got a weird one here. I'm using nhibernate and my problem is that on larger data inserts no exceptions are thrown, there is no data in the table, BUT the identity keys are taken up. So when I manually insert the next record the identity key skips a few as if the data was imported and deleted?!?!

Here are some things to consider: - I'm fetching data from a web service so each item takes a while before it gets called the make persistent - Depending on the item it is either an insert or an update - I'm using a foreach to iterate through the fetched collection before checking to see if it's update or insert (ie. I try to populate an entity or create new instance and then call make persistent at the end.) - the code works as data does get inserted on smaller batches and is visible in the database. For imports that takes slightly longer then it still complete without any exceptions but there is no data visible but only clue is the identity key has been taken up by what would have been inserted and visible.

Can anyone explain what is going on here? As I'm not getting any exceptions I have no way to diagnose this, any help or suggestion much appreciated!

Answer 1

Off the top of my head I can't think of what might be happening. Do you have SQL profiler? If you do and you can consistently reproduce this issue running the profiler while it happens should clue you in to what is going on. If not you may be able to write insert/delete triggers to keep track of what is going on in the table.

Answer 2

What is your PK generation strategy? (Knowing that might help explain why the PKs are being used up.) Off the top of my head, it sounds like your transaction is timing out. Some ways around this...

• Enable update batching via adonet.batch_size in hibernate.cfg.xml assuming your database provider supports it. The SQL Server and Oracle providers definitely do. Many others do not.
• Fetch all the data from the web service and don't start inserting/updating your objects until you have all the data. This will help keep your db transaction shorter as you won't be waiting on the web service.
• Consider splitting one larger batch into multiple smaller batches if your business logic allows it. Since smaller batches are working, it might make sense to commit your transaction and start a new one every X items.

Answer 3

Primary keys with IDENTITY, and a rolled back transaction, would explain the missing keys. They were inserted and then removed again, either because a later error caused a rollback, or the transaction timed out as James suggests. This is discussed here.

Your real problem seems to be the silent error. Are you supressing exceptions with an empty catch? Could your catch be throwing an exception of its own? If you haven't already, I recommend adding log4net to your project with a simple file appender (sink). NHibernate writes out everything it does to log4net if its present (useful for debugging, but don't leave it on in production). Alternatively, as already suggested, you could profile your SQL.