Choosing the eXtremeDB Transaction Manager

The choice of Transaction Manager can be important for multi-task and/or mutli-process applications, or multi-user environments where more than one thread or process may be updating database tables. In single-user/single-threaded applications, where only one application thread accesses the database, concurrency is not an issue and the choice is simple: use the EXCLUSIVE Transaction Manager.

The topic of concurrency control is covered in detail in the Concurrency page. Please refer to the description there for definitions and explanation of “Transaction Isolation” levels. Briefly, eXtremeDB offers both “pessimistic” and “optimistic” concurrency control depending on the Transaction Manager linked into the application. The MURSIW (Multiple Readers Single Writer) implements “pessimistic” concurrency management by effectively enforcing the “Serializable” isolation level. If a more “optimistic” concurrency management policy is desired, such as isolation level “Read Committed” or “Read Repeatable”, then the application must be linked with the MVCC (Multi-Versioning Concurrency Control) transaction manager.

There is also the possibility of using a specialized PRIORITIZED READ Transaction Manager that optimizes performance for applications with "mostly read" data access patterns.

EXCLUSIVE

This is the most efficient Transaction Manager for single-user/single-threaded applications. It only allows one task at a time to access the database for reading or writing. To use the EXCLUSIVE Transaction Manager the application must be linked with library mcotexcl.

MURSIW

As explained above, the MURSIW Transaction Manager enforces the “Serializable” isolation level, which means that an exclusive lock is applied to all write transactions—no other write transactions can run at the same time. However “readers” can still run in parallel with the “writer” and with each other.

To choose the MURSIW transaction manager, the application simply links with library mcotmursiw.

MVCC

The MVCC transaction manager allows the developer to choose one of two “optimistic” isolation levels by specifying MCO_READ_COMMITTED or MCO_REPEATABLE_READ, or the “pessimistic” isolation level by specifying MCO_SERIALIZABLE , when calling beginTransaction(). To select an isolation level the application must be linked with the MVCC library mcotmvcc.

The following code snippet demonstrates how the isolation level is specified in an eXtremeSQL application:

 
    void task( McoSqlEngine & engine, _Person* p )
    {
        int rc;
        uint4 i;
        McoSqlSession session( engine );
        Transaction* trans = session.database()->beginTransaction(Transaction::ReadWrite,
                                0, MCO_READ_REPEATABLE);
        rc = session.executeStatement(trans, "insert into Persons %r", p);
        sample_sleep(nap_duration2);
        trans->commit();
        trans->release();
    }
     

Some programming points to note in the code snippet above:

• The class McoSqlSession is used to create a new session (thread) using the current SQL database connection. This is necessary because a “default” transaction is started in the main thread when the McoSqlEngine is instantiated.
• An “allocation context” must be instantiated for the session.
• When calling session.database()->begnTransaction() the desired isolation level (MCO_READ_REPEATABLE in this case) is specified.

The isolation level can also be specified in a SQL statement using one of the two following syntax alternatives:

 
    set default_isolation_level ( read_committed | repeatable_read | serializable )
     

or

 
    set DefaultIsolationLevel ( ReadCommitted | RepeatableRead | Serializable )
     

PRIORITIZED READ

This transaction manager, implemented in library mcotread, significantly reduces the number of context switches when the access pattern is "mostly read". The downside is that write transactions are stalled until all read transactions are processed.