Sunday, September 14, 2008

Understanding Read-Only Transaction

In the Oracle document, it stats:
"A read-only transaction does not acquire any additional data locks to provide transaction-level read consistency. The multi-version consistency model used for statement-level read consistency is used to provide transaction-level read consistency; all queries return information with respect to the system change number (SCN) determined when the read-only transaction begins. Because no data locks are acquired, other transactions can query and update data being queried concurrently by a read-only transaction."

To help understand the concept of read-only transaction, I did a small test:

In the session 1, I created a table and keep updating the column value:



test@MYDB> create table t ( a int);

Table created.

test@MYDB> insert into t values(1);

1 row created.

test@MYDB> commit;

Commit complete.

test@MYDB> select * from t;

A
----------
1

test@MYDB> update t set a=2;

1 row updated.

test@MYDB> commit;

Commit complete.

test@MYDB> update t set a=3;

1 row updated.

test@MYDB> commit;

Commit complete.

test@MYDB> update t set a=4;

1 row updated.

test@MYDB> commit;

Commit complete.

test@MYDB> update t set a=5;

1 row updated.

test@MYDB> commit;

Commit complete.

test@MYDB>





In the session 2, after update the value to 3 in session 1, I started a read-only transaction as following:


test@MYDB> set transaction read only;

Transaction set.


And after each update in session 1, I query the table t, notice I always got value 3, this demonstrated the point:
"all queries return information with respect to the system change number (SCN) determined when the read-only transaction begins"


test@MYDB> select * from t;

A
----------
3

test@MYDB> select * from t;

A
----------
3

test@MYDB> select * from t;

A
----------
3





Then in session 2, I issue the commmit, this end of the read only transaction mode, and I query table t again, got the value 5 as expected


test@MYDB> commit;

Commit complete.

test@MYDB> select * from t;

A
----------
5

No comments: