Categories:
.NET (961)
C (387)
C++ (185)
CSS (84)
DBA (8)
General (31)
HTML (48)
Java (641)
JavaScript (220)
JSP (109)
JUnit (31)
MySQL (297)
Networking (10)
Oracle (562)
Perl (48)
Perl (9)
PHP (259)
PL/SQL (140)
RSS (51)
Software QA (28)
SQL Server (5)
Struts (20)
Unix (2)
Windows (3)
XHTML (199)
XML (59)
Other Resources:
How To Set a Transaction To Be READ ONLY
How To Set a Transaction To Be READ ONLY? - Oracle DBA FAQ - Understanding SQL Transaction Management
✍: FYIcenter.com
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
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> DELETE FROM hr.fyi_links where id = 112; 1 row deleted. SQL> DELETE FROM hr.fyi_links where id = 113; 1 row deleted. SQL> COMMIT; Commit complete.
Go back to the "HR" SQL*Plus window.
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 SQL> COMMIT; Commit complete. SQL> SELECT * FROM fyi_links; ID URL NOTES COUNTS CREATED ------- ---------------- ---------- ---------- --------- 101 FYICENTER.COM 07-MAY-06 110 CENTERFYI.COM 07-MAY-06
As you can see that two records were deleted from another session after the HR session started the READ ONLY transaction. The deleted records was not impacting any query statements until the transaction was ended with the COMMIT statement.
2007-04-17, 4793👍, 0💬
Popular Posts:
What Tools to Use to View HTML Documents? The basic tool you need to view HTML documents is any Web ...
How To Run a JUnit Test Class? A JUnit test class usually contains a number of test methods. You can...
Is There Any XSD File to Validate Atom Feed Files? - RSS FAQs - Atom Feed Introduction and File Gene...
What is a fish bone diagram ? Dr. Kaoru Ishikawa, invented the fishbone diagram. Therefore, it can b...
Would I use print "$a dollars" or "{$a} dollars" to print out the amount of dollars in this example?...