Hibernate version: 3.3.1 GA

Name and version of the database you are using: postgres 8

Hey,

I'd like to customise my session factory scope - currently it is as per usual, it is bound to the current thread, meaning any transaction on the thread will use the same sessionfactory/session. usually this works just fine however...

my current project does 2 things concurrently:

1. selects a massive amount of data in blocks of 50000 in transaction a
2. saves massive amounts of data in blocks of 1 in transaction b

so what we'd see happen can be described as follows:

transaction a select, commit
transaction b insert, commit
transaction b insert, commit
transaction b insert, commit
transaction b insert, commit
.... * 1000
repeat

each of these must be contained by it's own transaction because they are done on the same thread and cannot overlap.

what i'd like to do is to have all transaction a's occur in session a, and all transaction b's occur in session b so that i can batch save a multitude of b's all at once, cutting my number of transactions down by hundreds or thousands.

i thought i might be able to change the scope of the session/sessionfactory to be bound to 2 different classes perhaps.

advice appreciated.

p.

i've hacked a temporary solution where i batch the inserts - basically i hold on to the transaction for thousands of inserts and only commit it when i reach a configured upper limit or have to do another select.

it's sped up my process by a factor of 5 - what took 10 minutes now takes 2. i am going through roughly 1/1000 of the transactions i was previously.

but it's a hack because i'm having to keep track of the transaction in places that shouldn't know there's a transaction to keep track of - breaks my designed separation and mvc model to some extent.

i could alternatively only commit at the end of the thread, however having 1 million or so uncommitted commands in the one transaction seems like a bad idea. is there a rough guide to how many commands a single transaction can handle? is it db specific?

If there is a limit it is db-specific. We are using MySQL with InnoDb and have had transactions that inserted 100+ million rows. The whole operation involved a lot of number crunching as well and took several hours. We had to turn off the binary log because it has a limit of 4GB/transaction and we hit the limit in one of the tests.

We are using PostgreSQL also a little, but have never tried it with that large data sets.

hmm .. tried it (one tx for whole process) and ran into memory errors.

Oh... I should have mentioned that we are using a StatlessSession combined with plain JDBC. With a regular Hibernate Session the memory runs out after a few hundred thousands items (with 1Gb assigned).

thanks, i'm now using a stateless session with a single transaction. it's much tidier because i don't need to keep track of the transaction in more than one place. thanks for the advice.