Oracle DBA FAQ - Understanding SQL Transaction Management

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6 7

(Continued from previous part...)

Keep the "HR" SQL*Plus window as is, and open another window to run another instance of SQL*Plus.

>cd (OracleXE home directory)
>.\bin\sqlplus /nolog

SQL> connect SYSTEM/password
Connected.

SQL> SELECT sid, serial#, username, status, type
  2  FROM V$SESSION WHERE username = 'HR';
       SID    SERIAL# USERNAME           STATUS   TYPE
---------- ---------- ------------------ -------- -----
        39        141 HR                 INACTIVE USER

SQL> ALTER SYSTEM KILL SESSION '39,141';
System altered.

Go back to the "HR" SQL*Plus window.

SQL> SELECT * FROM fyi_links;
ORA-00028: your session has been killed

SQL> connect HR/fyicenter

SQL> SELECT * FROM fyi_links;
     ID URL              NOTES          COUNTS CREATED
------- ---------------- ---------- ---------- ---------
    101 FYICENTER.COM                          07-MAY-06
    110 CENTERFYI.COM                          07-MAY-06
    112 oracle.com                             07-MAY-06
    113 sql.com                                07-MAY-06

As you can see, two records were rolled back as the session got killed by another session.

How Does Oracle Handle Read Consistency?

Oracle supports two options for you on how to maintain read consistency:

READ WRITE (the default option), also called statement-level read consistency.
READ ONLY, also called transaction-level read consistency.

What Is a READ WRITE Transaction?

A READ WRITE transaction is a transaction in which the read consistency is set at the statement level. In a READ WRITE transaction, a logical snapshot of the database is created at the beginning of the execution of each statement and released at the end of the execution. This guaranties that all reads within a single statement get consistent data from the database.

For example, if you have a query statement that takes 10 minutes to be executed, a snapshot of the database will be created for this statement for 10 minutes. If a subquery is used in this statement, it will get the consistent data no matter when it gets executed within this 10 minutes. In another word, data changes made during this 10 minutes by other users will not impact the execution of this query statement.

By default, all transactions are started as READ WRITE transactions.

What Is a READ ONLY Transaction?

A READ ONLY transaction is a transaction in which the read consistency is set at the transaction level. In a READ ONLY transaction, a logical snapshot of the database is created at the beginning of the transaction and released at the end of the transaction. This guaranties that all reads in all statements within this transaction get consistent data from the database.

For example, if you have a transaction with many statements that takes 10 hours to be executed, a snapshot of the database will be created for this transaction for 10 hours. If a query statement is executed at the beginning of the transaction and at the end of the transaction, it will return the same result guarantied. In another word, data changes made during this 10 hours by other users will not impact the execution of statements within this transaction.

How To Set a Transaction To Be READ ONLY?

If you want a transaction to be set as READ ONLY, you need to the transaction with the SET TRANSACTION READ ONLY statement. Note that a DML statement will start the transaction automatically. So you have to issue the SET TRANSACTION statement before any DML statements. The tutorial exercise below shows you a good example of READ ONLY transaction:

SQL> connect HR/fyicenter

SQL> SET TRANSACTION READ ONLY;
Transaction set.

SQL> SELECT * FROM fyi_links;
     ID URL              NOTES          COUNTS CREATED
------- ---------------- ---------- ---------- ---------
    101 FYICENTER.COM                          07-MAY-06
    110 CENTERFYI.COM                          07-MAY-06
    112 oracle.com                             07-MAY-06
    113 sql.com                                07-MAY-06

(Continued on next part...)

Part: 1 2 3 4 5 6 7

More...