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 SqlLocalConnection or SqlRemoteConnection method ExecuteStatement() 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 ExecuteQuery() method starts and closes a READ_ONLY transaction and returns a result set SqlResultSet.

These implicit transactions are managed by an internal SqlLocalConnection or SqlRemoteConnection object and for most database operations the application developer need not be concerned with the transaction interface. However, some applications need to perform updates that require transaction blocking that spans more than one eXtremeSQL function call.

To take control of transaction processing with eXtremeSQL the application uses the SqlLocalConnection or SqlRemoteConnection methods StartTransaction() and CommitTransaction() or RollbackTransaction(). (Note also that you can use the CheckpointTransaction() method to update indexes and make objects visible to queries while the transaction is still open.) This is illustrated in the following code snippet:

    SqlLocalConnection con = db.ConnectSql();
    con.StartTransaction(Database.TransactionType.ReadWrite);
    con.ExecuteStatement("insert into MyTable (pk,value) values (?,?)", 2012, "Good bye");
    con.ExecuteStatement("insert into MyTable (pk,value) values (?,?)", 2013, "Hello");
    con.CommitTransaction();
     

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 to cause a checkpoint can be called as follows:

    SqlLocalConnection con = db.ConnectSql();
    con.StartTransaction(Database.TransactionType.ReadWrite);
    con.ExecuteStatement("insert into MyTable (pk,value) values (?,?)", 2012, "Good bye");
    con.CheckpointTransaction();
    SqlResultSet result = conn.ExecuteQuery("SELECT * FROM MyTable WHERE pk=?", 2012);
    con.CommitTransaction();
     

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

    con.ExecuteStatement("CHECKPOINT TRANSACTION");
     

To enable the autocheckpoint feature, set the DB_SQL_AUTOCHECKPOINT flag in database parameters Mode. For example:

    Database.Parameters parameters = new Database.Parameters();
    parameters.Mode = Database.DB_SQL_AUTOCHECKPOINT;
    ...