After I issue a COMMIT WRITE NOWAIT in Oracle, how can I find out when the transaction has become durable?
+2
A:
I'm assuming that you're asking about the new (in 10.2) asynchronous commit operation
COMMIT WRITE BATCH NOWAIT;
versus the old commit operation (in the new long-form syntax)
COMMIT WRITE IMMEDIATE WAIT;
If you specify WAIT, the transaction is durable when the commit operation returns. If you specify NOWAIT, you won't get any notification that LGWR has actually written your data to disk. At the next log switch, assuming you know the current SCN, V$LOG should verify that the data is actually on disk since you'll have a log group with a FIRST_CHANGE# greater than the SCN of your transaction, but that's not useful if you're looking for a more immediate notification. You could run a trace to see when the data is actually written as well, but that wouldn't be useful from an application.
If you are looking for a more or less immediate notification that your transaction has actually been written to disk when using asynchronous commits, can you explain the business problem you're trying to solve? Perhaps there is an alternate way of accomplishing whatever goal you have.
Justin Cave
2010-09-03 14:29:52
@Justin. I like not to unnecessaily lose time to waiting for synchronous commits. I have an event dispatching framework which notifys other processes of commited changes. Naturally, I want to delay that notification until the commit has become durable.
Peter G.
2010-09-03 15:30:54
@Justin, as I think about it, both IMMEDIATE and BATCH could be useful is this setting.
Peter G.
2010-09-03 15:41:59
@Justin, thanks for answering. After reflecting for a while, I now think should be happy with the four available commit write options. Anything else is asking for trouble in a production system.
Peter G.
2010-09-14 07:25:46