# Managing eXtremeSQL Transactions in C++

> All eXtremeDB database access is performed within transactions to assure database integrity by enforcing the `ACID` principles (see the eXtremeDB User Guide for a detailed description of transaction processing). When using the eXtremeSQL API, transaction processing is `implicit`, i.e. transactions are started and committed by the runtime without requiring explicit function calls. Specifically, the McoSqlEngine method `executeStatment()` starts and closes a `READ_ONLY` transaction when performing an SQL `SELECT` (unless the `FOR UPDATE` clause is specified), and a `READ_WRITE` transaction when performing `INSERT`, `UPDATE` or `DELETE` statements. Similarly, the QueryResult constructor starts a `READ_ONLY` transaction and returns a result set `cursor`; it’s destructor closes the transaction.

> However, some applications need to perform updates that require transaction blocking that spans more than one eXtremeSQL function call. For example, consider the following example that enters a “money exchange” where a credit and debit update need to be recorded simultaneously. Here it is crucial for data integrity that both updates happen. If some system malfunction where to interrupt the application between the updates, the database data would be left in an inconsistent state. Transaction blocking assures that either both updates succeed or neither update is applied to the database.

> To take control of transaction processing with eXtremeDB the application uses the McoSqlEngine method `beginTransaction()` to create a Transaction object, then the Transaction method `commit()` is called to complete the block of database operations. (Note that multi-threaded applications will use an McoSqlSession object which inherits the McoSqlEngine methods. Note also that you can use the Transaction method `checkpoint()` to update indexes and make objects visible to queries while the transaction is still open, or `rollback()` to undo all operations performed in this transaction.)

> ### Example

>         // Define global McoSQL engine
>         using namespace McoSql;
>         McoMultithreadedSqlEngine engine;

>         // Define the structure correponding to database record Person
>         struct _Member
>         {
>             int  id;
>             char * name;
>             int  balance;
>         };

>         int main()
>         {
>             _Member m;

>             // Open eXtremeDB database and SQL engine
>             engine.open( db_name, exchangedb_get_dictionary(), DATABASE_SIZE, MEMORY_PAGE_SIZE);

>             //Create a McoSession to manage the transactions
>             McoSqlSession session(&engine);

>             // Insert two Member records with initial balances of 100
>             m.id = 201000001;
>             m.name = "John Smith";
>             m.balance = 100;
>             session.executeStatement("insert into Member %r", &m);
>             m.id = 201000002;
>             m.name = "Peter Brown";
>             m.balance = 100;
>             session.executeStatement("insert into Member %r", &m);

>             // List the contents of the Member table
>             printf ("\n\tInitial Member records:\n");
>             listMembers( &session );

>             // Enter an exchange of 50 units from Smith to Brown and display result
>             doExchange( &session, 201000001, 201000002, 50 );
>             printf ("\n\tAfter an exchange of 50 units from 'Smith' to 'Brown':\n");
>             listMembers( &session );

>             // Close SQL session, engine and database
>             engine.close();
>             return 0;
>         }

>         void doExchange( McoSqlSession * session, int idFrom, int idTo, int amount)
>         {
>             int fromBalance = getBalance( session, idFrom ) - amount;
>             int toBalance = getBalance( session, idTo ) + amount;

>             // Perform two updates that must both complete within a single database transaction
>             Transaction* trans = session->database()->beginTransaction(Transaction::ReadWrite);

>             session->executeStatement(trans, "update Member set balance=%i where id=%i",

>                             fromBalance, idFrom);

>             session->executeStatement(trans, "update Member set balance=%i where id=%i",

>                             toBalance, idTo);

>             trans->commit();

>             trans->release();

>         }

>         int getBalance( McoSqlSession * session, int id )
>         {
>             QueryResult result(session->executeQuery( "select * from Member where id=%i", id ) );
>             Cursor* cursor = result->records();
>             Record* rec = cursor->next();
>             _Member m;
>             result->extract(rec, &m, sizeof(m));
>             return m.balance;
>         }

>         void listMembers( McoSqlSession * session )
>         {
>             QueryResult result(session->executeQuery("select * from Member order by id"));
>             Cursor* cursor = result->records();
>             while (cursor->hasNext())
>             {
>                 _Member m;
>                 Record* rec = cursor->next();
>                 result->extract(rec, &m, sizeof(m));
>                 printf("\t\t%s: Balance=%d\n", m.name, m.balance );
>             }
>         }

> Some programming points to note in the code sample above:

> - The McoSqlSession constructor is called to instantiate a session object then all successive database access is performed using this session
> - The McoMultithreadedSqlEngine object is used only to open and close the eXtremeSQL runtime and database
> - The Transaction object is instantiated by calling method `beginTransaction()` on the `database` member of the session object.
> - After completing the database updates, the transaction object is closed and its memory freed by calling its `commit()` and `release()` methods.

> ## 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 can be called as follows:

>         {
>             int fromBalance = getBalance( session, idFrom ) - amount;
>             int toBalance = getBalance( session, idTo ) + amount;

>             // Perform two updates that must both complete within a single database transaction
>             Transaction* trans = session->database()->beginTransaction(Transaction::ReadWrite);
>             session->executeStatement(trans, "update Member set balance=%i where id=%i",
>                             fromBalance, idFrom);
>             trans->checkpoint();

>             QueryResult result(session->executeQuery("select * from Member order by id"));
>             ...
>             trans->commit();
>             trans->release();
>         }

> Or the following SQL statement can be used to cause a checkpoint:

>         executeStatement("CHECKPOINT TRANSACTION");

> To enable the autocheckpoint feature, set the `SqlEngine.autoCheckpoint`:

>         using namespace McoSql;
>         McoSqlEngine engine;
>         int main(int argc, char* argv[])
>         {
>             engine.autoCheckpoint = true;
>             ...
>         }

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

>             ...
>         }