Summary of Oracle distributed transactions

Summary of Oracle distributed transactions ()

Basic Concepts

Local Coordinator: in distributed transactions, you must refer to the data on other nodes to complete this part of the site.

Global Coordinator: The initiator of the distributed transaction, responsible for coordinating the distributed transaction.

Commit point site: in a distributed transaction, the site that first executes the commit or rollback operation. Generally, the site that stores key data should be used as the commit point site. Because the commit point site is different from other sites and never enters the prepared State, there is no in-doubt transaction.

You can set the initialization parameter commit_point_strength. In a distributed transaction, the commit point site is determined based on the size of the value. The status information of the distributed transaction also exists in the database. Generally, the key database is used as the commit point site, and the database with a high value of commit_point_strength is the commit point site. In distributed transactions, the key database is submitted first.

Three stages of distributed submission

Two-phase commit of distributed transactions involves three processes:

1. preparation phase (prepare phase)

· The local database Global Coordinator sends a commit notification to other databases

· Compare the SCN numbers of all databases and use the highest SCN number as the global SCN Number of distributed transactions

· Write online logs to all databases

· Add a distribution lock to the table where the distributed transaction is modified to prevent reading and writing

· Databases send prepared notifications to Global Coordinator

All databases involved in distributed transactions must undergo the above preparations before entering the next stage.

2. Commit phase (commit phase)

· The local database Global Coordinator notifies the commit point site to be submitted first. After the commit point site is submitted, release the resources it occupies and notify global coordinator to complete the submission.

· Local database Global Coordinator notifies other databases to submit

· The submit node adds a message to the log, indicating that the distributed transaction has been submitted and the global coordinator is notified. At this time, the data of all databases remains consistent.

3. Forget phase)

· Local database Global Coordinator notifies commit point site that all databases have been submitted

· Commit point site clears records and status information of distributed transactions and notifies Global Coordinator

· Global Coordinator clears records and status information of local distributed transactions

At this time, the two-phase commit of distributed transactions is complete.

If an exception occurs in the database or network before the two-phase commit is completed, the application reports an error and the distributed transaction is in the in_doubt state. Once the database or network returns to normal, the system (reco process) automatically processes the distributed items in the in_doubt state. In some cases, the administrator needs to manually process the distributed items in the in_doubt state:

· Distributed transactions in the in _ doubt state lock key tables, resulting in abnormal application operations

Two important views

Dba_2pc_pending: lists all pending transactions. This view is empty before the pending transactions are filled in at the end, and is cleared after being resolved.

Local_tran_id

The identifier of a local transaction in the format of integer. Integer. ingeger.

When the local_tran_id and global_tran_id of a connection are the same, the node is the global coordinator of the transaction.

Global_tran_id

The global transaction identifier in the format of global_db_name.db_hex_id.local_tran_id. db_hex_id is the hexadecimal number used to identify the database 8 character. The common event IDs are the same in each node of the distributed transaction.

"Yes" means that some transactions have been committed on one node and rolled back on another node.

Tran_comment

The transaction annotation, or if the transaction name is used, the transaction name appears here when each transaction is committed.

Number of globally committed transactions

Description of the State column of dba_2pc_pending

Connecting

Generally, this entry is used only by the global coordinator and local coordinator. The Node collects information from other database services before determining whether the node can be prepared.

The node may or may not have notified the local coordinator of the prepared message. However, at this time, the node has not received the submitted request and remains in a ready state to control. to submit any local resources required by the firm.

The transaction has been committed by the node (of any type), but other nodes contained in the transaction may not be committed, that is, the transaction is still pending on one or more other nodes.

Forced commit

After determining, the DBA can forcibly submit outstanding transactions. If a transaction is manually submitted by the DBA on a local node, this project is generated.

Forced Abor (rollback)

After determining, the DBA can forcibly roll back outstanding transactions. If a transaction is manually rolled back by the DBA on a local node, this project is generated.

Dba_2pc_neighbors: lists all pending transactions (from remote customers) and sent (to remote servers). It also indicates whether the local node is the transaction commit site.

Local_tran_id

The name of the customer database whose local node information is obtained. The name of the database link used to access the information on the remote server.

Dbuser_owner

For a transaction, the local account used to connect the remote database link refers to the owner of the database link for sending the transaction.

Interface

'C' indicates the submission information. 'n' indicates a message in the prepared status or a read-only request.

When 'in _ out' is out, 'C' indicates that the remote site of the connection is submitted to the site, and whether the site is submitted or interrupted. 'N' indicates that the local node is notifying the remote node that it is ready.

When 'in _ out' is in, 'C' indicates that the local node or the remote database sent out is the site for submission. 'n' indicates that the local node is notifying the remote node it is ready.

General steps for handling hanging transactions

1. Check the alert file and find an error similar to the following:

ORA-1591 "lock held by in-doubt Distributed Transaction % s"

ORA-2062 "distributed recovery received dbid X, expected y"

ORA-2068 "following severe error from % S % s"

2. Check whether the network is normal, whether dblink is valid, and whether distributed transactions are currently used in V $ dblink and GV $ dblink.

3. query the view dba_2pc_pending and query the suspension transaction information:

Select local_tran_id, global_tran_id, state, mixed, host, commit #

From dba_2pc_pending

Where local_tran_id = '??. ';

If no record exists, the reco process has automatically processed the transaction.

4. query the view dba_2pc_neighbors on all nodes

5. Obtain the commit_point_strength value of all nodes. The maximum value is the commit point site, that is, the earliest submitted point. If the suspension transaction occurs at the commit point site, the State determines the state of the entire distributed transaction. Determines whether the transaction should be commit force or rollback force.

6. Check the State column of dba_2pc_pending. If it is commited, the local database has been submitted successfully. Other nodes need to force commit based on the local transaction number and the maximum commit. The usage is as follows:

Svrmgr> commit force 'your local transactionid on this node', 'highest SCN from already committed site ';

Svrmgr> commit force '1. 123', '123 ';

7. If the state of the commit point site is other than commited, it indicates that the commit point site has not been committed successfully, and the distributed transaction needs to be forcibly rolled back. The maximum commit # Of all nodes is no longer needed here #. The usage is as follows:

Svrmgr> rollback force 'your local transactionid on this node ';

Svrmgr> rollback force '1. 13.5197 ';

8. Clear related records of dba_2pc_pending and dba_2pc_neighbers. Generally, after a distributed transaction is automatically restored, the view content is automatically cleared. If a transaction is manually committed, you need to manually clear it using the dbms_transaction package. The clearing rules are shown in the following table:

Determine when dbms_transaction can be used

Collecting

Purge_lost_db_entry (only when the automatic reply fails to solve the transaction)

Committed

Committed

Committed

Purge_lost_db_entry (only when the automatic reply fails to solve the transaction)

Forced

Commit

Committed

Purge_lost_db_entry (only when the automatic reply fails to solve the transaction)

Forced rollback

Purge_lost_db_entry (only when the automatic reply fails to solve the transaction)

Forced commit

Committed

Manually delete inconsistencies and then use purge_mixed

Forced rollback

Manually delete inconsistencies and then use purge_mixed

Test records

In commit, set db1's commit_point_strength to 1, DB2's commit_point_strength to 2, and DB2 to commit point site.

Mongodb1 and DB2 execute 100 insert cycles. Each cycle inserts test tables in db1 and DB2 with distributed transactions. Intermediate reboot DB2 server. In this case, the following error occurs when db1 queries the test table:

SQL> select count (1) from temp. my_table;

Select count (1) from temp. my_table

*

Error at line 1:

ORA-01591: Lock held by in-doubt Distributed Transaction 7.30.7415

[Oracle @ DB2 bdump] $ tail-F alert_ntespay.log

Tue mar4 14:14:28 2008

Distrib Tran 1234.4f000000000000000000000000000054

Is local Tran 7.30.7415 (hex = 07.1e.1cf7)

Insert pending prepared Tran, SCN = 934346533 (hex = 0.37b0ff25)

The two views related to distributed transactions in db1 are as follows:

Select a. * From dba_2pc_pending A where local_tran_id = '7. 30.7415 ';

Local_tran_id global_tran_id state mixed advice tran_comment fail_time force_time retry_time OS _user OS _terminal host db_user commit #

1 7.30.7415 prepare prepared no 14:14:28 14:22:56 zhenxingzhai zhaizhenxing Netease/zhaizhenxing 934346533

Where,

State has the following states:

Collecting, prepared, committed, forced commit, or forced rollback

Mixed indicates whether to partially commit and partial rollback.

Advice:

C

For commit,

R

For rollback, else

Null

Select a. * From dba_2pc_neighbors A where local_tran_id = '7. 30.7415 ';

Local_tran_id in_out database dbuser_owner interface dbid sess # Branch

1 7.30.7415 in nulljavaxa.oracle.com temp n javaxa_orcl 1 01000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

This view describes the input connection information of data source 1. Because data source 2 is not connected through dblink, there is no record of it.

After DB2 is restarted, query my_tab:

SQL> select count (1) From my_tab;

Count (1)

----------

75

Because dba_2pc_pending and dba_2pc_neighbers in DB2 do not have records, and DB2 is a commit point site. If no records exist, it means that no operations are performed. Therefore, db1 should be forced rollback like DB2.

SQL> Conn/As sysdba

Connected.

SQL> rollback force '7. 30.7415 ';

Rollback complete.

SQL> select count (12) from temp. my_table;

Count (12)

----------

75

The Alert Log of db1 shows the rollback process of suspicious transactions:

Tue mar4 15:14:31 2008

Distrib Tran 1234.4f000000000000000000000000000054

Is local Tran 7.30.7415 (hex = 07.1e.1cf7)

Change pending prepared Tran, SCN = 934346533 (hex = 0.37b0ff25)

To pending forced rollback Tran, SCN = 934346533 (hex = 0.37b0ff25)

After rollback, the status of the two views is changed to the following:

Select a. * From dba_2pc_pending A where local_tran_id = '9. 33.5992 ';

Local_tran_id global_tran_id state mixed advice tran_comment fail_time force_time retry_time OS _user OS _terminal host db_user commit #

1 7.30.7415 explain forced rollback no 14:14:28 15:14:31 zhenxingzhai zhaizhenxing Netease/zhaizhenxing 934346533

Select a. * From dba_2pc_neighbors A where local_tran_id = '9. 33.5992 ';

Local_tran_id in_out database dbuser_owner interface dbid sess # Branch

1 7.30.7415 in nulljavaxa.oracle.com temp n javaxa_orcl 1 01000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

¡Remove the records in dba_2pc_pending and dba_2pc _ neighbors:

(1) Disable distributed recovery

SQL> alter system disable distributed recovery;

System altered.

(2) puege (empty) in-doubt transaction entry:

SQL> exec dbms_transaction.purge_lost_db_entry ('7. 30.7415 ');

PL/SQL procedure successfully completed.

(3) commit;

(4) Enable distributed recovery:

SQL> alter system enable distributed recovery;

Distributed Transaction-related materials

Note: 1012842.102

Note: 100664.1

Note: 274321.1

Note: 126069.1

Http://www.itk.ilstu.edu/docs/Oracle/server.101/b10739/ds_txns.htm#i1007721