Re: write consistency/read consistency
- From: Nigel Thomas <nigel@xxxxxxxxxxxxxx>
- To: henry@xxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 27 Jun 2006 02:39:15 -0700 (PDT)
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:
