# 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);