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>