Transaction isolation level - A test

From Oracle 9i Database Concepts Chapter 20, we can read the following statement:

Oracle generates an error when a serializable transaction tries to update or delete data modified by a transaction that commits after the serializable transaction began:

ORA-08177: Cannot serialize access for this transaction

Tested with the following steps to verify the above statement


(1) In Session 1, issue:

hr@ORCL> select * from t1;

ID NAME
---------- ----------
4 SMITH
1 SUN
2 SUN
3 JIN

(2) Also in Session 1:

hr@ORCL> alter session set isolation_level=serializable;

Session altered.

Note: Oracle default isolation_level is read committed

(3) Still in Session 1:
hr@ORCL> update t1 set name='SUN' where id=3;

1 row updated.

hr@ORCL> select * from t1;

ID NAME
---------- ----------
4 SMITH
1 SUN
2 SUN
3 SUN

(4) Now in session 2, execute:

hr@ORCL> update t1 set name='SUN' where id=4;

1 row updated.
hr@ORCL> commit;

Commit complete.

hr@ORCL> select * from t1;

ID NAME
---------- ----------
4 SUN
1 SUN
2 SUN
3 JIN

(5) Now back to the session 1, issue:

hr@ORCL> delete from t1 where name='SUN';
delete from t1 where name='SUN'
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction


hr@ORCL> commit;

Commit complete.

hr@ORCL> select * from t1;

ID NAME
---------- ----------
4 SUN
1 SUN
2 SUN
3 SUN


Note: if the isolation level is the default (read commit), the delete in this step will delete all rows in the table t1