Friday, April 05, 2013

What it Does (SET TRANSACTION READ ONLY - Part 4)

This was tested on Oracle 11.2. So far, I have not explained what SET TRANSACTION READ ONLY does. To illustrate this, I first need to show what happens normally, i.e. when you are not in a READ ONLY transaction. I created a table and added a row to it in session 1 below:
 
SQL> create table tab1 (col1 number);
 
Table created.
 
SQL> insert into tab1 values (1);
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> select * from tab1;
 
      COL1
----------
         1
 
SQL>
 
Then I went to session 2, updated COL1 to 2 and did a COMMIT:
 
SQL> select * from tab1;
 
      COL1
----------
         1
 
SQL> update tab1 set col1 = 2;
 
1 row updated.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from tab1;
 
      COL1
----------
         2
 
SQL>
 
I returned to session 1 and was able to see the new value of COL1:
 
SQL> select * from tab1;
 
      COL1
----------
         2
 
SQL>
 
If you are in a READ ONLY transaction, SELECT statements return data as it was when you ran the SET TRANSACTION statement. I tested this as follows. First I started a READ ONLY transaction and queried TAB1 in session 1:
 
SQL> set transaction read only;
 
Transaction set.
 
SQL> select * from tab1;
 
      COL1
----------
         2
 
SQL>
 
Then in session 2, I changed COL1 to 3:
 
SQL> update tab1 set col1 = 3;
 
1 row updated.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from tab1;
 
      COL1
----------
         3
 
SQL>
 
I returned to session 1 and queried TAB1 there. COL1 was still set to 2:
 
SQL> select * from tab1;
 
      COL1
----------
         2
 
SQL>

No comments: