Managing eXtremeSQL Transactions in C

As explained in the eXtremeDB User Guide, all eXtremeDB database operations are performed within the context of a transaction. A READ_ONLY transaction is started for database read operations, and then the transaction is committed or rolled back after the read is completed. Likewise, all database updates (INSERT, UPDATE, DELETE, operations) are performed within a READ_WRITE transaction that is either committed or rolled back when the operation is complete.

This “transaction blocking” is done implicitly by the query and update statements. When mcosql_execute_query() or mcosql_execute_prepared_query() is called with a NULL transaction handle (second argument) as in the above examples, a READ_ONLY transaction is started and closed by the runtime. Likewise, when calling functions mcosql_execute_statement() or mcosql_execute_prepared_statement(), a READ_WRITE transaction is started and closed by the runtime.

If the application needs to group a number of SQL statements in a single database transaction, it takes charge of the transaction blocking explicitly by first opening the transaction with mcosql_begin_transaction() which returns a transaction handle; then, after all of the SQL statements have been completed, closing the transaction with mcosql_commit_transaction() or mcosql_rollback_transaction(). The function mcosql_begin_transaction() is as follows:

 
    mcosql_begin_transaction(database_t database, transaction_t* trans,
                    enum mcosql_transaction_mode mode,
                    int priority);
     

This function returns a valid transaction in argument trans, or an error code if a transaction cannot be started. The third argument mode specifies the transaction type: MCO_READ_ONLY or MCO_READ_WRITE. And the fourth argument specifies the transaction priority which should always be MCO_TRANS_FOREGROUND (though other transaction priority options are available, they are not recommended. (Please refer to the Concurrency Control page for a detailed explanation of transaction processing and priority management.)

The functions mcosql_commit_transaction() and mcosql_rollback_transaction() simply require a valid transaction handle argument. They are as follows:

     
    mcosql_commit_transaction(transaction_t trans);
    mcosql_rollback_transaction(transaction_t trans);
     

Checkpoint and Autocheckpoint

As explained in the eXtremeSQL Transaction Control page, a checkpoint operation may be necessary in complex transactions to update indexes prior to the transaction commit. The C API function to cause a checkpoint is mco_trans_checkpoint(). Or the following SQL statement can be used to cause a checkpoint:

 
    executeStatement("CHECKPOINT TRANSACTION");
     

To enable the autocheckpoint feature set the MCO_DB_SQL_AUTOCHECKPOINT flag in database parameter mco_db_params_t.mode_mask. For example:

 
    int main(int argc, char* argv[])
    {
        ...
        mco_db_params_t       db_params;
        ...
        mco_db_params_init( &db_params );               /* Initialize the params with default values */
        ...
        db_params.mode_mask = MCO_DB_SQL_AUTOCHECKPOINT;
            
        ...
    }
     

Isolation level

It is also possible to set the isolation level for a transaction by calling the extended version mcosql_begin_transaction_ex() whose signature is as follows :

 
    mcosql_begin_transaction_ex(database_t database, transaction_t* trans,
                    enum mcosql_transaction_mode mode,
                    int priority,
                    enum mcosql_transaction_isolation_level level);
     

Upgrading a Transaction

It is possible to upgrade a READ_ONLY transaction started for a query to a READ_WRITE transaction for updating data within the same transaction by calling the following:

 
    mcosql_upgrade_transaction(transaction_t trans);
     

Releasing a Transaction

Once the transaction block is complete, the transaction handle should be “released” to release all resources associated with this transaction. This is done by calling the following function:

 
    mcosql_release_transaction(transaction_t trans);