Using the eXtremeSQL Two-Phase Commit

Some applications require a more elaborate control of the transaction commit processing; specifically, committing the transaction in two steps (phases). The first phase writes the data into the database, inserts new data into indexes and checks index restrictions (uniqueness) (altogether, the “pre-commit”) and returns control to the application. The second phase finalizes the commit.

One example of such an application is the case where multiple eXtremeDB databases need to synchronize the updates performed within a single transaction. Another example could be that the eXtremeDB transaction commit is included in a global transaction that involves other database systems or external storage. In this case, the application coordinates the eXtremeDB transaction with the global transaction between the first phase and the second phase.

The essential feature of a two-phase commit is that the second phase of the commit succeeds only if no error condition is detected. The following sample code demonstrates how a multi-task application that inserts records into a single database manages a duplicate key situation by rolling back the transaction when the error is detected:

     
    char * dbName1 = "ph2_1";
    char * dbName2 = "ph2_2";
    using namespace McoSql;
    McoSqlEngine engine1, engine2;
    McoSqlOpenParameters params;
     
    // Define the structure correponding to database table T
    struct _T
    {
        uint4       IntKey;
    };
 
    int main()
    {
        uint4 i, key;
        int nCommitsAttempted = 0;
        int nCommitsSucceded = 0;
        params.databaseName            = dbName1;
        params.dictionary              = ph2commitdb_get_dictionary();
        params.mainMemoryDatabaseSize  = DATABASE_SIZE;
        params.mainMemoryPageSize      = MEMORY_PAGE_SIZE;
        engine1.open(params);
 
        params.databaseName = dbName2;
        params.flags &= ~(McoSqlOpenParameters::START_MCO_RUNTIME); // don't restart runtime
        engine2.open(params);
 
        /* fill database 1 */
        printf("\n\tFill database ph2_%d ...\n", 1 );
        for (i = 0; i < nRecords; i++)
        {
            key = i * 3 + 1;
            engine1.executeStatement("insert into T (intKey) values (%u)", key);
            printf("\t\tInsert into T key = %u\n", key );
        }
 
        /* fill database 2 */
        printf("\n\tFill database ph2_%d ...\n", 2 );
        for (i = 0; i < nRecords; i++)
        {
            key = i * 3 + 2;
            engine2.executeStatement("insert into T (intKey) values (%u)", key);
            printf("\t\tInsert into T key = %u\n", key );
        }
         
        printf("\n\n\tBegin 2-phase commits for key values %d to %d ...\n\n", 0,
                nRecords*3-1 );
        for (i = 0; i < nRecords * 3; ++i)
        {
            Transaction* trans1 = engine1.database()->beginTransaction(Transaction::ReadWrite);
            Transaction* trans2 = engine2.database()->beginTransaction(Transaction::ReadWrite);
            key = i;
            engine1.executeStatement(trans1, "INSERT INTO T (intKey) VALUES(%u)", key);
            engine2.executeStatement(trans2, "INSERT INTO T (intKey) VALUES(%u)", key);
            printf("\t  key = %u\n", key );
             
            try 
            {
                nCommitsAttempted++;
                printf("\t\tCommit phase 1 to ph2_1 for key = %u\n", key );
                trans1->commit(1);
                nCommitsSucceded++;
                nCommitsAttempted++;
                printf("\t\tCommit phase 1 to ph2_2 for key = %u\n", key );
                trans2->commit(1);
                nCommitsSucceded++;
                 
                // Both phase-1 commits succeded, Commit phase 2 of both transactions
                nCommitsAttempted++;
                printf("\t\tCommit phase 2 to ph2_1 for key = %u\n", key );
                trans1->commit(2);
                nCommitsSucceded++;
                nCommitsAttempted++;
                printf("\t\tCommit phase 2 to ph2_2 for key = %u\n", key );
                trans2->commit(2);
                nCommitsSucceded++;
            } 
            catch (NotUnique &) 
            {
                printf("\tError committing transactions for key = %u ...\n", key );
                trans1->rollback();
                trans2->rollback();
            }
 
            // Release the transactions
            trans1->release();
            trans2->release();
        }
         
        printf("\t%d 2-phase commits attempted, %d succeded\n\n\tFinal results:\n",
            nCommitsAttempted, nCommitsSucceded );
        showRecords( 1 );
        showRecords( 2 );
        engine2.close();
        engine1.close();
        sample_pause_end("\n\nPress any key to continue . . . ");
        return 0;
    }