A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly (with a COMMIT or ROLLBACK statement) or implicitly (when a DDL statement is issued).
To illustrate the concept of a transaction, consider a banking database. When a bank customer transfers money from a savings account to a checking account, the transaction might consist of three separate operations: decrement the savings account, increment the checking account, and record the transaction in the transaction journal.
Oracle must allow for two situations. If all three SQL statements can be performed to maintain the accounts in proper balance, the effects of the transaction can be applied to the database. However, if something (such as insufficient funds, invalid account number, or a hardware failure) prevents one or two of the statements in the transaction from completing, the entire transaction must be rolled back so that the balance of all accounts is correct.
Figure 12 - 1 illustrates the banking transaction example.
Figure 12 - 1. A Banking Transaction
Executing successfully means that a single statement was parsed and found to be a valid SQL construction, and that the entire statement executed without error as an atomic unit (for example, all rows of a multi-row update are changed). However, until the transaction that contains the statement is committed, the transaction can be rolled back, and all of the changes of the statement can be undone. A statement, rather than a transaction, executes successfully.
Committing means that a user has said either explicitly or implicitly "make the changes in this transaction permanent". The changes made by the SQL statement(s) of your transaction only become permanent and visible to other users after your transaction has been committed. Only other users' transactions that started after yours will see the committed changes.
Errors that cause statement rollbacks are errors discovered during the execution stage of SQL statement processing (such as attempting to insert a duplicate value in a primary key or an invalid number into a numeric column), not the parsing stage (such as syntax errors in a SQL statement). Single SQL statements can also be rolled back to resolve deadlocks (competition for the same data); see "Deadlock Detection" ![[*]](jump.gif)
.
Therefore, a SQL statement that fails causes the loss only of any work it would have performed itself; it does not cause the loss of any work that preceded it in the current transaction. If the statement is a DDL statement, the implicit commit that immediately preceded it is not undone. This is a statement-level rollback.
Note: Users cannot directly refer to implicit savepoints in rollback statements.