Oracle Database Online Documentation 11g Release 1

Four Transaction Control
This bankruptcy defines a transaction and describes how you’ll be able to organize your paintings the use of transactions.

This bankruptcy incorporates the next subjects:

Advent to Transactions
Review of Transaction Control
Evaluation of Independent Transactions
Advent to Transactions
A transaction is a logical unit of labor that accommodates a number of SQL statements. A transaction is an atomic unit. The consequences of all of the SQL statements in a transaction can also be both all dedicated (implemented to the database) or all rolled again (undone from the database).

A transaction starts with the primary executable SQL assertion. A transaction ends when it’s dedicated or rolled again, both explicitly with a COMMIT or ROLLBACK assertion or implicitly when a DDL assertion is issued.

For instance the idea that of a transaction, believe a banking database. When a financial institution buyer transfers cash from a financial savings account to a bank account, the transaction can consist of 3 separate operations:

Decrement the financial savings account
Increment the bank account
Document the transaction within the transaction magazine
Oracle Database will have to permit for 2 scenarios. If all 3 SQL statements may also be carried out to care for the accounts in right kind stability, the consequences of the transaction may also be implemented to the database. On the other hand, if an issue corresponding to inadequate price range, invalid account quantity, or a hardware failure prevents one or two of the statements within the transaction from finishing, all of the transaction will have to be rolled again in order that the stability of all accounts is right kind.

Determine Four-1 illustrates the banking transaction instance.

Determine Four-1 A Banking Transaction

Description of Determine Four-1 follows
Description of “Determine Four-1 A Banking Transaction”

This segment comprises the next subjects:

Remark Execution and Transaction Regulate
Observation-Degree Rollback
Resumable Area Allocation
Observation Execution and Transaction Keep an eye on
A SQL assertion that runs effectively isn’t like a dedicated transaction. Executing effectively signifies that a unmarried assertion used to be:


Discovered to be a legitimate SQL development

Run with out error as an atomic unit. As an example, all rows of a multirow replace are modified.

Then again, till the transaction that incorporates the assertion is dedicated, the transaction may also be rolled again, and all the adjustments of the assertion may also be undone. A assertion, slightly than a transaction, runs effectively.

Committing signifies that a consumer has explicitly or implicitly asked that the adjustments within the transaction be made everlasting. An particular request happens when the consumer problems a COMMIT assertion. An implicit request happens after commonplace termination of an software or final touch of a knowledge definition language (DDL) operation. The adjustments made via the SQL assertion(s) of a transaction transform everlasting and visual to different customers best after that transaction commits. Queries which are issued after the transaction commits will see the dedicated adjustments.

You’ll be able to identify a transaction the use of the SET TRANSACTION … NAME assertion prior to you get started the transaction. This makes it more uncomplicated to watch long-running transactions and to get to the bottom of in-doubt allotted transactions.

See Additionally:
“Transaction Naming”
Observation-Degree Rollback
If at any time throughout execution a SQL assertion reasons an error, all results of the assertion are rolled again. The impact of the rollback is as though that assertion had by no means been run. This operation is a statement-level rollback.

Mistakes found out all the way through SQL assertion execution reason statement-level rollbacks. An instance of such an error is making an attempt to insert a replica worth in a number one key. Unmarried SQL statements concerned about a impasse (festival for a similar knowledge) too can purpose a statement-level rollback. Mistakes found out right through SQL assertion parsing, corresponding to a syntax error, have now not but been run, so they don’t purpose a statement-level rollback.

A SQL assertion that fails reasons the loss handiest of any paintings it will have carried out itself. It does now not purpose the lack of any paintings that preceded it within the present transaction. If the assertion is a DDL assertion, then the implicit devote that in an instant preceded it isn’t undone.

Customers can not without delay discuss with implicit savepoints in rollback statements.
See Additionally:
Resumable Area Allocation
Oracle Database supplies a way for postponing, and later resuming, the execution of huge database operations within the tournament of area allocation screw ups. This permits an administrator to take corrective motion, as an alternative of the Oracle database server returning an error to the consumer. After the mistake situation is corrected, the suspended operation routinely resumes.

A assertion runs in a resumable mode best when the buyer explicitly allows resumable semantics for the consultation the use of the ALTER SESSION assertion.

Resumable area allocation is suspended when probably the most following prerequisites happen:

Out of area situation
Most extents reached situation
Area quota exceeded situation
For nonresumable area allocation, those prerequisites lead to mistakes and the assertion is rolled again.

Postponing a assertion routinely ends up in postponing the transaction. Thus all transactional assets are held thru a assertion droop and resume.

When the mistake situation disappears (as an example, because of consumer intervention or most likely type area launched through different queries), the suspended assertion mechanically resumes execution.

See Additionally:
Oracle Database Administrator’s Information for details about enabling resumable area allocation, what prerequisites are correctable, and what statements can also be made resumable.
Review of Transaction Control
A transaction in Oracle Database starts when the primary executable SQL assertion is encountered. An executable SQL assertion is a SQL assertion that generates calls to an example, together with DML and DDL statements.

When a transaction starts, Oracle Database assigns the transaction to an to be had undo tablespace to report the rollback entries for the brand new transaction.

A transaction ends when any of the next happens:

A consumer problems a COMMIT or ROLLBACK assertion and not using a SAVEPOINT clause.

A consumer runs a DDL assertion similar to CREATE, DROP, RENAME, or ALTER. If the present transaction accommodates any DML statements, Oracle Database first commits the transaction, after which runs and commits the DDL assertion as a brand new, unmarried assertion transaction.

A consumer disconnects from Oracle Database. The present transaction is dedicated.

A consumer procedure terminates abnormally. The present transaction is rolled again.

After one transaction ends, the following executable SQL assertion mechanically begins the next transaction.

This segment comprises the next subjects:

Devote Transactions
Rollback of Transactions
Savepoints In Transactions
Transaction Naming
The Two-Segment Devote Mechanism
Programs will have to all the time explicitly devote or undo transactions prior to program termination.
Devote Transactions
Committing a transaction approach making everlasting the adjustments carried out through the SQL statements inside the transaction.

Ahead of a transaction that modifies knowledge is dedicated, the next has befell:

Oracle Database has generated undo knowledge. The undo knowledge incorporates the previous knowledge values modified through the SQL statements of the transaction.

Oracle Database has generated redo log entries within the redo log buffer of the SGA. The redo log report incorporates the exchange to the information block and the trade to the rollback block. Those adjustments would possibly pass to disk sooner than a transaction is dedicated.

The adjustments were made to the database buffers of the SGA. Those adjustments would possibly move to disk ahead of a transaction is dedicated.

The information adjustments for a dedicated transaction, saved within the database buffers of the SGA, don’t seem to be essentially written instantly to the datafiles via the database author (DBWn) background procedure. This writing takes position when it’s most productive for the database to take action. It could actually occur sooner than the transaction commits or, on the other hand, it may occur a while after the transaction commits.
When a transaction is dedicated, the next happens:

The interior transaction desk for the related undo tablespace data that the transaction has dedicated, and the corresponding distinctive device exchange quantity (SCN) of the transaction is assigned and recorded within the desk.

The log author procedure (LGWR) writes redo log entries within the SGA’s redo log buffers to the redo log record. It additionally writes the transaction’s SCN to the redo log record. This atomic tournament constitutes the devote of the transaction.

Oracle Database releases locks hung on rows and tables.

Oracle Database marks the transaction whole.

The default conduct is for LGWR to write down redo to the web redo log information synchronously and for transactions to watch for the redo to visit disk prior to returning a decide to the consumer. On the other hand, for decrease transaction devote latency software builders can specify that redo be written asynchronously and that transactions don’t want to look forward to the redo to be on disk.
See Additionally:
Oracle Database Complex Software Developer’s Information for more info on asynchronous devote

“Evaluation of Locking Mechanisms”

“Evaluate of Oracle Database Processes” for more info concerning the background processes LGWR and DBWn

Rollback of Transactions
Rolling again way undoing any adjustments to knowledge which were carried out via SQL statements inside of an uncommitted transaction. Oracle Database makes use of undo tablespaces (or rollback segments) to retailer previous values. The redo log accommodates a document of adjustments.

Oracle Database allows you to roll again a whole uncommitted transaction. On the other hand, you’ll be able to roll again the trailing portion of an uncommitted transaction to a marker referred to as a savepoint.

All varieties of rollbacks use the similar procedures:

Remark-level rollback (because of assertion or impasse execution error)
Rollback to a savepoint
Rollback of a transaction because of consumer request
Rollback of a transaction because of odd procedure termination
Rollback of all remarkable transactions when an example terminates abnormally
Rollback of incomplete transactions right through restoration
In rolling again a whole transaction, with out referencing any savepoints, the next happens:

Oracle Database undoes all adjustments made through all of the SQL statements within the transaction by way of the use of the corresponding undo tablespace.

Oracle Database releases all of the transaction’s locks of knowledge.

The transaction ends.

See Additionally:
“Savepoints In Transactions”

“Evaluation of Locking Mechanisms”

Oracle Database Backup and Restoration Consumer’s Information for details about what occurs to dedicated and uncommitted adjustments all the way through restoration

Savepoints In Transactions
You’ll be able to claim intermediate markers referred to as savepoints inside the context of a transaction. Savepoints divide an extended transaction into smaller portions.

The use of savepoints, you’ll be able to arbitrarily mark your paintings at any level inside of an extended transaction. Then you find a way later of rolling again paintings carried out sooner than the present level within the transaction however after a declared savepoint inside the transaction. As an example, you’ll be able to use savepoints right through an extended complicated collection of updates, so if you are making an error, you do not want to resubmit each and every assertion.

Savepoints are in a similar fashion helpful in software systems. If a process accommodates a number of purposes, then you’ll be able to create a savepoint prior to each and every serve as starts. Then, if a serve as fails, it’s simple to go back the information to its state prior to the serve as started and re-run the serve as with revised parameters or carry out a restoration motion.

After a rollback to a savepoint, Oracle Database releases the information locks acquired through rolled again statements. Different transactions that have been looking ahead to the in the past locked assets can continue. Different transactions that need to replace in the past locked rows can achieve this.

When a transaction is rolled again to a savepoint, the next happens:

Oracle Database rolls again most effective the statements run after the savepoint.

Oracle Database preserves the required savepoint, however all savepoints that have been established after the required one are misplaced.

Oracle Database releases all desk and row locks obtained since that savepoint however keeps all knowledge locks got prior to the savepoint.

The transaction stays lively and can also be persisted.

On every occasion a consultation is ready on a transaction, a rollback to savepoint does now not loose row locks. To ensure a transaction does now not hold if it can not download a lock, use FOR UPDATE … NOWAIT sooner than issuing UPDATE or DELETE statements. (This refers to locks received ahead of the savepoint to which has been rolled again. Row locks received after this savepoint are launched, because the statements achieved after the savepoint were rolled again utterly.)

Transaction Naming
You’ll be able to identify a transaction, the use of a easy and remarkable textual content string. This identify is a reminder of what the transaction is set. Transaction names substitute devote feedback for dispensed transactions, with the next benefits:

It’s more uncomplicated to watch long-running transactions and to get to the bottom of in-doubt allotted transactions.

You’ll be able to view transaction names in conjunction with transaction IDs in programs. As an example, a database administrator can view transaction names in Undertaking Supervisor when tracking device process.

Transaction names are written to the transaction auditing redo report, if compatibility is about to Oracle9i or upper.

LogMiner can use transaction names to seek for a selected transaction from transaction auditing data within the redo log.

You’ll be able to use transaction names to discover a particular transaction in knowledge dictionary perspectives, comparable to V$TRANSACTION.

This segment comprises the next subjects:

How Transactions Are Named
Devote Remark
How Transactions Are Named
Identify a transaction the use of the SET TRANSACTION … NAME assertion ahead of you get started the transaction.

While you identify a transaction, you affiliate the transaction’s identify with its ID. Transaction names don’t have to be distinctive; other transactions may have the similar transaction identify on the similar time through the similar proprietor. You’ll be able to use any identify that lets you distinguish the transaction.

Devote Remark
In earlier releases, you have to affiliate a remark with a transaction through the use of a devote remark. On the other hand, a remark may also be related to a transaction most effective when a transaction is being dedicated.

Devote feedback are nonetheless supported for backward compatibility. On the other hand, Oracle strongly recommends that you simply use transaction names. Devote feedback are disregarded in named transactions.

In a long run unencumber, devote feedback shall be deprecated.

See Additionally:
Oracle Database Administrator’s Information for more info about allotted transactions
Oracle Database SQL Language Reference for more info about transaction naming syntax
The Two-Segment Devote Mechanism
In a allotted database, Oracle Database will have to coordinate transaction regulate over a community and deal with knowledge consistency, although a community or device failure happens.

A dispensed transaction is a transaction that incorporates a number of statements that replace knowledge on two or extra distinct nodes of a allotted database.

A two-phase devote mechanism promises that every one database servers taking part in a dispensed transaction both all devote or all undo the statements within the transaction. A two-phase devote mechanism additionally protects implicit DML operations carried out through integrity constraints, faraway process calls, and triggers.

The Oracle Database two-phase devote mechanism is totally clear to customers who factor allotted transactions. In reality, customers don’t need to even know the transaction is sent. A COMMIT assertion denoting the top of a transaction mechanically triggers the two-phase devote mechanism to devote the transaction. No coding or complicated assertion syntax is needed to incorporate dispensed transactions inside the frame of a database software.

The recoverer (RECO) background procedure routinely resolves the result of in-doubt allotted transactions—allotted transactions during which the devote used to be interrupted by way of any form of gadget or community failure. After the failure is repaired and communique is reestablished, the RECO strategy of each and every native Oracle database mechanically commits or rolls again any in-doubt dispensed transactions persistently on all concerned nodes.

Within the tournament of a long-term failure, Oracle Database lets in each and every native administrator to manually devote or undo any dispensed transactions which are unsure because of the failure. This feature allows the native database administrator to loose any locked assets which are held indefinitely because of the long-term failure.

If a database will have to be recovered to some degree prior to now, Oracle Database restoration amenities allow database directors at different websites to go back their databases to the sooner time limit additionally. This operation guarantees that the worldwide database stays constant.

See Additionally:
Oracle Database Heterogeneous Connectivity Administrator’s Information
Review of Self sufficient Transactions
Self reliant transactions are unbiased transactions that may be referred to as from inside of any other transaction. An self sufficient transaction permits you to depart the context of the calling transaction, carry out some SQL operations, devote or undo the ones operations, after which go back to the calling transaction’s context and proceed with that transaction.

As soon as invoked, an self sufficient transaction is completely unbiased of the primary transaction that referred to as it. It does now not see any of the uncommitted adjustments made by way of the primary transaction and does now not percentage any locks or assets with the primary transaction. Adjustments made by way of an self sustaining transaction develop into visual to different transactions upon devote of the self sustaining transactions.

One independent transaction can name some other. There aren’t any limits, rather than useful resource limits, on what number of ranges of self sufficient transactions can also be referred to as.

Deadlocks are imaginable between an self sustaining transaction and its calling transaction. Oracle Database detects such deadlocks and returns an error. The appliance developer is answerable for averting impasse scenarios.

Self reliant transactions are helpful for enforcing movements that want to be carried out independently, without reference to whether or not the calling transaction commits or rolls again, comparable to transaction logging and retry counters.

Independent PL/SQL Blocks
You’ll be able to name self sufficient transactions from inside of a PL/SQL block. Use the pragma AUTONOMOUS_TRANSACTION. A pragma is a compiler directive. You’ll be able to claim the next forms of PL/SQL blocks to be independent:

Saved process or serve as
Native process or serve as
Package deal
Sort means
Most sensible-level nameless block
When an self sustaining PL/SQL block is entered, the transaction context of the caller is suspended. This operation guarantees that SQL operations carried out on this block (or different blocks referred to as from it) don’t have any dependence or impact at the state of the caller’s transaction context.

When an self sustaining block invokes some other self sustaining block or itself, the referred to as block does now not percentage any transaction context with the calling block. On the other hand, when an independent block invokes a non-autonomous block (this is, one that isn’t declared to be self sufficient), the referred to as block inherits the transaction context of the calling self sufficient block.

Transaction Keep an eye on Statements in Self sufficient Blocks
Transaction regulate statements in an self sufficient PL/SQL block follow handiest to the recently lively self sustaining transaction. Examples of such statements are:

In a similar fashion, transaction regulate statements in the primary transaction practice most effective to that transaction and to not any self sufficient transaction that it calls. As an example, rolling again the primary transaction to a savepoint taken prior to the start of an independent transaction does now not undo the self sustaining transaction.

Leave a Reply

Your email address will not be published. Required fields are marked *