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.