19.Using Two-Phase Commits (TPC)
The python-oracledb functionstpc_begin() andtpc_end() support distributed transactions. SeeTwo-PhaseCommit Mechanism in the Oracle Databasedocumentation.
The methodConnection.tpc_begin() can be used to start a TPCtransaction.
Distributed transaction protocols attempt to keep multiple data sourcesconsistent with one another by ensuring updates to the data sourcesparticipating in a distributed transaction are all performed, or none ofthem performed. These data sources, also called participants or resourcemanagers, may be traditional database systems, messaging systems, andother systems that store state such as caches. A common class ofdistributed transaction protocols are referred to as two-phase commitprotocols. These protocols split the commitment of a distributedtransaction into two distinct, separate phases.
During the first phase, the participants (data sources) are polled orasked to vote on the outcome of the distributed transaction. This phase,called the prepare phase, ensures agreement or consensus on the abilityfor each participant to commit their portion of the transaction. Whenasked to prepare, the participants respond positively if they can committheir portion of the distributed transaction when requested or respondthat there were no changes, so they have no need to be committed. Onceall participants have responded to the first phase, the second phase ofthe protocol can begin. The methodConnection.tpc_prepare() canbe used to prepare a global transaction for commit.
During the second phase of the protocol, called the commit phase, all ofthe participants that indicated they needed to be committed are asked toeither commit their prepared changes or roll them back. If the decision onthe outcome of the distributed transaction was to commit the transaction,each participant is asked to commit their changes. If the decision was toabort or rollback the distributed transaction, each participant is askedto rollback their changes. The methodsConnection.tpc_commit() andConnection.tpc_rollback() can be used to commit or rollback atransaction respectively.
While applications can coordinate these activities, it takes on additionalresponsibilities to ensure the correct outcome of all participants, even inthe face of failures. These failures could be of the application itself, oneof the participants in the transaction, of communication links, etc. In orderto assure the atomic characteristics of a distributed transaction, once thedecision has been made to commit the distributed transaction, this decisionneeds to be durably recorded in case of failure. The application, as part ofits steps for recovery from a failure, now needs to check the durable log andnotify the participants of the outcome. Failures may be nested such that notonly might the application fail, one or more participants or connections toparticipants might fail. All these scenarios require careful considerationand remediation to ensure that all participants either committed or rolledback their local updates.
As a result, most applications rely upon the services provided by atransaction manager (TM), also called a transaction coordinator. The purposeof having a transaction manager perform this coordination is to eliminatehaving to have each application perform these transaction management functions.The application asks the transaction manager to start a transaction. Asadditional participants or resource managers join the transaction, they registerwith the transaction manager as participants. When the original application decidesthe transaction is to be committed or rolled back, it asks the transaction managerto commit or rollback the transaction. If the application asked the transaction tobe rolled back, the transaction coordinator notifies all participants to roll back.Otherwise, the transaction manager then starts the two-phase commit protocol.
The following example shows how to perform an application level two-phase commit:
importoracledb# connect to first database and begin transactionconn1=oracledb.connect(DSN1)xid1=conn1.xid(1000,"txn1","branch1")conn1.tpc_begin(xid1)withconn1.cursor()ascursor:cursor.execute("insert into SomeTable values (1, 'Some value')")# connect to second database and begin transactionconn2=oracledb.connect(DSN2)xid2=conn1.xid(1000,"txn1","branch2")conn2.tpc_begin(xid2)withconn2.cursor()ascursor:cursor.execute("insert into SomeOtherTable values (2, 'Some value')")# prepare both transactions and commitcommit_needed1=conn1.tpc_prepare()commit_needed2=conn2.tpc_prepare()ifcommit_needed1:conn1.tpc_commit()ifcommit_needed2:conn2.tpc_commit()
The following example shows how to perform recovery.
importoracledbwithoracledb.connect(DSN,mode=oracledb.SYSDBA)asconn:forxidinconn.tpc_recover():print("Recovering xid by rolling it back:",xid)conn.tpc_rollback(xid)