To ensure the ACID ( atomicity, consistency, isolation and durability ) feature inside the cego DBMS, queries are encapsulated in transactions. Cego provides transactional features in a similar way as known from any other known DBMS system. At default, each insert, update or delete query is treated as a single transaction and is automatically commmited after succesful termination. This is called autocommit mode.

To encapsulate subsequent database queries into one transaction, the query sequence must be started with an explicit start transaction command and closed with a commit command. For abortion, instead of a commit a rollback aborts the transaction and resets the database system to the original state.

6.1   Beginning a transaction

A transaction can be explicit started using the start transaction command. ( Note : This is just valid, if autocommit is enabled, otherwise a transaction is automatically started. See the section Autocommit below ) As the Cego DBMS provides tablesets as full recoverable units, transactions are limited to a dedicated tableset. The transaction is performed on the current active tableset.

CGCLT > start transaction;
Transaction on TS1 started
ok ( 0.001 s )

This starts a new transaction for the current tableset TS1. All subsequent queries are part of the started transaction. During the transaction is active, all other clients are getting the original view of the table ( the state of the table before the transaction has been started). Since the all cursors on tables are using an ignore touched policy, any update and delete operations of other transaction will not be able to modify any tuple, which has been changed by the current transaction. The following sample will illustrate this.

We are using a cego client to connect to an active tableset containing the table t1 with three entries.

CGCLT > select * from t1;
+-----------+-----------+
|      t1_1 |      t1_1 |
|         a |         b |
+-----------+-----------+
|         1 |         1 |
|         2 |         2 |
|         3 |         3 |
+-----------+-----------+
ok ( 0.001 s )

Now we are starting a transaction on the tableset and we are deleting one specified tuple

CGCLT > start transaction;
Transaction on STS started
ok ( 0.000 s )
CGCLT > delete from t1 where b = 2;
Query executed
ok ( 0.001 s )

As next, we start another client program to the same tableset and retrieve the available tuples from table t1.

CGCLT > select * from t1;
+-----------+-----------+
|      t1_1 |      t1_1 |
|         a |         b |
+-----------+-----------+
|         1 |         1 |
|         2 |         2 |
|         3 |         3 |
+-----------+-----------+
ok ( 0.002 s )

CGCLT > delete from t1 where b = 2;
Query executed
ok ( 0.001 s )
CGCLT > select * from t1;
+-----------+-----------+
|      t1_1 |      t1_1 |
|         a |         b |
+-----------+-----------+
|         1 |         1 |
|         2 |         2 |
|         3 |         3 |
+-----------+-----------+
ok ( 0.002 s )

Since the tuple is already touched by transaction A, the second delete operation has no effect. After we have retrieved the tuples again, all tuples are still available.

6.2   Committing a transaction

If the transaction should be commited and finished, this is done using the commit command.

CGCLT > commit;
Transaction on TS1 committed
ok ( 0.024 s )

In the sample above, subsequent queries on the table lead to the following result.

CGCLT > select * from t1;
+-----------+-----------+
|      t1_1 |      t1_1 |
|         a |         b |
+-----------+-----------+
|         1 |         1 |
|         3 |         3 |
+-----------+-----------+
ok ( 0.002 s )

6.3   Aborting a transaction

Sometimes for any reasons, a transaction should be aborted and all modifications must be rollbacked. This is done using the rollback command, which restores the database state to the begining of the transaction.

CGCLT > rollback;
Transaction on TS1 rollbacked
ok ( 0.001 s )

6.4   Autocommit

Without starting a transaction explicit, each query is commited automatically. This can be proofed by enabling the autocommit mode with the folowing command.

CGCLT > set autocommit on;
Autocommit enabled
ok ( 0.001 s )

If it is more useful to commit a set of queries with the commit command, either this can be done using the start transaction command, or to disable the autocommit mode.

CGCLT > set autocommit off;
Autocommit disabled
ok ( 0.001 s )

All queries are just commited or aborted after an explicit commit or rollback command.

6.5   Isolation level

With the isolation level, the read behaviour for the corresponding session is defined. Cego provides two isolation levels

• Read Committed
• Read Uncommitted

If the isolation level is set to Read Committed, modifications of other transactions are just visible after they have been commited. Modified rows are read with their origin values and deleted rows are still visible. In terms of consistency, this is the preferred level. With the isolation level set to Read Uncommitted, all non-commited modifications of other transaction are visible. In this level, dirty reads are allowed, so one transaction may see not-yet-committed changes made by other transactions. Dirty reads may result in wrong data. For this reason, it is recommended to use this isolation level just for special purposes.

The isolation level is set with the set isolation command either to Read Committed

CGCLT > set isolation read_committed;
Isolation level set
ok ( 0.001 s )

or to isolation level Read Uncommitted

CGCLT > set isolation read_uncommitted;
Isolation level set
ok ( 0.001 s )

The default value for the isolation level is Read Commited