|
Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index
Oracle DBA FAQ - Understanding SQL Transaction Management
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
|