Transactions-CSDN博客

一，Enqueued Transactions

Depending on the scenario, transactions waiting for previously locked resources may still be blocked after a rollback to savepoint. When a transaction is blocked by another transaction it enqueues on the blocking transaction itself, so that the entire blocking transaction must commit or roll back for the blocked transaction to continue.

In the scenario shown in Table 10-2, session 1 rolls back to a savepoint created before it executed a DML statement. However, session 2 is still blocked because it is waiting for the session 1 transaction to complete.

二，Rollback of Transactions

A rollback of an uncommitted transaction undoes any changes to data that have been performed by SQL statements within the transaction. After a transaction has been rolled back, the effects of the work done in the transaction no longer exist.

In rolling back an entire transaction, without referencing any savepoints, Oracle Database performs the following actions:

Undoes all changes made by all the SQL statements in the transaction by using the corresponding undo segments

The transaction table entry for every active transaction contains a pointer to all the undo data (in reverse order of application) for the transaction. The database reads the data from the undo segment, reverses the operation, and then marks the undo entry as applied. Thus, if a transaction inserts a row, then a rollback deletes it. If a transaction updates a row, then a rollback reverses the update. If a transaction deletes a row, then a rollback reinserts it.

Releases all the locks of data held by the transaction
Erases all savepoints in the transaction

Ends the transaction

In Table 10-1,the ROLLBACK leaves the database in the same state as it was after the initial COMMIT was executed.

The duration of a rollback is a function of the amount of data modified.

三Committing Transactions

A commit ends the current transaction and makes permanent all changes performed in the transaction. In Table 10-1, a second transaction begins with sal_update2 and ends with an explicit COMMIT statement. The changes that resulted from the two UPDATE statements are now made permanent.

When a transaction commits, the following actions occur:

A system change number (SCN) is generated for the COMMIT.

The i nternal transaction table for the associated undo tablespace records that the transaction has committed. The corresponding unique SCN of the transaction is assigned and recorded in the transaction table. See "Serializable Isolation Level".

The log writer (LGWR) process writes remaining redo log entries in the redo log buffers to the online redo log and writes the transaction SCN to the online redo log. This atomic event constitutes the commit of the transaction.
Oracle Database releases locks held on rows and tables.

Users who were enqueued waiting on locks held by the uncommitted transaction are allowed to proceed with their work.

Oracle Database deletes savepoints.

In Table 10-1, no savepoints existed in the sal_update transaction so no savepoints were erased.

Oracle Database performs a commit cleanout.

If modified blocks containing data from the committed transaction are still in the SGA, and if no other session is modifying them, then the database removes lock-related transaction information from the blocks. Ideally, the COMMIT cleans out the blocks so that a subsequent SELECT does not have to perform this task.

Note:

Because a block cleanout generates redo, a query may generate redo and thus cause blocks to be written during the next checkpoint.

Oracle Database marks the transaction complete.

After a transaction commits, users can view the changes.

Typically, a commit is a fast operation, regardless of the transaction size. The speed of a commit does not increase with the size of the data modified in the transaction. The lengthiest part of the commit is the physical disk I/O performed by LGWR. However, the amount of time spent by LGWR is reduced because it has been incrementally writing the contents of the redo log buffer in the background.

The default behavior is for LGWR to write redo to the online redo log synchronously and for transactions to wait for the buffered redo to be on disk before returning a commit to the user. However, for lower transaction commit latency, application developers can specify that redo be written asynchronously so that transactions need not wait for the redo to be on disk and can return from the COMMIT call immediately.

See Also:

Oracle Database PL/SQL Language Reference for more information on asynchronous commit
"Locking Mechanisms"
"Overview of Background Processes" for more information about LGWR

四,Overview of Autonomous Transactions

An autonomous transaction is an independent transaction that can be called from another transaction, called the main transaction. You can suspend the calling transaction, perform SQL operations and commit or undo them in the autonomous transaction, and then resume the calling transaction.

Autonomous transactions are useful for actions that must be performed independently, regardless of whether the calling transaction commits or rolls back. For example, in a stock purchase transaction, you want to commit customer data regardless of whether the overall stock purchase goes through. Additionally, you want to log error messages to a debug table even if the overall transaction rolls back.

Autonomous transactions have the following characteristics:

The autonomous transaction does not see uncommitted changes made by the main transaction and does not share locks or resources with the main transaction.
Changes in an autonomous transaction are visible to other transactions upon commit of the autonomous transactions. Thus, users can access the updated information without having to wait for the main transaction to commit.
Autonomous transactions can start other autonomous transactions. There are no limits, other than resource limits, on how many levels of autonomous transactions can be called.

In PL/SQL, an autonomous transaction executes within an autonomous scope, which is a routine marked with the pragma AUTONOMOUS_TRANSACTION. In this context, routines include top-level anonymous PL/SQL blocks and PL/SQL subprograms and triggers. A pragma is a directive that instructs the compiler to perform a compilation option. The pragma AUTONOMOUS_TRANSACTION instructs the database that this procedure, when executed, is to be executed as a new autonomous transaction that is independent of its parent transaction.

Figure 10-2 shows how control flows from the main routine (MT) to an autonomous routine and back again. The main routine is proc1 and the autonomous routine is proc2. The autonomous routine can commit multiple transactions (AT1 and AT2) before control returns to the main routine.

Figure 10-2 Transaction Control Flow

Description of "Figure 10-2 Transaction Control Flow"

When you enter the executable section of an autonomous routine, the main routine suspends. When you exit the autonomous routine, the main routine resumes.

In Figure 10-2, the COMMIT inside proc1 makes permanent not only its own work but any outstanding work performed in its session. However, a COMMIT in proc2 makes permanent only the work performed in the proc2 transaction. Thus, the COMMIT statements in transactions AT1 and AT2 have no effect on the MT transaction.

See Also:

Oracle Database Advanced Application Developer's Guide and Oracle Database PL/SQL Language Reference to learn how to use autonomous transactions

五 Overview of Distributed Transactions

A distributed database is a set of databases in a distributed system that can appear to applications as a single data source. A distributed transaction is a transaction that includes one or more statements that update data on two or more distinct nodes of a distributed database, using a schema object called a database link. A database link describes how one database instance can log in to another database instance.

Unlike a transaction on a local database, a distributed transaction alters data on multiple databases. Consequently, distributed transaction processing is more complicated because the database must coordinate the committing or rolling back of the changes in a transaction as an atomic unit. The entire transaction must commit or roll back. Oracle Database must coordinate transaction control over a network and maintain data consistency, even if a network or system failure occurs.

See Also:

Oracle Database Administrator's Guide

六 Two-Phase Commit

The two-phase commit mechanism guarantees that all databases participating in a distributed transaction either all commit or all undo the statements in the transaction. The mechanism also protects implicit DML performed by integrity constraints, remote procedure calls, and triggers.

In a two-phase commit among multiple databases, one database coordinates the distributed transaction. The initiating node is called the global coordinator. The coordinator asks the other databases if they are prepared to commit. If any database responds with a no, then the entire transaction is rolled back. If all databases vote yes, then the coordinator broadcasts a message to make the commit permanent on each of the databases.

The two-phase commit mechanism is transparent to users who issue distributed transactions. In fact, users need not even know the transaction is distributed. A COMMIT statement denoting the end of a transaction automatically triggers the two-phase commit mechanism. No coding or complex statement syntax is required to include distributed transactions within the body of a database application.

See Also:

Oracle Database Administrator's Guide to learn about the two-phase commit mechanism

七 In-Doubt Transactions

An in-doubt distributed transaction occurs when a two-phase commit was interrupted by any type of system or network failure. For example, two databases report to the coordinating database that they were prepared to commit, but the coordinating database instance fails immediately after receiving the messages. The two databases who are prepared to commit are now left hanging while they await notification of the outcome.

The recoverer (RECO) background process automatically resolves the outcome of in-doubt distributed transactions. After the failure is repaired and communication is reestablished, the RECO process of each local Oracle database automatically commits or rolls back any in-doubt distributed transactions consistently on all involved nodes.

In the event of a long-term failure, Oracle Database enables each local administrator to manually commit or undo any distributed transactions that are in doubt because of the failure. This option enables the local database administrator to free any locked resources that are held indefinitely because of the long-term failure.

If a database must be recovered to a past time, then database recovery facilities enable database administrators at other sites to return their databases to the earlier point in time. This operation ensures that the global database remains consistent.

See Also: