суббота, 2 января 2010 г.

ORM-style Identity generator or impossible is nothing.

What the hell?! Isn't it a bit like even odds? Everybody knows that it is impossible to prevent NHibernate from breaking Unit of Work semantics, when your primary keys are generated by SQL Server Identity facility. To justify that, please read the following: generators revealed, and follow on with this story.

In short, the major problem of Identity generator is that it is a post insert generator, that is to say it can't produce new Id value without INSERT statement being executed by database engine. Furthermore NHibernate can't persist entity
without Id. That is why NHibernate will insert our object to the database the moment it was persisted without waiting for Flush() to be called. This causes huge problems should you try to implement long-running business transactions. The biggest trouble is that you won't be able to rollback changes made in business transaction.

In short, this is what I want to achieve:

  • insert values into Identity fields on my own;
  • acqiure correct Identity values that wouldn't cause troubles in database;
UPD: As it was fairly noticed, it looks like I work very hard to get something that is already in the box (HiLo). It really looks like that, but I have to prove that is not a waste of time. The point is that my case is constrained with existing database with defined schema. This database is filled with loads of records by other processes that I have no access to. That is why I can't use solutions like HiLo and have to invent my own.

In SQL Server one can disable autoincrement for Identity field and
insert its own values. But how would he get a correct value? The easiest way is to insert a dummy row in a local transaction and then perform a rollback. After transaction was cancelled, Identity value won't be reverted. Thus, by changing a table and reverting all changes made, we produce a gap in an Identity sequence, and the gap value can be inserted manually afterwards.

Having said that, I've drawn a following sequence diagram:

The solution, as it comes from a diagram above, consists of 3 parts:

  • Custom Id generator, which I've called ORMIdentityGenerator;
  • PreInsert listener, to disable Identity field autoincrement;
  • PostInsert listener, to enable Identity field autoincrement back;


This is an easiest possible implementation of what I was talking before. Agile principles say KISS - keep it simple, stupid! and that is what I do. This generator is merely an INSERT statement executor. One can configure it with the INSERT statement to perform safe (in terms of database integrity) operation that will change a table and force database to produce a new Identity value. This value will be returned with a next SELECT statement execution



That's it. Now I can edit my persistent objects with Id field generated by Identity in
a long-running session and rollback changes should I need it.

Good luck!