RE: Asynchronous commit - wait or no wait 10GR2

Very well said Nigel.  

I am concerned that it is possible to 'lose' a transaction after a commit has been issued (and returned), but before it was written to disk.

It seems to me to be a risk that has been accepted if you code "commit immediate nowait". However if you use "commit" then you assume that a wait instruction has been given to Oracle as the default.  

In my testing I see the numbers in the test table growing as each row is committed. However I also see a big difference in timings between "commit" and "commit write immediate wait" (both called by a PL/SQL proc). Where exactly is the time saving made?  

From: Nigel Thomas [mailto:nigel_cl_thomas_at_yahoo.com] Sent: 04 October 2007 17:51
To: jobmiller_at_yahoo.com; John Hallas; oracle-l_at_freelists.org Subject: Re: Asynchronous commit - wait or no wait 10GR2  

The thinking behind PL/SQL's long standing behaviour is that there is no real benefit in upgrading from CWINW to CWIW because

1. if the system goes down after a commit but before the commit is written, the database will still be restored in a consistent state (just a commit or two behind what your batch pl/sql might have expected
2. if the process itself is lost, there is no effective difference

NOTE that when you commit within a batch you need to handle the possibility of restart - REGARDLESS of whether you WAIT or NOWAIT. You pay a (small) restart penalty for NW as you are likely to have to reprocess an extra slice or two of your data. The size of the penalty depends on the size of the slice (the bigger the slice, the statistically less likely it is that your failure will occur in the short period between CWINW and the physical write).  

Obviously the fly in the ointment would be if you communicated (eg how far you had got) with another system via a mechanism outside the Oracle transaction:  

LOOP    PROCESS_A_SLICE;    CWINW;    SEND_NON_TRANSACTIONAL_MESSAGE; END LOOP;   For example, you could successfully send a message to a file, unix pipe, or DBMS_PIPE to tell another process to get on with downstream processing of invoice 101, but then the database fails before the write completes and so is restored back to invoice 100; you then find yourself sending the same message again when you restart your batch and/or the downstream process could find that invoice 101 was unexpectedly missing. In those cases, you would have to consider downstream duplicate detection etc.  

Anyone want to consider what effects might be on processing messages via (a) AQ and (b) other transactional message services? Any cases where messages could be duplicated?  

Regards Nigel

Where line xxxxxxxx can be commit write immediate wait;, commit write immediate nowait; , commit; The first and last should be identical and be the Oracle default. My parameter commit_write is not set. I am truncating the table after each run.  

Timings  

CWIW 5000 records 57 secs

CWINW 5000 records 3 seconds

Commit 5000 records 3 seconds    

BJSS Limited, 1st Floor Coronet House, Queen Street, Leeds LS1 2TW. Registered in England with company number 2777575. http://www.bjss.co.uk

--
http://www.freelists.org/webpage/oracle-l