2

My Java application uses Hibernate and H2.

It process music files within folders. Typically for a folder it would read the files in the folder and store each file as a Song class in database, then later on it would add additional data to those Song objects.

The application is multi-threaded so therefore multiple folders can be processed at the same, and hence there can be multiple reads/write request to the database.

Can't remember exact db terminoloy but I assume that H2 will get a SHARE lock when querying the database, and needs a WRITE lock to add new rows the table, which it cannot do whilst there is a SHARE lock on a table.

Profiling application has shown quite alot of the threads can spend time blocked waiting on db.

But here is the thing, although it is concurrent Thread A will never work on the same rows as Thread B. e.g Thread A will work on songs 1,2,3 and Thread B will work on 4,5,6. So knowing this is there anything I can do to allow Thread A to write to SONG db at same time as Thread B is reading since there is no risk of Thread A modifying any records that Thread B is read/writing and vice versa.

Im simplifing this slightly, Im using Executors and whilst FolderA will be initially processed by Executor1, it may then be passed onto Executor2 and Executor3. But it still remains that two executors will never be working on the same rows.

Executor1 mainly loads files, and modification is deferred to Executor2. Executor1 was originally multithreaded but I have since changed to a singlethreaded executor because with multiplethreads there was no inprovements in performance, the threads were just blocking. The other executors are multithreaded since they don't only have database work to do.

  • Have you identified an actual performance problem by measuring? – Robert Harvey Jun 13 at 14:25
  • Yes, initially my application was by default utilizing all cpus and I found on machines with more cores it was hanging because so many threads were blocking trying to read/write to database. I then did further profiling using Yourkit that confirmed the issue. – Paul Taylor Jun 13 at 14:40
  • Well, the last paragraph of your answer gives me pause. It's not the reading that's the problem, it's the writing. In SQL Server, we have a clause called NOLOCK that allows reading while writing; it's use is discouraged, but it has significant performance benefits if you don't mind things like partially-written records or duplicates. – Robert Harvey Jun 13 at 14:42
  • Is this happening because you have thousands of concurrent users, or just two? – Robert Harvey Jun 13 at 14:43
  • I only have one user, however the application uses multiple threads to process the folders as quick as possible, there are various webservices in use and file i/o so if it was a singlenthread it would be much slower as the the thread would be spending alot of time waiting rather than doing anything – Paul Taylor Jun 13 at 14:56
2

I assume that H2 will get a SHARE lock when querying the database, and needs a WRITE lock to add new rows the table, which it cannot do whilst there is a SHARE lock on a table.

That is only true if you're using the older PageStore engine.

If you are using anything more recent than H2 version 1.4 then MVStore is the default engine. That allows concurrent reads and writes. A reader will see the last-committed value if it reads while an update transaction is taking place. The update transaction won't be blocked by readers - assuming you have set your transaction isolation level correctly at read-committed.

How many threads do you have writing to the database? If you have more than 1 thread trying to write at the same time then you will get waits due to serialisation - simply because the underlying IO code that is writing those changes to disk has to serialise the work internally. (To be more accurate, I think H2 serialises changes as they are applied in-memory to the page-cache, and physical IO writes are from snapshots of the page cache so they can be done concurrently).

I would say it's unlikely that Hibernate is the issue, but to narrow down your performance problems and test your architecture, I would recommend writing a really simple test program with a number of threads just doing very simple plain JDBC reads and writes. Try varying the number of reader and writer threads and see what difference it makes.

If possible, try a simple work-queue architecture where you have several threads reading, but only one thread writing. The readers read the data and do some work, then post a data object onto a queue for writing. The single writer thread then just takes each data object off the queue and writes the changes to the database. That should give you the best write throughput.

  • I was using H2 1.3, I have just moved to 1.4 and that does seem to have reduced the amount of blocking I see, but i dont know about this bit ' assuming you have set your transaction isolation level correctly at read-committed.' - how do I check/chnage that – Paul Taylor Jun 14 at 8:30
  • If you haven't explicitly set anything else, then read-committed should be the default. The Hibernate configuration property name is "hibernate.connection.isolation" to set anything different. Also make sure you have set the H2 dialect in Hibernate. – David Jun 14 at 8:36
  • Couple of other performance points - auto-commit should default to off, but it might be worth making sure that is explicitly configured ("hibernate.connection.autocommit"). Also try setting transactions to read-only if you can. – David Jun 14 at 8:38
  • One final idea - check what your connection pool is doing and how it is configured. I built a desktop Java application that uses Hibernate and H2 and built a custom Hibernate connection provider - the custom code opens connections, and hands them to Hibernate (and holds open connections in a list when Hibernate hands them back). It doesn't need all the complications of a connection pool for a shared DB like Oracle, so is very basic and very fast. – David Jun 14 at 8:41
  • So that would be better than using c3p0, can you share it ? – Paul Taylor Jun 14 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.