Hello,
I have got problems with occuring database deadlocks in my application while it's under heavy load. I discovered this fact while doing load tests with the tool "The Grinder".

Our configuration is as follows:
- Hibernate 3.2.1ga
- Oracle 10g
- Jboss 4.0.1SP1

I searched this forum about topics which treat similar problems and found many different "very technical" topics.
What I really need, and I think, this would help a lot of other people, is the right concept, to handle the following situation correct with hibernate:

The scenario is (in an abstract form) as follows:
Table Hotel includes several accomodations.
Table Days includes a row for every day in a season for every Hotel. A Day has many different attributes. To keep it simple, lets say it has just one attribute "Free" which represents the number of free rooms in a Hotel at a specific day.

e.g.
- Hotel Sunshine:
- Day 01.01.2008 --> Free: 10
- Day 02.01.2008 --> Free: 15
- Day 03.01.2008 --> Free: 10
- Day etc.

If I want to book a room in a hotel for a specific timeperiod (e.g. one week), I select the corresponding days and update their attribute Free afterwards (Free = Free - 1). If everything was alright (enough free rooms available) I go on with the booking which will be persisted later. The booking runs within one transaction, to do a complete rollback when an exception occurs. The processing of the days is just a very small part of it.

Now...in my load test, I let many threads book the same hotels for the same periods concurrently. And there appears my problem. I tried many different ways to select and update the rows in table Days, but found no way to do this without errors.
My best solution at the moment is to use pessimistic locking, what means that I do a select for update with a criteria query, lock the affected days and update them afterwards. The problem with this solution is, that the lock is held until the booking is commited. This method results in "just" 18 deadlocks while doing 1000 bookings with 4 threads. Optimistic locking results in "tons of" concurrent modification exceptions.

So it would be great if someone can give me a hint how such an issue should be solved with hibernate.

Many Thanks in advance!

Best regards,
Andreas

Hi,
I had the same exact problem. Let me ask you first: does the load-test really represent the expected behavior of the system? I mean, assuming there are hundreds of hotels, the "load" will be distributed across all hotels - not on a single hotel, so locking exceptions will become less propbable as the number of hotels increase. In general, fine tune The Grinder to simluate a more realistic scenario - maybe you'll discover that optimistic locking works for you. Don't start optimizing your application in areas where it's not needed.

If the load is expected, I can think of several suggestions, each has its pros and cons, of course:

1. work asyncronously: have the "booking request" go into some message queue, which will be processed and re-processed when locking exceptions occur, until the booking is complete. At that point, the user will be notified (email, alert, etc) that the booking is ok or was impossible due to a fully booked hotel.

2. research the impact of using 'SELECT FOR UPDATE'

3. during system deployment, ensure that the module that updates the "problematic areas" of the database has only a single instance, effectively serializing access to those areas. This is a "last resort" solution only and I don't think you will need it in your case.

good luck

Thanks for your answer!

I chose your second approach, and did a deeper research (logging session) of the SELECT FOR UPDATE statement. I found out that on oracle side,
it is possible to set a parameter on tables/indexes called INITRANS.
I am not absolutley sure about it's meaning, so i don't try to explain it... :-)
I set the value of this parameter to 100 for all indexes in the table / and on the table itself --> I get (hardly) no deadlocks any more.
Hardly, because in did a few tests with 1000 bookings/5 threads --> 5000 bookings alltogether and got in one of them 2 deadlock errors... :-)

Current possible solution:



Set Table and Index Initrans to 100:



Does anyone see a problem with this solution?

Hi,
I was wondering what is your answer to the first question: is your test scenario realistic? how many hotels did you setup during the 5000-bookings test?

Hi,
for the current customers of our software it's not really realistic.
The complete setup is: 100 Hotels, each will be booked 10 times with a different number of rooms/customers/travelperiods by 5 threads.

The aim of the load test is a comparison of the performance of our application on different application server platforms and of course to see it's limits/behaviour in a high load scenario.

It's nice to test theoretical limits, but I recommend you tune The Grinder to a more realistic, yet still high-load, scenario. Otherwise, you'll start researching areas that will divert you from the "critical path", i.e. getting your app ready to work in a realistic environment. For example, you may find that fine tuning your queries with SELECT FOR UPDATE and the like, may make it very difficult to support certain DBMS's, or use middleware like Hibernate, which move you away from the underlying SQL language.

If you still have time to fine tune performance, I predict that you can reduce collisions to a minimum by strengthening the hardware, the network, etc.

Personally, if a more realistic test shows almost no lockings, I would go with a simple synchronous approach that, in the worst case, retries the booking a few times, and reports back to the user either:
1. booking completed
2. hotel is full
3. system unavailable, try again later

thanks.

I have to revide my last post. The setting of INITRANS improved my current situation but didn't fix it.

The main cause of my problem was a bug in our current oracle version:



After execute the workaround statement, no deadlock appeard any more!
I tried with 50 parallel connections and got just connection timeouts, no deadlocks :-)

That's good news! I remind you that you must still use optimistic lockings to maintain data integrity and consistency, ON TOP of the 'select for update' implementation. Otherwise, your data may change in unexpected ways AND you may never get any exceptions about it!

good luck...