Re: write consistency/read consistency

One (heavy handed) approach is to SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
from the 9iR2 docs:
Use the ISOLATION LEVEL clause to specify how transactions containing database 
modifications are handled.
 
The SERIALIAZBLE (sic) setting specifies serializable transaction isolation 
mode as defined in the SQL92 standard. If a serializable transaction contains 
data manipulation language (DML) that attempts to update any resource that may 
have been updated in a transaction uncommitted at the start of the serializable 
transaction, then the DML statement fails.
This should prevent the data corruption risk in your example, but at the cost 
of failing the whole update. And the setting has to apply for the whole 
transaction - you can't switch between this and the default READ COMMITTED. 
 
I guess you keep resubmitting the SQL until it works - which will certainly 
help to serialize your application, as the more retries, the more the chances 
of a conflict (vicious spiral time).
 
Regards Nigel

Other related posts: