5.3 Transaction Control

 < Day Day Up > 



SQL DML can be controlled for rollback and DDL commands cannot. A transaction is a sequence of SQL DML commands. This sequence of commands can be stored or undone using a COMMIT or ROLLBACK command respectively. A SAVEPOINT command can be used to execute a partial rollback on a transaction back to a SAVEPOINT label.

SAVEPOINT label;  ¼ sql commands ¼  ROLLBACK TO SAVEPOINT label;

The SET TRANSACTION command is used to set certain aspects for a transaction or sequence of SQL statements.

SET TRANSACTION  {       NAME 'transaction'    | {             READ { WRITE | ONLY }          | ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE }          | USE ROLLBACK SEGMENT rollback segment       } [ NAME 'transaction' ] };

The defaults for the SET TRANSACTION command are READ WRITE and ISOLATION LEVEL READ COMMITTED. ISOLATION LEVEL READ COMMITTED will cause the transaction to wait for any competing locks to be released before the transaction is completed. ISOLATION LEVEL SERIALIZABLE will cause a transaction to immediately fail if a lock is encountered.

5.3.1 COMMIT versus ROLLBACK

COMMIT stores changes made in a transaction to the database. ROLLBACK will undo those changes. A quick examination of exactly what the COMMIT and ROLLBACK commands do is warranted, since COMMIT is in general a faster process than a ROLLBACK operation. The reason for this is that it is assumed, and quite sensibly so, that COMMIT is executed much more often than ROLLBACK.

Why is a COMMIT command faster than a ROLLBACK command? What occurs in the database during the processing of these commands?

  • Before COMMIT or ROLLBACK

    • The database is changed.

    • Redo logs are written.

    • Rollback is written.

  • On COMMIT

    • Rollback is deleted.

  • On ROLLBACK

    • Rollback is applied to the database.

    • Rollback is recorded in the redo logs.

    • Rollback is deleted.

Before a COMMIT or ROLLBACK is executed any changes are physically stored in the database. The COMMIT command simply removes any recourse to undoing the changes and ROLLBACK executes everything in reverse. Figure 5.5 shows a vague relationship among the database, logs, and rollback.

click to expand
Figure 5.5: COMMIT versus ROLLBACK

5.3.2 Transaction Control between Multiple Sessions

Locking can occur between different sessions when changes are made to the same data. If one user changes a row using an UPDATE or DELETE command, without terminating the transaction, then that row or rows will be locked until a COMMIT or ROLLBACK command is executed. A second session attempting to change the same row or rows will not respond until the first session terminates the transaction. Multiple sessions can access the database as shown in Figure 5.6.

click to expand
Figure 5.6: Locking Rows Before Transaction Completion



 < Day Day Up > 



Oracle High Performance Tuning for 9i and 10g
Oracle High Performance Tuning for 9i and 10g
ISBN: 1555583059
EAN: 2147483647
Year: 2003
Pages: 164

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net