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>  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.

What Are the Restrictions in a READ ONLY Transaction?

There are lots of restrictions in a READ ONLY transaction:

• You can not switch to READ WRITE mode.
• You can not run any INSERT, UPDATE, DELETE statements.
• You can run SELECT query statements.

The tutorial exercise below shows you some of the restrictions:

SQL> connect HR/fyicenter

SQL> SET TRANSACTION READ ONLY;
Transaction set.

SQL> SET TRANSACTION READ WRITE;
ORA-01453: SET TRANSACTION must be first statement of
   transaction

SQL> INSERT INTO fyi_links (url, id)
  2  VALUES ('sql.com', 113);
ORA-01456: may not perform insert/delete/update operation
  inside a READ ONLY transaction

SQL> DELETE FROM fyi_links where id = 110;
ORA-01456: may not perform insert/delete/update operation
  inside a READ ONLY transaction

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

What Are the General Rules on Data Consistency?

• All SQL statements always work with a snapshot of the database to provide data consistency.
• For READ WRITE transactions, the snapshot is taken when each statement starts.
• For READ ONLY transactions, the snapshot is taken when the transaction starts.
• The snapshot never include uncommitted changes from other transactions.
• The snapshot always include uncommitted changes from its own transaction.

(Continued on next part...)

Part:   1  2  3  4   5  6  7