You don’t need a “begin transaction” statement in Oracle. A transaction implicitly begins with the first statement that modifies data (the first statement that gets a TX lock). You can explicitly begin a transaction using SET TRANSACTION or the DBMS_TRANSACTION package, but it is not a necessary step, unlike in some other databases. Issuing either a COMMIT or ROLLBACK statement explicitly ends a transaction.
Note Not all ROLLBACK statements are created equal. It should be noted that a ROLLBACK TO SAVEPOINT command will not end a transaction! Only a full, proper ROLLBACK will.
You should always explicitly terminate your transactions with a COMMIT or ROLLBACK; otherwise, the tool or environment you’re using will pick one or the other for you. If you exit your SQLPlus session normally, without committing or rolling back, SQLPlus assumes you wish to commit your work and it does so. If you just exit from a Pro*C program, on the other hand, an implicit rollback takes place. Never rely on implicit behavior, as it could change in the future. Always explicitly COMMIT or ROLLBACK your transactions.
Note As an example of something changing in the future, SQLPlus in Oracle 11g Release 2 and above contains a setting, “exitcommit.” This setting controls whether SQLPlus issues a COMMIT or ROLLBACK upon exit. So when you use 11g Release 2, the default behavior that has been in place since SQL*Plus was invented may well be different!
Transactions are atomic in Oracle, meaning that either every statement that comprises the transaction is committed (made permanent) or all of the statements are rolled back. This protection is extended to individual statements as well. Either a statement entirely succeeds, or the statement is entirely rolled back.
Note that I said the “statement” is rolled back. The failure of one statement does not cause previously executed statements to be automatically rolled back. Their work is preserved and must either be committed or rolled back by you.
Before we get into the details of exactly what it means for a statement and transaction to be atomic, let’s take a look at the various transaction control statements available to us:
•\ COMMIT: To use this statement’s simplest form, you just issue COMMIT. You could be more verbose and say COMMIT WORK, but the two are equivalent. A COMMIT ends your transaction and makes any changes permanent (durable). There are extensions to the COMMIT statement used in distributed transactions that allow you to label a COMMIT (label a transaction) with some meaningful comment and force the commit of an in-doubt distributed transaction. There are also extensions that allow you to perform an asynchronous commit—a commit that actually breaks the durability concept. We’ll take a look at this in a bit and see when it might be appropriate to use.
•\ ROLLBACK: To use this statement’s simplest form, you just issue ROLLBACK. Again, you could be more verbose and say ROLLBACK WORK, but the two are equivalent. A rollback ends your transaction and undoes any uncommitted changes. It does this by reading information stored in the rollback/undo segments (going forward I’ll refer to these exclusively as undo segments) and restoring the database blocks to the state they were before your transaction began.
•\ SAVEPOINT: A SAVEPOINT allows you to create a marked point within a transaction. You may have multiple SAVEPOINTs within a single transaction.
•\ ROLLBACK TO : This statement is used with the SAVEPOINT command. You can roll back your transaction to that marked point without rolling back any of the work that preceded it. So, you could issue two UPDATE statements, followed by a SAVEPOINT and then two DELETE statements. If an error or some sort of exceptional condition occurs during execution of the DELETE statements, and you catch that exception and issue the ROLLBACK TO SAVEPOINT command, the transaction will roll back to the named SAVEPOINT, undoing any work performed by the DELETEs but leaving the work performed by the UPDATE statements intact.
•\ SET TRANSACTION: This statement allows you to set various transaction attributes, such as the transaction’s isolation level and whether it is read-only or read-write. You can also use this statement to instruct the transaction to use a specific undo segment when using manual undo management, but this is not recommended. We’ll discuss manual and automatic undo management in more detail in the next chapter.
That’s it—there are no other transaction control statements. The most frequently used control statements are COMMIT and ROLLBACK. The SAVEPOINT statement has a somewhat special purpose. Internally, Oracle uses it frequently; in fact, Oracle uses it every time you execute any SQL or PL/SQL statement, and you may find some use for it in your applications as well.
Atomicity
Now we’re ready to see what’s meant by statement, procedure, and transaction atomicity.
