This section describes the transaction functions.
Table 16-10 Transaction Functions
Function | Purpose |
---|---|
Commit a transaction on a service context |
|
Detach a transaction from a service context |
|
Forget a prepared global transaction |
|
Prepare a transaction with multiple branches in a single cell |
|
Prepare a global transaction for commit |
|
Roll back a transaction |
|
Start a transaction on a service context |
Commits the transaction associated with a specified service context.
sword OCITransCommit ( OCISvcCtx *svchp, OCIError *errhp, ub4 flags );
The service context handle.
An error handle you can pass to OCIErrorGet()
for diagnostic information in the event of an error.
A flag used for one-phase commit optimization in global transactions.
OCI_DEFAULT
- If the transaction is non-distributed, the flags parameter is ignored, and OCI_DEFAULT
can be passed as its value.
OCI_TRANS_TWOPHASE
- OCI applications managing global transactions should pass this value to the flags parameter for a two-phase commit. The default is one-phase commit.
OCI_TRANS_WRITEIMMED
- I/O is initiated by LGWR (Log Writer Process in the background) to write the (in-memory) redo buffers to the online redo logs. IMMEDIATE
means that the redo buffers of the transaction are written out immediately by sending a message to the LGWR, which processes the message immediately.
OCI_TRANS_WRITEBATCH
- No I/O is issued by LGWR to write the in-memory redo buffers of the transaction to the online redo logs. BATCH
means that the LGWR batches the redo buffers before initiating I/O for the entire batch. An error occurs when you specify both BATCH
and IMMEDIATE
. IMMEDIATE
is the default.
OCI_TRANS_WRITEWAIT
- LGWR is requested to write the redo for the commit to the online redo logs and the commit waits for the redo buffers to be written to the online redo logs. WAIT
means that the commit does not return until the in-memory redo buffers corresponding to the transaction are written in the (persistent) online redo logs.
OCI_TRANS_WRITENOWAIT
- LGWR is requested to write the redo for the commit to the online redo logs, but the commit returns without waiting for the buffers to be written to the online redo logs. NOWAIT
means that the commit returns to the user before the in-memory redo buffers are flushed to the online redo logs. An error occurs when you specify both WAIT
and NOWAIT
. WAIT
is the default.
These last four options only affect the commit of top-level non-distributed transactions and are ignored for externally coordinated distributed transactions. They can be combined using the OR
operator, subject to the stated restrictions.
The transaction currently associated with the service context is committed. If it is a global transaction that the server cannot commit, this call additionally retrieves the state of the transaction from the database to be returned to the user in the error handle.
If the application has defined multiple transactions, this function operates on the transaction currently associated with the service context. If the application is working with only the implicit local transaction created when database changes are made, that implicit transaction is committed.
If the application is running in the object mode, then the modified or updated objects in the object cache for this transaction are also flushed and committed.
Under normal circumstances, OCITransCommit()
returns with a status indicating that the transaction has either been committed or rolled back. With global transactions, it is possible that the transaction is now in-doubt, meaning that it is neither committed nor terminated. In this case, OCITransCommit()
attempts to retrieve the status of the transaction from the server. The status is returned.
The following example demonstrates the use of a simple local transaction, as described in the section "Simple Local Transactions".
int main() { OCIEnv *envhp; OCIServer *srvhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; dvoid *tmp; text sqlstmt[128]; OCIEnvCreate(&envhp, OCI_DEFAULT, (dvoid *)0, 0, 0, 0, (size_t)0, (dvoid *)0); OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, (ub4) OCI_HTYPE_ERROR, (size_t)0, (dvoid **) 0); OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, (ub4) OCI_HTYPE_SERVER, (size_t)0, (dvoid **) 0); OCIServerAttach( srvhp, errhp, (text *) 0, (sb4) 0, (ub4) OCI_DEFAULT); OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, (ub4) OCI_HTYPE_SVCCTX, (size_t)0, (dvoid **) 0); OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0); OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp, 0, OCI_ATTR_SERVER, errhp); OCILogon(envhp, errhp, &svchp, (text *)"HR", strlen("HR"), (text *)"HR", strlen("HR"), 0, 0); /* update hr.employees employee_id=7902, increment salary */ sprintf((char *)sqlstmt, "UPDATE EMPLOYEES SET SALARY = SALARY + 1 \ WHERE EMPLOYEE_ID = 7902"); OCIStmtPrepare(stmthp, errhp, sqlstmt, strlen((char *)sqlstmt), OCI_NTV_SYNTAX, 0); OCIStmtExecute(svchp, stmthp, errhp, 1, 0, 0, 0, 0); OCITransCommit(svchp, errhp, (ub4) 0); /* update hr.employees employee_id=7902, increment salary again, but rollback */ OCIStmtExecute(svchp, stmthp, errhp, 1, 0, 0, 0, 0); OCITransRollback(svchp, errhp, (ub4) 0); }
Detaches a transaction.
sword OCITransDetach ( OCISvcCtx *svchp, OCIError *errhp, ub4 flags );
The service context handle.
An error handle you can pass to OCIErrorGet()
for diagnostic information in the event of an error.
You must pass a value of OCI_DEFAULT
for this parameter.
Detaches a global transaction from the service context handle. The transaction currently attached to the service context handle becomes inactive at the end of this call. The transaction may be resumed later by calling OCITransStart()
, specifying a flags value of OCI_TRANS_RESUME
.
When a transaction is detached, the value which was specified in the timeout parameter of OCITransStart()
when the transaction was started is used to determine the amount of time the branch can remain inactive before being deleted by the server's PMON process.
Note:
The transaction can be resumed by a different process than the one that detached it, if the transaction has the same authorization. If this function is called before a transaction is actually started, this function is a no-op.For example code demonstrating the use of OCITransDetach()
see the description of OCITransStart().
Causes the server to forget a heuristically completed global transaction.
sword OCITransForget ( OCISvcCtx *svchp, OCIError *errhp, ub4 flags );
The service context handle in which the transaction resides.
An error handle you can pass to OCIErrorGet()
for diagnostic information in the event of an error.
You must pass OCI_DEFAULT
for this parameter.
Forgets a heuristically completed global transaction. The server deletes the status of the transaction from the system's pending transaction table.
You set the XID of the transaction to be forgotten as an attribute of the transaction handle (OCI_ATTR_XID
).
OCITransCommit(), OCITransRollback()
Prepares a transaction with multiple branches in a single call.
sword OCITransMultiPrepare ( OCISvcCtx *svchp, ub4 numBranches, OCITrans **txns, OCIError **errhp);
The service context handle.
The number of branches expected. It is also the array size for the next two parameters.
The array of transaction handles for the branches to prepare. They should all have the OCI_ATTR_XID
set. The global transaction ID should be the same.
The array of error handles. If OCI_SUCCESS
is not returned, then these will indicate which branches received which errors.
Prepares the specified global transaction for commit. This call is valid only for distributed transactions. This call is an advanced performance feature intended for use only in situations where the caller is responsible for preparing all the branches in a transaction.
Prepares a transaction for commit.
sword OCITransPrepare ( OCISvcCtx *svchp, OCIError *errhp, ub4 flags );
The service context handle.
An error handle you can pass to OCIErrorGet()
for diagnostic information in the event of an error.
You must pass OCI_DEFAULT
for this parameter.
Prepares the specified global transaction for commit.
This call is valid only for global transactions.
The call returns OCI_SUCCESS_WITH_INFO
if the transaction has not made any changes. The error handle will indicate that the transaction is read-only. The flag parameter is not currently used.
OCITransCommit(), OCITransForget()
Rolls back the current transaction.
sword OCITransRollback ( dvoid *svchp, OCIError *errhp, ub4 flags );
A service context handle. The transaction currently set in the service context handle is rolled back.
An error handle you can pass to OCIErrorGet()
for diagnostic information in the event of an error.
You must pass a value of OCI_DEFAULT
for this parameter.
The current transaction— defined as the set of statements executed since the last OCITransCommit()
or since OCISessionBegin()
—is rolled back.
If the application is running under object mode then the modified or updated objects in the object cache for this transaction are also rolled back.
Attempting to roll back a global transaction that is not currently active causes an error.
For example code demonstrating the use of OCITransRollback()
see the description of OCITransCommit().
Sets the beginning of a transaction.
sword OCITransStart ( OCISvcCtx *svchp, OCIError *errhp, uword timeout, ub4 flags );
The service context handle. The transaction context in the service context handle is initialized at the end of the call if the flag specified a new transaction to be started.
The OCI error handle. If there is an error, it is recorded in err
and this function returns OCI_ERROR
. Diagnostic information can be obtained by calling OCIErrorGet()
.
The time, in seconds, to wait for a transaction to become available for resumption when OCI_TRANS_RESUME
is specified. When OCI_TRANS_NEW
is specified, the timeout parameter indicates the number of seconds the transaction can be inactive before it is automatically terminated by the system. A transaction is inactive between the time it is detached (with OCITransDetach()) and the time it is resumed with OCITransStart()
.
Specifies whether a new transaction is being started or an existing transaction is being resumed. Also specifies serializiability or read-only status. More than a single value can be specified. By default, a read/write transaction is started. The flag values are:
OCI_TRANS_NEW
- starts a new transaction branch. By default starts a tightly coupled and migratable branch.
OCI_TRANS_TIGHT
- explicitly specifies a tightly coupled branch
OCI_TRANS_LOOSE
- specifies a loosely coupled branch
OCI_TRANS_RESUME
- resumes an existing transaction branch.
OCI_TRANS_READONLY
- start a read-only transaction
OCI_TRANS_SERIALIZABLE
- start a serializable transaction
OCI_TRANS_SEPARABLE
- the transaction will be separated after each call.
This flag results in a warning that the transaction was started using regular transactions. Separated transactions are not supported through release 9.0.1 of the server.
An error message results if there is an error in your code or the transaction service. The error indicates that you attempted an action on a transaction that has already been prepared.
This function sets the beginning of a global or serializable transaction. The transaction context currently associated with the service context handle is initialized at the end of the call if the flags parameter specifies that a new transaction should be started.
The XID of the transaction is set as an attribute of the transaction handle (OCI_ATTR_XID
)
The following examples demonstrate the use of OCI transactional calls for manipulating global transactions.
This concept is illustrated by Figure 8-2, "Session Operating on Multiple Branches".
int main() { OCIEnv *envhp; OCIServer *srvhp; OCIError *errhp; OCISvcCtx *svchp; OCISession *usrhp; OCIStmt *stmthp1, *stmthp2; OCITrans *txnhp1, *txnhp2; dvoid *tmp; XID gxid; text sqlstmt[128]; OCIEnvCreate(&envhp, OCI_DEFAULT, (dvoid *)0, 0, 0, 0, (size_t)0, (dvoid *)0); OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, (ub4) OCI_HTYPE_ERROR, 52, (dvoid **) &tmp); OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, (ub4) OCI_HTYPE_SERVER, 52, (dvoid **) &tmp); OCIServerAttach( srvhp, errhp, (text *) 0, (sb4) 0, (ub4) OCI_DEFAULT); OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, (ub4) OCI_HTYPE_SVCCTX, 52, (dvoid **) &tmp); OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp1, OCI_HTYPE_STMT, 0, 0); OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp2, OCI_HTYPE_STMT, 0, 0); OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp, 0, OCI_ATTR_SERVER, errhp); /* set the external name and internal name in server handle */ OCIAttrSet((dvoid *)srvhp, OCI_HTYPE_SERVER, (dvoid *) "demo", 0, OCI_ATTR_EXTERNAL_NAME, errhp); OCIAttrSet((dvoid *)srvhp, OCI_HTYPE_SERVER, (dvoid *) "txn demo", 0, OCI_ATTR_INTERNAL_NAME, errhp); /* allocate a user context handle */ OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp, (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0); OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION, (dvoid *)"HR", (ub4)strlen("HR"), OCI_ATTR_USERNAME, errhp); OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION, (dvoid *)"HR", (ub4)strlen("HR"),OCI_ATTR_PASSWORD, errhp); OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS, 0); OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX, (dvoid *)usrhp, (ub4)0, OCI_ATTR_SESSION, errhp); /* allocate transaction handle 1 and set it in the service handle */ OCIHandleAlloc((dvoid *)envhp, (dvoid **)&txnhp1, OCI_HTYPE_TRANS, 0, 0); OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)txnhp1, 0, OCI_ATTR_TRANS, errhp); /* start a transaction with global transaction id = [1000, 123, 1] */ gxid.formatID = 1000; /* format id = 1000 */ gxid.gtrid_length = 3; /* gtrid = 123 */ gxid.data[0] = 1; gxid.data[1] = 2; gxid.data[2] = 3; gxid.bqual_length = 1; /* bqual = 1 */ gxid.data[3] = 1; OCIAttrSet((dvoid *)txnhp1, OCI_HTYPE_TRANS, (dvoid *)&gxid, sizeof(XID), OCI_ATTR_XID, errhp); /* start global transaction 1 with 60 second time to live when detached */ OCITransStart(svchp, errhp, 60, OCI_TRANS_NEW); /* update hr.employees employee_id=7902, increment salary */ sprintf((char *)sqlstmt, "UPDATE EMPLOYEES SET SALARY = SALARY + 1 \ WHERE EMPLOYEE_ID = 7902"); OCIStmtPrepare(stmthp1, errhp, sqlstmt, strlen((char *)sqlstmt), OCI_NTV_SYNTAX, 0); OCIStmtExecute(svchp, stmthp1, errhp, 1, 0, 0, 0, 0); /* detach the transaction */ OCITransDetach(svchp, errhp, 0); /* allocate transaction handle 2 and set it in the service handle */ OCIHandleAlloc((dvoid *)envhp, (dvoid **)&txnhp2, OCI_HTYPE_TRANS, 0, 0); OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)txnhp2, 0, OCI_ATTR_TRANS, errhp); /* start a transaction with global transaction id = [1000, 124, 1] */ gxid.formatID = 1000; /* format id = 1000 */ gxid.gtrid_length = 3; /* gtrid = 124 */ gxid.data[0] = 1; gxid.data[1] = 2; gxid.data[2] = 4; gxid.bqual_length = 1; /* bqual = 1 */ gxid.data[3] = 1; OCIAttrSet((dvoid *)txnhp2, OCI_HTYPE_TRANS, (dvoid *)&gxid, sizeof(XID), OCI_ATTR_XID, errhp); /* start global transaction 2 with 90 second time to live when detached */ OCITransStart(svchp, errhp, 90, OCI_TRANS_NEW); /* update hr.employees employee_id=7934, increment salary */ sprintf((char *)sqlstmt, "UPDATE EMPLOYEES SET SALARY = SALARY + 1 \ WHERE EMPLOYEE_ID = 7934"); OCIStmtPrepare(stmthp2, errhp, sqlstmt, strlen((char *)sqlstmt), OCI_NTV_SYNTAX, 0); OCIStmtExecute(svchp, stmthp2, errhp, 1, 0, 0, 0, 0); /* detach the transaction */ OCITransDetach(svchp, errhp, 0); /* Resume transaction 1, increment salary and commit it */ /* Set transaction handle 1 into the service handle */ OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)txnhp1, 0, OCI_ATTR_TRANS, errhp); /* attach to transaction 1, wait for 10 seconds if the transaction is busy */ /* The wait is clearly not required in this example because no other */ /* process/thread is using the transaction. It is only for illustration */ OCITransStart(svchp, errhp, 10, OCI_TRANS_RESUME); OCIStmtExecute(svchp, stmthp1, errhp, 1, 0, 0, 0, 0); OCITransCommit(svchp, errhp, (ub4) 0); /* attach to transaction 2 and commit it */ /* set transaction handle2 into the service handle */ OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)txnhp2, 0, OCI_ATTR_TRANS, errhp); OCITransCommit(svchp, errhp, (ub4) 0); }
Example 2 - A Single Session Operating On Multiple Branches That Share The Same Transaction.
int main() { OCIEnv *envhp; OCIServer *srvhp; OCIError *errhp; OCISvcCtx *svchp; OCISession *usrhp; OCIStmt *stmthp; OCITrans *txnhp1, *txnhp2; dvoid *tmp; XID gxid; text sqlstmt[128]; OCIEnvCreate(&envhp, OCI_DEFAULT, (dvoid *)0, 0, 0, 0, (size_t)0, (dvoid *)0); OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, (ub4) OCI_HTYPE_ERROR, 52, (dvoid **) &tmp); OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, (ub4) OCI_HTYPE_SERVER, 52, (dvoid **) &tmp); OCIServerAttach( srvhp, errhp, (text *) 0, (sb4) 0, (ub4) OCI_DEFAULT); OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, (ub4) OCI_HTYPE_SVCCTX, 52, (dvoid **) &tmp); OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0); OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp, 0, OCI_ATTR_SERVER, errhp); /* set the external name and internal name in server handle */ OCIAttrSet((dvoid *)srvhp, OCI_HTYPE_SERVER, (dvoid *) "demo", 0, OCI_ATTR_EXTERNAL_NAME, errhp); OCIAttrSet((dvoid *)srvhp, OCI_HTYPE_SERVER, (dvoid *) "txn demo2", 0, OCI_ATTR_INTERNAL_NAME, errhp); /* allocate a user context handle */ OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp, (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0); OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION, (dvoid *)"HR", (ub4)strlen("HR"), OCI_ATTR_USERNAME, errhp); OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION, (dvoid *)"HR", (ub4)strlen("HR"),OCI_ATTR_PASSWORD, errhp); OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS, 0); OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX, (dvoid *)usrhp, (ub4)0, OCI_ATTR_SESSION, errhp); /* allocate transaction handle 1 and set it in the service handle */ OCIHandleAlloc((dvoid *)envhp, (dvoid **)&txnhp1, OCI_HTYPE_TRANS, 0, 0); OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)txnhp1, 0, OCI_ATTR_TRANS, errhp); /* start a transaction with global transaction id = [1000, 123, 1] */ gxid.formatID = 1000; /* format id = 1000 */ gxid.gtrid_length = 3; /* gtrid = 123 */ gxid.data[0] = 1; gxid.data[1] = 2; gxid.data[2] = 3; gxid.bqual_length = 1; /* bqual = 1 */ gxid.data[3] = 1; OCIAttrSet((dvoid *)txnhp1, OCI_HTYPE_TRANS, (dvoid *)&gxid, sizeof(XID), OCI_ATTR_XID, errhp); /* start global transaction 1 with 60 second time to live when detached */ OCITransStart(svchp, errhp, 60, OCI_TRANS_NEW); /* update hr.employees employee_id=7902, increment salary */ sprintf((char *)sqlstmt, "UPDATE EMPLOYEES SET SALARY = SALARY + 1 \ WHERE EMPLOYEE_ID = 7902"); OCIStmtPrepare(stmthp, errhp, sqlstmt, strlen((char *)sqlstmt), OCI_NTV_SYNTAX, 0); OCIStmtExecute(svchp, stmthp, errhp, 1, 0, 0, 0, 0); /* detach the transaction */ OCITransDetach(svchp, errhp, 0); /* allocate transaction handle 2 and set it in the service handle */ OCIHandleAlloc((dvoid *)envhp, (dvoid **)&txnhp2, OCI_HTYPE_TRANS, 0, 0); OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)txnhp2, 0, OCI_ATTR_TRANS, errhp); /* start a transaction with global transaction id = [1000, 123, 2] */ /* The global transaction will be tightly coupled with earlier transaction */ /* There is not much practical value in doing this but the example */ /* illustrates the use of tightly-coupled transaction branches */ /* In a practical case the second transaction that tightly couples with */ /* the first can be executed from a different process/thread */ gxid.formatID = 1000; /* format id = 1000 */ gxid.gtrid_length = 3; /* gtrid = 123 */ gxid.data[0] = 1; gxid.data[1] = 2; gxid.data[2] = 3; gxid.bqual_length = 1; /* bqual = 2 */ gxid.data[3] = 2; OCIAttrSet((dvoid *)txnhp2, OCI_HTYPE_TRANS, (dvoid *)&gxid, sizeof(XID), OCI_ATTR_XID, errhp); /* start global transaction 2 with 90 second time to live when detached */ OCITransStart(svchp, errhp, 90, OCI_TRANS_NEW); /* update hr.employees employee_id=7902, increment salary */ /* This is possible even if the earlier transaction has locked this row */ /* because the two global transactions are tightly coupled */ OCIStmtExecute(svchp, stmthp, errhp, 1, 0, 0, 0, 0); /* detach the transaction */ OCITransDetach(svchp, errhp, 0); /* Resume transaction 1 and prepare it. This will return */ /* OCI_SUCCESS_WITH_INFO because all branches except the last branch */ /* are treated as read-only transactions for tightly-coupled transactions */ OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)txnhp1, 0, OCI_ATTR_TRANS, errhp); if (OCITransPrepare(svchp, errhp, (ub4) 0) == OCI_SUCCESS_WITH_INFO) { text errbuf[512]; ub4 buflen; sb4 errcode; OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR); printf("OCITransPrepare - %s\n", errbuf); } /* attach to transaction 2 and commit it */ /* set transaction handle2 into the service handle */ OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)txnhp2, 0, OCI_ATTR_TRANS, errhp); OCITransCommit(svchp, errhp, (ub4) 0); }