Hi,

This may seem like a dumb question, I'm fairly new to this stuff.

We are designing a database and will be using Hibernate to manage persistance. At certain points within the application, we will be persisting an object model to the db. This will consist of a three level parent/child model and the number of records would be around 500. It has been suggested to us that persisting 500 new records in one transaction is risky from a database perspective and it would be better to batch the data into smaller bundles. To do this we would need to have an extra column on the database to allow rollback. ie while inserting the rows, each row would have a certain state (eg created) and then when all rows where persisted they would all be updated to indicate they have all been saved (eg with a state of saved). So if something went wrong during persistance, all rows with the 'created' state would be deleted which would be equivalent to a rollback if this were a single transaction.

My questions are:

1) Is this necessary or is the single transation fine?
2) Does Hibernate do any of this type of management for us? ie Does it split up large inserts into batches? If it does or can how does it handle rollback?
3) Does anyone have any suggestions how to best handle this?

Thanks. Any help is much appreciated.

Regards.
Doyley.

Who has suggested this and what is their rationale?

On the face of it, it sounds completely nutty.

You realize that SQL databases are regularly used to process millions of records, right?

It was suggested by a consultant, shouldn't say where from. There rationalle is that persisting that many records in one commit can be risky from a db perspective, not sure why.

I agree it doesn't seem that big an issue, I'm sure I've done similar things in the past without any issue. Be aware that it is 500 inserts in one transaction, so I guess that is quite a few but I wouldn't think it would introduce too much risk for the db. It's an Oracle db.

So are you suggesting that breaking it up into smaller batches is a waste of time?

I'm suggesting that you should probably consider sacking your consultant.

(Unless there is some other key fact you are not telling us, like, each insert requires 60seconds of other processing or something.)