Set transaction read only

By default Oracle guarantees statement-level read consistency, but not transaction-level read consistency. If we want transaction-level read consistency then we have to use "read only transaction"

I came across this statement today, and experimented on it, below is the test case about this statement.

Many of us know that when  user1 updated a field in a table and does not commit. User2 when query on the same table, would see the values of the table before updated (old values) where as user1 would see new values even though it is not commited.

ex: table1

name class
x     1
y     2
z     3

user1: update table1 set class=4 where class=3;
1 row updated

user2: select * from user1.table1;

name class
x     1
y     2
z     3

user1: select * from table1;

name class
x     1
y     2
z     4

Once user1 issues commit statement then user2 will see the updated table.

User1: commit;
User2: select * from user1.table1;

name class
x     1
y     2
z     4

above these things are known by many of us already.

Now lets see what  "Set transaction read only" will do
--------------------------------------------------

we will take the same table
ex: table1


name class
x     1
y     2
z     3

user1: update table1 set class=4 where class=3;
1 row updated

user2: set transaction read only;
user2: select * from user1.table1;

name class
x     1
y     2
z     3


earlier when user1 issues commit statement then user2 will see the updated table. where as in this case user2 will see the consistent copy of table prior updated by the user1.

User1: commit;
User1: select * from table1;

name class
x     1
y     2
z     4


User2: select * from table1;

name class
x     1
y     2
z     3

A COMMIT, ROLLBACK, or DDL statement terminates the read-only transaction.