COMMIT WRITE BATCH NOWAIT in Hibernate

Question

Is it possible to execute COMMIT WRITE BATCH NOWAIT in Hibernate?

Answer 1

I didn't search extensively but I couldn't find any evidence that you can access this functionality at the JDBC driver level.

And this leaves you with the option to specify the COMMIT_WRITE parameter at the instance or session level, if this makes sense for you.

Just in case, let me quote this blog post (I'm pasting the content for reference because the original site is either unavailable or dead and I had to use Google Cache):

Using "Commit Write Batch Nowait" from within JDBC

Anyone who has used the new asynchronous commit feature of Oracle 10.2 will be aware that it's very useful for transaction processing systems that would traditionally be bound by log_file_sync wait events.

COMMIT WRITE BATCH NOWAIT is faster because it doesn't wait for a message assuring it that the transaction is safely in the redo log - instead it assumes it will make it. This nearly eliminates log_file_sync events. It also arguably undermines the whole purpose of commit, but there are many situations where the loss of a particular transaction (say to delete a completed session) is perfectly survivable and far more preferable than being unable to serve incoming requests because all your connections are busy with log_file_sync wait events.

The problem anyone using Oracle's JDBC driver is that neither the 10.2 or 11.1 drivers have any extensions which allow you to access this functionality easily - while Oracle have lots of vendor specific extensions for all sorts of things support for async commit is missing.

This means you can:

Turn on async commit at the instance level by messing with the COMMIT_WRITE init.ora parameter. There's a really good chance this will get you fired, as throughout the entire system COMMIT will be asynchronous. While we think this is insane for production systems there are times where setting it on a development box makes sense, as if you are 80% log file sync bound setting COMMIT_WRITE to COMMIT WRITE BATCH NOWAIT will allow you to see what problems you face if you can somehow fix your current ones.

Change COMMIT_WRITE at the session level. This isn't as dangerous as doing it system wide but it's hard to see it being viable for a real world system with transactions people care about.

Prepare and use a PL/SQL block that goes "BEGIN COMMIT WRITE BATCH NOWAIT; END". This is safer than the first two ideas but still involves a network round trip.

Wrap your statement in an anonymous block with an asynchronous commit. This is the best approach we've seen. Your code will look something like this:

BEGIN

--

insert into generic_table

(a_col, another_col, yet_another_col)

values

(?,?,?);

--

COMMIT WRITE BATCH NOWAIT;

--

END;