Hi
We have tables in our database that are shared across many platforms. Our team does not own them so I cannot make changes to their structure (include a counter) so I am bound by an existing business rule that whenever I wish to add a new row I must take the maximum id found in the table at the index row and add one to it (just like an auto-number field). The problem is if I wish to do a number of inserts inside a transaction and there is a potential for concurrent users I need to track what is the current maximum id number to correctly add 1 to it. Unfortunately I have found nHibernate to time out the second time I try to obtain the max number because of the transaction. If I don't use a transaction it works fine so that is my basis for deducing that the transaction is what is locking out the read. Using straight SQL with ADO.Net I can query DB2 to get the information using something called a "dirty read" which reads the table as it is at the time of request regardless of locks by appending a "with ur" tag to the end of it. This seemingly doesn't work with HQL so I was wondering how one gets around multiple requests to a given table inside a transaction? So far I am using nHibernate as well as ADO.Net in concert but would prefer a cleaner solution.

Thanks,
Robin

Why would you get the max number more than once. That will be pretty inefficient... Can't you do something like:

(psuedo code)

Being Transaction

Get Max number from Table into NextId

While
NextId ++
Populate ID of object
Loop

Save Objects

End Transaction

The reason is because the table is shared so we cannot assume that the next available number will be plus one of the previous.

For example user a gets the max number of 10 and has 100 rows to process. If you use a counter that adds 100 to 10 you would fine if you were the only person writing. But say user b requests the max number while user a is writing in the transaction and also gets 10 as the max number and writes using the same first number and counter you receive duplicate key errors and transactions that roll back.

Barring an nHibernate solution, the only thing we have found is that we could single thread the class or use ADO .Net as well as NHibernate. My question was if there is a way inside of NHIbernate because we can't be the only company with a few badly designed tables.

Thanks

Um... yes you can. That's why you wrap it in a transaction, including the read of the max number. As long as you are using REPEATABLE READ or SERIALIZABLE transaction isolation this should work.

The other apps that share this table must do the same thing? Even if a single ap used this table since it would have multi-users.

BOb

We run Cobol jobs throughout the day on these systems. The locking on the table isn't absoute since we cannot hold up jobs that need to read from a table while another process writes to the table hence the "with ur" postfix on the SQL strings. It gives the requester the state of the table before the transaction is committed or rolled back. Anything that might have been flushed using nHibernate or added from another app but not committed will get picked up. It won't cut down on all collisions based on key but what it does do is that if a job also wants to write to the table it doesn't collect the same max id number that the job that has the page locked is using and incrementing which would generate a bunch of duplicate key errors. It is nothing an auto-number field wouldn't have fixed but that isn't an option.

I'm actually thinking about single threading this as a service and not even worrying about the mainframe side. Thanks.