Re: keep select in transaction

> I want to open a transaction in oracle (9i),
> where first I declare a transaction,
> I want that any select will not be affected, as the database changes
after I
> open the transaction.

It is not necessary to lock the entire table. You can accomplish what you want with a serializable transaction, using the SET TRANSACTION command:

http://oraclesvca2.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_106a.htm#2067252

This will do what you want. The only caveat is that if someone changes any data that you will try to change later yourself in this transaction, you will receive an ORA-08177 error. At that point you will want to roll back & re-try your serializable transaction until you succeed.

If you don't need to change data in this transaction, you can also accomplish the same thing using a read only transaction (same command & pages).

Greater detail can be found here:

http://oraclesvca2.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg08sql.htm#2655

-Steve Received on Mon Apr 11 2005 - 10:18:48 CDT