Transaction Control in eXtremeSQL

As explained in the eXtremeDB Transaction Control page, a transaction is a unit of work with the database and all database operations must be implemented within a transaction block. By default, eXtremeSQL executes all statements within a single transaction; i.e. the runtime implicitly performs a start transaction then executes the statement and closes the transaction with commit transaction. This is called auto-commit.

Sometimes it may be desirable or necessary to perform multiple statements within a single transaction. This can be done by explicitly starting and closing (commit or rollback) the transaction. For example:

 
    XSQL>create table foo(name string primary key);
    XSQL>start transaction;
    XSQL>insert into foo values ('one');
    XSQL>insert into foo values ('two');
    XSQL>commit transaction;
    XSQL>select * from foo;
    name
    ---------------------------------------------------------------------- 
    one
    two
 
    Selected records: 2
     

When performing a series of operations in a single transaction, it may be necessary to access a database object newly inserted, or modified, within the current transaction. This requires that all indexes in which the object participates be updated. Normally indexes are updated during the commit transaction operation. But when necessary, it is possible to update the indexes prior to the commit transaction by explicitly calling a checkpoint transaction method. For example:

 
    XSQL>start transaction;
    XSQL>insert into foo values ('three');
    XSQL>select * from foo;
    name
    ---------------------------------------------------------------------- 
    one
    two
 
    Selected records: 2
    XSQL>checkpoint transaction;
    XSQL>select * from foo;
    name
    ---------------------------------------------------------------------- 
    one
    two
    three
 
    Selected records: 3
    XSQL>insert into foo values ('four');
    XSQL>commit transaction;
    XSQL>select * from foo;
    name
    ---------------------------------------------------------------------- 
    one
    two
    three
    four
 
    Selected records: 4
     

In some APIs, it is possible to enable the autocheckpoint feature to cause all operations to implicitly update the appropriate indexes prior to the commit transaction.

Checkpoint

When an indexed field is modified by a transaction, the object is removed from all indexes defined for that class. Regardless of the whether the modified field is present in other indexes. Once the object is removed from indexes, there is no way to locate the object based on any search function. The object is put back into the indexes upon the transaction commit. The checkpoint transaction method is the only way to put the object back into indexes within the transaction and thus make it visible to select operations.

Please note that checkpoint operation is only meaningful in MVCC transaction manager. MURSIW allows only a single transaction at a time if it is a write transaction.

Autocheckpoint

The autocheckpoint feature is different that auto-commit in that it causes all indexes to be updated whenever a database object is inserted, updated or deleted. So the current state of the database is available to other operations within the same transaction. These index updates, and the database objects to which they refer, are "temporarily" applied to the database, but are "reversed" if the transaction is rolled back.

Note that there is a subtle difference between the behavior of autocheckpoint and explicitly calling the checkpoint transaction method. Whereas the autocheckpoint feature affects every insert, update, or delete operation, the checkpoint transaction method affects all insert, update or delete operations so far executed within the transaction.

Native Language APIs

Please use the links below to view detailed explanations and examples for your development environment:

C	eXtremeSQL transaction management in C
C++	eXtremeSQL transaction management in C++
Java	eXtremeSQL transaction management in Java
C#	eXtremeSQL transaction management in C#
Python	eXtremeSQL transaction management in Python