0675-0677
Page 675
- If duplicate customer is found, roll back to the previous savepoint (continue transaction 2).
- Repeat steps 4_7 until finished.
- Issue commit (end transaction 2).
- Drop temporary table (begin and end transaction 3).
- Exit from SQL*Plus (end session 1).
Notice how the savepoint enables you to roll back to a point within your current transaction without affecting the previous updates before the savepoint. Anywhere within your procedure, you can roll back to any savepoint or roll back the entire transaction. By using the savepoints, you are providing a collection of recovery points that are available to you until you end that transaction. When the transaction is ended, all savepoints are erased.
| TIP |
You use savepoints to logically break up large commits. For example, if you have a situation in which you must update a large history table before checking for inaccurate data, you could |
| CAUTION |
Once a transaction is ended (by a commit or rollback), it is impossible to roll back to a previously created savepoint. At that point, all savepoints are deleted and can be reused. If you specify a duplicate name for a savepoint, the previous savepoint will be deleted and a new one set at the current point in the transaction. |
Page 676
Transaction Control Statements
Transaction control statements are statements that affect the execution or properties of a transaction, whether it is the management of data or characteristics of how the transaction executes. The family of transaction control statements includes
- COMMIT
- SAVEPOINT
- ROLLBACK
- SET TRANSACTION
Types of Transactions
Several names are used to identify transactions and their states. Knowing these terms is helpful in understanding the terms mentioned by Oracle and interpreting Oracle errors returned during a transaction. These terms cover types of transactions as well as other terms used in identifying them.
Concurrent Transactions
Concurrent transactions are transactions that are executed in the same general time. These transactions, because they have started so close to each other, generally do not see the changes made by the other transactions. Any data that has been updated by a concurrent transaction and requested by another concurrently running transaction must be read from rollback segments until the transaction requesting the data has completed. This has the potential of leading to the error message Snapshot too old, which is discussed in more detail under the "Assigning Rollback Segments" section of this chapter.
Discreet Transactions
A discreet transaction is used to improve the performance of short transactions. For developers creating custom applications, the procedure BEGIN_DISCREET_TRANSACTION() changes the steps followed during the duration of a session in order to speed its processing. The main differences are as follows :
- All changes are held until the transaction ends.
- Other transactions cannot see uncommitted changes.
- Redo information is stored in a separate location in the SGA.
- No rollback information is written because all changes are held until a commit and then applied directly to the data block(s).
Because the overhead associated with redo and rollback segments is bypassed by storing information directly in the SGA, these transactions bypass a great amount of processing overhead.
Page 677
At first this sounds more like a flaw than a feature, but most changes can be made in a very short amount of time because these transactions are short in nature.
| NOTE |
Discreet transactions will not work as distributed transactions due to the changes in transaction processing. |
Distributed Transactions
Distributed transactions are transactions in which one or more statements manipulate data on two or more nodes, or remote systems, of a distributed database. If a transaction manipulates data on only one node, it is considered a remote transaction. As in a remote transaction, none of the redo information is stored locally.
In-Doubt Transactions
An in-doubt transaction is actually a state of a transaction instead of a type and refers to transactions within a distributed database environment. One situation that causes this state is if an instance involved in a currently running transaction fails, that transaction must be either rolled back or committed. It is difficult, however, to do either without knowing the state of the transaction in the affected database. In this case, all other instances in the distributed environment mark this transaction as in-doubt. Once the instance is restarted, the transaction can be analyzed and all instances can either commit or roll back.
It is possible to force the commit or rollback of a distributed transaction by using either SQL*DBA and doing a Recover In-Doubt Transaction, or the command COMMIT WORK ... FORCE with the local or global transaction ID of the in-doubt transaction. Refer to Chapter 14, "Server Manager," or Chapter 13 for further information on how to roll back or commit this transaction.
Normal Transactions
Normal transaction is a term used to refer to a local (non-remote) transaction. All redo information is stored in the local database, and all data manipulation is done to the same database. This type of transaction is the focus for the discussion on transaction processing.
Read-Only Transactions
Read-only refers to the type of read consistency that is set or defaulted to for a given transaction. By default, the level of read consistency is statement level, which is also known as read-write. This means that each consecutive statement in your transaction will see the changes made to the database by any previous statements regardless of which transaction has committed the changes.
EAN: 2147483647
Pages: 391