In a discussion with a DBA today, the topic of commit block sizes and how tuning the commit block size can have an impact on the disk usage of a database and potentially the performance of the database came up.

Bearing in mind that I'm not a DBA, and this particular issue is somewhat new to me, I get the sense that one might tune this by committing, say, on every tenth business transaction instead of every one transaction. That sounds to me as if there's a serious potential for data loss, but that was countered with the idea of savepoints, and that commit blocks in combination with savepoints might allow for significant tuning capabilities.

This came up in the context of talking about Hibernate, and I wasn't able to address how one might be able to do that sort of tuning within the strictures of Hibernate. A search through the documentation and forums doesn't seem to talk about it either, so -- thoughts?

Does Hibernate allow for anything of this nature, and if so, how? To a certain extent, I could imagine that commit blocks are supported simply by not telling Hibernate to commit immediately, but without the concept of savepoints, it seems as if that would mean that a rollback on the ninth business transaction (assuming a commit block size of ten) would roll back the previous eight.

Is this making sense to anyone? I tried to do some preliminary research on the subject before posting, but if I'm making some fundamental missteps here on what the DBA was talking about, please feel free to correct me.

This does not make real sense to me.

First, AFAIK savepoint support is very new in the JDBC standard, and therefore most likely supported very little by JDBC drivers.

Second, I can't really imagine doing some manual handling of savepoints will reduce db load very significantly. In effect, instead of handling the transactions, the database now will have to handle the savepoints. I am not sure this reduces the load much.

So in effect, you can of course control transaction handling within the Hibernate Transaction API, but there is no savepoint concept. You can however still drop the Transaction API and do manual transaction handling on the JDBC connection, if it supports savepoints (which i am quite sure not many drivers do yet)

I've gotta admit, I don't see why savepoints would be /enough/ better with respect to performance and/or resource usage to warrant particular consideration, but it seemed as if this was important to a few people here, so I decided I would be better off to be armed with good information on the subject so as not to end up giving the impression that Hibernate would 'get in the way'.

I wasn't able to quickly ascertain which databases and JDBC drivers support savepoints, but the initial impression I had was that it was supported by current DB/2 and Oracle databases and drivers. We'll see. Although obviously using a cross-database technology and floating that on top of a technology that is new and not particularly cross-database has its own concerns. ;)

Thanks for the information, though -- I've got more to go on. I hadn't considered the fact that Hibernate could be used on top of the existing JDBC transaction infrastructure if need be.

I gotta say, I also can't see how this could possibly be more efficient. If such a strategy were efficient, why would the database itself not perform that optimization?


Anyway, it is possible in Hibernate, you can supply your own JDBC connection and control transactions at the level of JDBC. Hibernate doesn't need to be involved.

But don't do this. Its silly.

I personally believe that savepoints have a very valid place in the architecture. We've used them in our unit tests to control the state of the data and are looking at enhancing Hibernate to support for our DB2 environment.

The first assumption is that savepoints are much lighter weight than commits. The second assumption is that Hibernate is useful for batch, which we're doing. The third assumption is that for high performance in batch you raise your commit intervals to a high value (e.g. 100 or more). Now, in batch you need to handle skip logic when you hit a bad record. Before savepoints you had to rollback to the beginning of the transaction and proceed over records that have already been processed. You skip the offending record (e.g. by sending it to an suspense output source) and proceed forward. Savepoints allow you to rollback to the previous record instead of the beginning of the transaction demarction.

Savepoints could be very useful. We'll know shortly as we need to at least try the option for our batch environment.