ToC path:

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;
            
        ...
    }
     