Hibernate version: 1.1

Name and version of the database you are using: SQL Server 2000

.NET version: 1.1


Hello everyone.
We're working now on an application developed by another company. The thing is HUGE so we are looking for solutions as broad as possible, since rewriting the entire app is out of question (at least for the customers, we would be happy ;) )

Everything worked fine until the number of users began to grow. Now the application gives Timeout and Deadlock errors, specially in some specific modules that are more intensive in their DB usage.

Changes we made so far: tone down the isolation level from Serializable to RepeateableRead, check the indexes on the DB, verify the code for the classical problem of accessing objects in different order.

We still have some concurrency problems, and I decided to register and ask for some guidance.
One idea we came up with was to modify the NHibernate transaction so that in the event of a deadlock it would retry automatically. I dont know how complicated can that get, but is a solution that would require us to modify just one place in our code instead of different modules.

Is it a good idea? a bad idea? any other things we might be missing?

Thanks in advance,
Sebastian.

How critical is RepeatableRead for you? Can you reduce the isolation level to ReadCommitted?

It is very important to guarantee reads for us.

One example: Several users can work at the same time on the same module, and although they work with different "users" (rows on table A), there is also a final update on a "store" (single row on table B).

So, different people begin working at the same time without interference, however in the final step of the transactin BOTH update a single value.

Can optimistic locking help you here? If different sessions from different users are hitting different rows in the same A table, then those shouldn't interfere with each other. When different sessions from different users need to update the same row in the B table, if you either don't select that B entity until you need to update it or you call Refresh() on it just before you update, flush and commit it, then the window of contention should be small. Optimistic locking will then take care of the infrequent cases where someone beats you to updating the same row between the short time you read it and tried to update it.

We don't have to deal with data contention problems much in our product, so I may be off base here, but I think this is all you need to do. Optmistic locking should allow for good performance, and you should only have to resort to pessemistic locking after you have a good amount of hard evidence that optimistic locking is causing too many people to "lose" in their update attempts and have to retry. In neither case will you damage your persistent data.

First of all, I want to thank you for taking the time to help.

We are already selecting the row (getting the object) right before the update. I'll check the documentation for more details about the use of Refresh().
I'll look with more detail the optimistic locking chapter of the reference too. Looks like I have a config problem that won't require modifying hibernate after all.
The thing is, I've been reading so much documentation over the last few
days, and this final issue seemed like impossible to resolve.