Results for TRANSACTIONS


1 to 60 of 2111 results. Prev  Next
Results per page:   20  | 40 | 60


Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Database Concepts

Distributed TransactionsA distributed transaction is a transaction that includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database. For example, this transaction updates the local database and the remote sales database: UPDATE scott.dept@sales.us.americas.example_auto.com SET loc = 'NEW YORK' WHERE deptno = 10; UPDATE scott.emp SET deptno = 11 WHERE

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Database Concepts

Remote TransactionsA remote transaction contains one or more remote statements, all of which reference a single remote node. For example, the following transaction contains two statements, each of which accesses the remote sales database: UPDATE scott.dept@sales.us.americas.example_auto.com SET loc = 'NEW YORK' WHERE deptno = 10; UPDATE scott.emp@sales.us.americas.example_auto.com SET deptno = 11 WHERE deptno = 10;

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Static SQL

Autonomous Transactionstransaction. Autonomous transactions do SQL operations and commit or roll back, without committing or

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Naming Transactionstransactions, the name is sent to participating sites when a transaction is committed. If a COMMIT COMMENT

Database Reference, 11g Release 2 (11.2)

TRANSACTIONS

TRANSACTIONSRange of values 4 to 2 32 Oracle RAC Multiple instances can have different values. TRANSACTIONS … concurrent transactions is now restricted by undo tablespace size ( UNDO_MANAGEMENT = AUTO ) or the

Database Concepts, 11g Release 2 (11.2)

Introduction to Oracle Database

Transactionsfor transactions is a funds transfer from a savings account to a checking account. The transfer … transaction from executing, then the other statements must be rolled back. Transactions are one of the … basic principle of a transaction is \"all or nothing\": an atomic operation succeeds or fails as a whole. See Also: Chapter 10, \"Transactions\"

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Transactions Concepts

DML and DDL Transactionspermissible operations in distributed transactions: DML and DDL Transactions Transaction Control Statements … DML and DDL Transactions The following are the DML and DDL operations supported in a distributed

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Static SQL

Advantages of Autonomous Transactionstransactions roll back, but autonomous transactions do not. Its committed changes are visible to other … transactions immediately. A nested transaction's committed changes are not visible to other … transactions until the main transaction commits. Exceptions raised in an autonomous transaction cause a … transaction-level

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Transactions Concepts

34 Distributed Transactions ConceptsIn this chapter: What Are Distributed Transactions? Session Trees for Distributed Transactions Two … -Phase Commit Mechanism In-Doubt Transactions Distributed Transaction Processing: Case Study

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Transactions Concepts

In-Doubt Transactionsbecomes in-doubt. Distributed transactions can become in-doubt in the following ways: A server … process automatically resolves in-doubt transactions when the system, network, or software problem is … . This section contains the following topics: Automatic Resolution of In-Doubt Transactions Manual … Resolution of In-Doubt Transactions

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Transactions Concepts

What Are Distributed Transactions?A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database. For example, assume the database configuration depicted in Figure 34-1:

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

35 Managing Distributed TransactionsIn this chapter: Specifying the Commit Point Strength of a Node Naming Transactions Viewing … Information About Distributed Transactions Deciding How to Handle In-Doubt Transactions Manually Overriding … In-Doubt Transactions Purging Pending Rows from the Data Dictionary Manually Committing an In-Doubt

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Static SQL

Controlling Autonomous Transactionsrollback comprise the current transaction. To control autonomous transactions, use these statements … Avoiding Errors with Autonomous Transactions Entering and Exiting When you enter the executable … explicitly commit or roll back all autonomous transactions. If the routine (or any routine invoked … by it) has pending transactions,

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Static SQL

Declaring Autonomous TransactionsTo declare an autonomous transaction, use the AUTONOMOUS_TRANSACTION pragma. For information about this pragma, see \"AUTONOMOUS_TRANSACTION Pragma\". Tip: For readability, put the AUTONOMOUS_TRANSACTION pragma at the top of the declarative section. (The pragma is allowed anywhere in the declarative section.) You cannot apply the AUTONOMOUS_TRANSACTION pragma to an entire package or ADT, but you can

Database Concepts, 11g Release 2 (11.2)

Transactions

Sample Transaction: Account Debit and CreditTo illustrate the concept of a transaction, consider a banking database. When a customer transfers money from a savings account to a checking account, the transaction must consist of three separate operations: Decrement the savings account Increment the checking account Record the transaction in the transaction journal Oracle Database must allow for two situations. If all three SQL statements maintain

Database Concepts, 11g Release 2 (11.2)

Transactions

System Change Numbers (SCNs)A system change number (SCN) is a logical, internal time stamp used by Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction. Oracle Database uses SCNs to mark the SCN before which all changes are known to be on disk so that recovery avoids applying unnecessary redo. The database also uses SCNs to mark the point at which

Database Concepts, 11g Release 2 (11.2)

Transactions

Committing Transactionsbehavior is for LGWR to write redo to the online redo log synchronously and for transactions to wait … transactions need not wait for the redo to be on disk and can return from the COMMIT call immediately

Database Concepts, 11g Release 2 (11.2)

Transactions

Table 10-1 Transaction ControlTime Session Explanation t0 COMMIT; This statement ends any existing transaction in the session. t1 SET TRANSACTION NAME 'sal_update'; This statement begins a transaction and names it sal_update. t2 UPDATE employees SET salary = 7000 WHERE last_name = 'Banda'; This statement updates the salary for Banda to 7000. t3 SAVEPOINT after_banda_sal; This statement creates a savepoint named after_banda_sal,

Database Concepts, 11g Release 2 (11.2)

Transactions

Active TransactionsAn active transaction has started but not yet committed or rolled back. In Table 10-1, the first statement to modify data in the sal_update transaction is the update to Banda's salary. From the successful execution of this update until the ROLLBACK statement ends the transaction, the sal_update transaction is active. Data changes made by a transaction are temporary until the transaction is committed

Database Concepts, 11g Release 2 (11.2)

Transactions

Overview of Transaction Controlstatements into transactions. In general, application designers are concerned with transaction control

Database Concepts, 11g Release 2 (11.2)

Transactions

Transaction Namesadvantages: It is easier to monitor long-running transactions and to resolve in-doubt distributed … transactions. You can view transaction names along with transaction IDs in applications. For example, a

Database Concepts, 11g Release 2 (11.2)

Transactions

Rollback of TransactionsA 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

Database Concepts, 11g Release 2 (11.2)

Transactions

Overview of Autonomous Transactions… Autonomous transactions are useful for actions that must be performed independently, regardless of whether … . Autonomous transactions have the following characteristics: The autonomous transaction does not see … transaction. Changes in an autonomous transaction are visible to other transactions upon commit of the

Database Concepts, 11g Release 2 (11.2)

Transactions

10 TransactionsThis chapter defines a transaction and describes how the database processes transactions. This … chapter contains the following sections: Introduction to Transactions Overview of Transaction Control … Overview of Autonomous Transactions Overview of Distributed Transactions

Database Concepts, 11g Release 2 (11.2)

Transactions

Statement-Level AtomicityOracle Database supports statement-level atomicity, which means that a SQL statement is an atomic unit of work and either completely succeeds or completely fails. A successful statement is different from a committed transaction. A single SQL statement executes successfully if the database parses and runs it without error as an atomic unit, as when all rows are changed in a multirow update. If a SQL

Database Concepts, 11g Release 2 (11.2)

Transactions

Savepointslearn about transaction processing and control Enqueued Transactions Depending on the scenario … , transactions waiting for previously locked resources may still be blocked after a rollback to savepoint

Database Concepts, 11g Release 2 (11.2)

Transactions

Table 10-2 Rollback to Savepoint ExampleTime Session 1 Session 2 Session 3 Explanation t0 UPDATE employees SET salary = 7000 WHERE last_name = 'Banda'; Session 1 begins a transaction. The session places an exclusive lock on the Banda row (TX) and a subexclusive table lock (SX) on the table. t1 SAVEPOINT after_banda_sal;     Session 1 creates a savepoint named after_banda_sal. t2 UPDATE employees SET salary = 12000 WHERE last_name = 'Greene';

Database Concepts, 11g Release 2 (11.2)

Transactions

Overview of Distributed Transactionsin transactions AT1 and AT2 have no effect on the MT transaction. See Also: Oracle Database Advanced … autonomous transactions Overview of Distributed Transactions A distributed database is a set of

Database Concepts, 11g Release 2 (11.2)

Transactions

Introduction to Transactionsassigns every transaction a unique identifier called a transaction ID. All Oracle transactions comply … partial transactions. For example, if a transaction starts updating 100 rows, but the system fails … effect of a transaction is not visible to other transactions until the transaction is committed. For … made concurrently by

Database Concepts, 11g Release 2 (11.2)

Transactions

Structure of a Transactiontransactions before program termination. A client process terminates abnormally, causing the transaction

Database Concepts, 11g Release 2 (11.2)

Transactions

In-Doubt Transactionsof in-doubt distributed transactions. After the failure is repaired and communication is … distributed transactions consistently on all involved nodes. In the event of a long-term failure … transactions that are in doubt because of the failure. This option enables the local database administrator to … Administrator's Guide to

Database Concepts, 11g Release 2 (11.2)

Transactions

Two-Phase Committransactions. In fact, users need not even know the transaction is distributed. A COMMIT statement … complex statement syntax is required to include distributed transactions within the body of a database

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Manually Overriding In-Doubt TransactionsUse the COMMIT or ROLLBACK statement with the FORCE option and a text string that indicates either the local or global transaction ID of the in-doubt transaction to commit. Note: In all examples, the transaction is committed or rolled back on the local node, and the local pending transaction table records a value of forced commit or forced termination for the STATE column the row for this transaction.

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Error Handling

Retrying Transactions After Handling ExceptionsTo retry a transaction after handling an exception that it raised, use this technique: Enclose the transaction in a sub-block that has an exception-handling part. In the sub-block, before the transaction starts, mark a savepoint. In the exception-handling part of the sub-block, put an exception handler that rolls back to the savepoint and then tries to correct the problem. Put the sub-block inside

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Viewing Information About Distributed TransactionsThe data dictionary of each database stores information about all open distributed transactions … . You can use data dictionary tables and views to gain information about the transactions. This section … contains the following topics: Determining the ID Number and Status of Prepared Transactions Tracing the Session Tree of In-Doubt Transactions

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Transactions Concepts

Session Trees for Distributed TransactionsAs the statements in a distributed transaction are issued, the database defines a session tree of all nodes participating in the transaction. A session tree is a hierarchical model that describes the relationships among sessions and their roles. Figure 34-2 illustrates a session tree:

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Locks from In-Doubt TransactionsA query or DML statement that requires locks on a local database can be blocked indefinitely due to the locked resources of an in-doubt distributed transaction. In this case, the database issues the following error message: ORA-01591: lock held by in-doubt distributed transaction identifier In this case, the database rolls back the SQL statement immediately. The user who executed the statement can

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Transactions Concepts

Global CoordinatorThe node where the distributed transaction originates is called the global coordinator. The database application issuing the distributed transaction is directly connected to the node acting as the global coordinator. For example, in Figure 34-2, the transaction issued at the node sales references information from the database servers warehouse and finance. Therefore, sales.example.com is the global

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Transactions Concepts

Commit PhaseThe second phase in committing a distributed transaction is the commit phase. Before this phase occurs, all nodes other than the commit point site referenced in the distributed transaction have guaranteed that they are prepared, that is, they have the necessary resources to commit the transaction. Steps in the Commit Phase The commit phase consists of the following steps: The global coordinator instructs

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Transactions Concepts

Forget PhaseAfter the participating nodes notify the commit point site that they have committed, the commit point site can forget about the transaction. The following steps occur: After receiving notice from the global coordinator that all nodes have committed, the commit point site erases status information about this transaction. The commit point site informs the global coordinator that it has erased the status

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Step 4: Querying Data Dictionary Views on All Nodesreturned when Steps 2 and 3 are performed at sales and hq. Checking the Status of Pending TransactionsTransactions at HQ At this stage, the hq administrator queries the DBA_2PC_PENDING data dictionary view: SELECT

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Disabling and Enabling RECOinvolving distributed transactions. At exponentially growing time intervals, the RECO background process of … . When a connection is established, RECO automatically resolves all in-doubt transactions. Rows … corresponding to any resolved in-doubt transactions are automatically removed from the pending … statement enables RECO

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Transactions Concepts

ClientsA node acts as a client when it references information from a database on another node. The referenced node is a database server. In Figure 34-2, the node sales is a client of the nodes that host the warehouse and finance databases.

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Transactions Concepts

Relevance of System Change Numbers for In-Doubt Transactionsdatabase uses SCNs to coordinate distributed transactions among different databases. For example, the … for distributed transactions because they function as a synchronized commit timestamp of a transaction

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Transactions Concepts

Stage 5: Commit Point Site Informs Global Coordinator of CommitThis stage involves the following steps: The commit point site tells the global coordinator that the transaction has committed. Because the commit point site and global coordinator are the same node in this example, no operation is required. The commit point site knows that the transaction is committed because it recorded this fact in its online log. The global coordinator confirms that the transaction

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Tracing the Session Tree of In-Doubt TransactionsThe following view shows which in-doubt transactions are incoming from a remote client and which … ) and outgoing (to remote server) in-doubt distributed transactions. It also indicates whether the

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Step 2: Query DBA_2PC_PENDINGAfter connecting with SQL*Plus to warehouse, query the local DBA_2PC_PENDING data dictionary view to gain information about the in-doubt transaction: CONNECT SYS@warehouse.example.com AS SYSDBA SELECT * FROM DBA_2PC_PENDING WHERE LOCAL_TRAN_ID = '1.21.17'; The database returns the following information: Column Name Value ---------------------- -------------------------------------- LOCAL_TRAN_ID 1.21.17

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Managing Read ConsistencyAn important restriction exists in the Oracle Database implementation of distributed read consistency. The problem arises because each system has its own SCN, which you can view as the database internal timestamp. The Oracle Database server uses the SCN to decide which version of data is returned from a query. The SCNs in a distributed transaction are synchronized at the end of each remote SQL statement

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Transactions Concepts

Transaction Control StatementsThe following are the supported transaction control statements: COMMIT ROLLBACK SAVEPOINT See Also: Oracle Database SQL Language Reference for more information about these SQL statements

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Transactions Concepts

Stage 1: Client Application Issues DML StatementsAt the Sales department, a salesperson uses SQL*Plus to enter a sales order and then commit it. The application issues several SQL statements to enter the order into the sales database and update the inventory in the warehouse database: CONNECT scott@sales.example.com...; INSERT INTO orders...; UPDATE inventory@warehouse.example.com...; INSERT INTO orders...; UPDATE inventory@warehouse.example.com...;

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Transactions Concepts

Stage 6: Global and Local Coordinators Tell All Nodes to CommitThe committing of the transaction by all the nodes in the transaction involves the following steps: After the global coordinator has been informed of the commit at the commit point site, it tells all other directly referenced nodes to commit. In turn, any local coordinators instruct their servers to commit, and so on. Each node, including the global coordinator, commits the transaction and records

Database Administrator's Guide, 11g Release 2 (11.2)

Distributed Transactions Concepts

Stage 7: Global Coordinator and Commit Point Site Complete the CommitFigure 34-10 Instructing Nodes to Commit Description of \"Figure 34-10 Instructing Nodes to Commit\" Stage 7: Global Coordinator and Commit Point Site Complete the Commit The completion of the commit of the transaction occurs in the following steps: After all referenced nodes and the global coordinator have committed the transaction, the global coordinator informs the commit point site of this fact.

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Specifying the Commit Point Strength of a NodeThe database with the highest commit point strength determines which node commits first in a distributed transaction. When specifying a commit point strength for each node, ensure that the most critical server will be non-blocking if a failure occurs during a prepare or commit phase. The COMMIT_POINT_STRENGTH initialization parameter determines the commit point strength of a node. The default value

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Table 35-2 DBA_2PC_NEIGHBORS, the node is the global coordinator of the transaction. IN_OUT IN for incoming transactions; OUT for … outgoing transactions. DATABASE For incoming transactions, the name of the client database that … requested information from this local node; for outgoing transactions, the name of the database link used … to

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Determining When to Use DBMS_TRANSACTIONThe following tables indicates what the various states indicate about the distributed transaction what the administrator's action should be: STATE Column State of Global Transaction State of Local Transaction Normal Action Alternative Action Collecting Rolled back Rolled back None PURGE_LOST_DB_ENTRY (only if autorecovery cannot resolve transaction) Committed Committed Committed None PURGE_LOST_DB_ENTRY

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Manually Committing an In-Doubt Transaction: Examplecritical to other transactions. Further, the locks must be held until the in-doubt transaction either commits or rolls back.

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Step 3: Query DBA_2PC_NEIGHBORS on Local NodeThe purpose of this step is to climb the session tree so that you find coordinators, eventually reaching the global coordinator. Along the way, you may find a coordinator that has resolved the transaction. If not, you can eventually work your way to the commit point site, which will always have resolved the in-doubt transaction. To trace the session tree, query the DBA_2PC_NEIGHBORS view on each node.

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Table 35-1 DBA_2PC_PENDINGcommitted. HOST Name of the host system. COMMIT# Global commit number for committed transactions

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Manually Rolling Back an In-Doubt TransactionBefore attempting to roll back the in-doubt distributed transaction, ensure that you have the proper privileges. Note the following requirements: User Committing the Transaction Privilege Required You FORCE TRANSACTION Another user FORCE ANY TRANSACTION The following SQL statement rolls back an in-doubt transaction: ROLLBACK FORCE 'transaction_id'; The variable transaction_id is the identifier of

Database Administrator's Guide, 11g Release 2 (11.2)

Managing Distributed Transactions

Step 1: Record User Feedbackreport problems to identify which in-doubt transactions should be forced.





Was this page helpful?

Integrated Cloud Applications & Platform Services