Re: Some experience or benefit using DBMS_TRANSACTION.READ_ONLY frequently

The benefit of this statement is that your whole transactions see the database state as it was in the start of the transaction. Lets say you have invoice table and invoice items (items) table and in each invoice you have the number of items in this invoice. You do select from invoice and get a record that say: I have three items in this invoice.
While you process the record from invoice someone add another item to items for this invoice.
Now your program does select count (*) from items where invoice number = 18. You get 4 items and you report the invoice as wrong.

With DBMS_TRANSACTION.READ_ONLY keeps the SCN from the start of the program and provide read consistency to this SCN, so in the case above you will get only three records and report the invoice as OK.

You need to remember:
1) This must be the first statement in you transaction. 2) You might get snapshot too old error.

Information provided by Eyal Robin from 2TRAIN4 company in Israel. We had a talk a few days ago and did some tracing on work done by application server and this statement popped up. I asked him what is was and he was kind enough to explain. It seems that the application server issue set transaction statement at the start of each transaction.

Yechiel Adar
Mehish
----- Original Message -----

From: "Juan Carlos Reyes Pacheco" <jreyes_at_dazasoftware.com> To: <oracle-l_at_freelists.org>
Sent: Wednesday, October 20, 2004 12:10 AM Subject: Some experience or benefit using DBMS_TRANSACTION.READ_ONLY frequently

--

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 20 2004 - 09:17:26 CDT