NHibernate Hi/Lo - gaps in ids

Question

Scenario: Hi/Lo is initialized for MyEntity with Lo 100

The table is empty.

Two sessions with different connections have both inserted three items.

TableIds

1
2
3
100
101
102

If a third one comes in at a later time and inserts three items:

TableIds

...
200
201
202

Is there a way to not get these gaps?

Answer 1

As Phill quite rightly pointed out the gaps appear when you build a sessionFactory e.g. the application restarts etc or you are running on the cloud and each node has its own set of Hi and lo.

You could reduce the lo to 10 rather than 100 to reduce the gaps and/or I recommend using int64 rather than int32 for ease of mind.

However does the gaps actually matter? Would you ever see yourself running out?

I have never read anything negative about performance (database issues) with a large number of gaps when using Hilo. The only thing I have seen is that some people complain that they run out when using int32 or when the lo has been set to high.

Answer 2

The Lo value is stored against the SessionFactory, not the Session. You only ever get gaps when you restart your application and create a new instance of the SessionFactory.

A new Hi value is pulled from the database and stored in the SessionFactory, so if you have a web farm, each site will have it's own instance of the SessionFactory which will each have it's own 'next' Hi value from the database. When it runs out of Lo values it will update the Hi to the next available Hi from the database.

Edit:

If you have client applications then I would recommend not using HiLo at all, instead use a GuidComb, it's a sequential Guid and you wont have an issue with gaps.

Since it's an existing app however and you can't really go changing the identifier, I would recommend requiring the client applications to insert via a Web Service which has it's own SF, that way you can maintain a single Hi instead of multiple Hi's per client app.

If you can't do that then you're going to have to lower your Lo.