More OCI Relational Functions, 95 of 106

OCITransStart()

Purpose

Sets the beginning of a transaction.

Syntax

sword OCITransStart ( OCISvcCtx    *svchp, 
                      OCIError     *errhp, 
                      uword        timeout,
                      ub4          flags );

Parameters

svchp (IN/OUT)

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.

errhp (IN/OUT)

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().

timeout (IN)

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 aborted by the system. A transaction is inactive between the time it is detached (with OCITransDetach()) and the time it is resumed with OCITransStart().

flags (IN)

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.

Comments

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)

Examples

The following examples demonstrate the use of OCI transactional calls for manipulating global transactions.

Example 1 - A Single Session Operating On Different Branches.

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];

  OCIInitialize((ub4) OCI_OBJECT, (dvoid *)0,  (dvoid * (*)()) 0,
             (dvoid * (*)()) 0,  (void (*)()) 0 );
  
  OCIHandleAlloc( (dvoid *) NULL, (dvoid **) &envhp, (ub4) OCI_HTYPE_ENV,
             0, (dvoid **) &tmp);
  
  OCIEnvInit( &envhp, (ub4) OCI_DEFAULT, 21, (dvoid **) &tmp  );
  
  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 *)"scott", 
             (ub4)strlen("scott"), OCI_ATTR_USERNAME, errhp);
  OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION, (dvoid *)"tiger", 
             (ub4)strlen("tiger"),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 scott.emp empno=7902, increment salary */
  sprintf((char *)sqlstmt, "UPDATE EMP SET SAL = SAL + 1 WHERE EMPNO = 7902");
  OCIStmtPrepare(stmthp1, errhp, sqlstmt, strlen(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 scott.emp empno=7934, increment salary */
  sprintf((char *)sqlstmt, "UPDATE EMP SET SAL = SAL + 1 WHERE EMPNO = 7934");
  OCIStmtPrepare(stmthp2, errhp, sqlstmt, strlen(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];

  OCIInitialize((ub4) OCI_OBJECT, (dvoid *)0,  (dvoid * (*)()) 0,
             (dvoid * (*)()) 0,  (void (*)()) 0 );
  
  OCIHandleAlloc( (dvoid *) NULL, (dvoid **) &envhp, (ub4) OCI_HTYPE_ENV,
             0, (dvoid **) &tmp);
  
  OCIEnvInit( &envhp, (ub4) OCI_DEFAULT, 21, (dvoid **) &tmp  );
  
  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 *)"scott", 
             (ub4)strlen("scott"), OCI_ATTR_USERNAME, errhp);
  OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION, (dvoid *)"tiger", 
             (ub4)strlen("tiger"),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 scott.emp empno=7902, increment salary */
  sprintf((char *)sqlstmt, "UPDATE EMP SET SAL = SAL + 1 WHERE EMPNO = 7902");
  OCIStmtPrepare(stmthp, errhp, sqlstmt, strlen(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 scott.emp empno=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);
}

Related Functions

OCITransDetach()

Miscellaneous Functions

This section describes the miscellaneous OCI functions.

Table 16-7 Miscellaneous Functions

Function	Purpose
`OCIBreak()`	Perform an immediate asynchronous break
`OCIErrorGet()`	Return error message and Oracle error
`OCILdaToSvcCtx()`	Toggle Lda_Def to service context handle
`OCINlsEnvironmentVariableGet()`	Returns character set id from NLS_LANG and national character set id from NLS_NCHAR.
`OCIPasswordChange()`	Change password
`OCIReset()`	Called after `OCIBreak()` to reset asynchronous operation and protocol
`OCIRowidToChar()`	Converts a Universal ROWID to character extended (base 64) representation.
`OCIServerVersion()`	Get the Oracle version string
`OCISvcCtxToLda()`	Toggle service context handle to Lda_Def
`OCIUserCallbackGet()`	Identifies the callback that is registered for handle
`OCIUserCallbackRegister()`	Registers a user-created callback function