# Database Concurrency and Transaction Management in C#

> As explained in the Transaction and Concurrency control pages, applications use transaction blocking for all database access. This allows the eXtremeDB `transaction managers` to schedule and process all database operations, whether they involve simple `ReadOnly` access or `ReadWrite` operations that modify database objects.

> ## Transaction blocking

> An eXtremeDB transaction block consists of a set of database operations enclosed within a transaction start and commit or rollback. In C# applications, transactions are started by calling one of two versions of the Connection method `startTransaction()`; the first version accepts a single argument of type `Database.TransactionType` which can be one of the following

> - `Exclusive` - for single-process, single-threaded applications only
> - `ReadOnly` - for `READ_ONLY` database access

> - `ReadWrite` - for performing database updates

> - `Update` - for reading with the intention to possibly update the database

> The second version differs in that it allows setting the `TransactionPriority` and the `IsolationLevel` for the transaction. As explained in the Transaction Priorities and Scheduling page, the transaction priority can be one of the following:

> - `AboveNormal` -
> - `Background` -
> - `Foreground`-
> - `Highest` -
> - `Idle` -

> As explained in the Isolation Levels page, the isolation level can be one of the following:

> - `Default` -
> - `ReadCommitted` -
> - `RepeatableRead` -
> - `Serializable` -

> When the first version of `StartTransaction()` is called, the transaction isolation level is set to the default which is `Serializable` for `MURSIW` and `RepeatableRead` for `MVCC`. (Note that if `MURSIW` is used the only possible level is `Serializable`.)

> ### Transaction Commit and Rollback

> To commit a transaction call the Connection method `CommitTransaction()`; to discard any database operations since the `StartTransaction()`,call `rollbackTransaction()`. The `CommitTransaction()` method has two versions: the first requires no argument, the second allows specifying the phase (`1` or `2`) for a two-phase commit (see below.)

> ## Transaction Managers

> As explained in the Fundamental Concepts page, eXtremeDB offers three transaction managers to meet varying application demands and concurrency strategies. The choice of transaction manager can have a significant performance impact on applications. But fortunately, changing transaction managers is simply a matter of changing the transaction manager library loaded by the application when the `Database` is instantiated. Please use these links to view implementation details of the `MURSIW` and `MVCC` transaction managers. The EXCL(usive) transaction manager is not supported for C# applications.

> In C# applications, the transaction manager library, as are all other libraries, are loaded dynamically at runtime. As ``MURSIW`` is the default transaction manager, no special action is required to load it. However, to load the `MVCC` library the Database.Mode property `MVCCTransactionManager` must be passed to the Database constructor. Also, the debug version of the ``MVCC`` library can be specified by the additional Database.Modeproperty `DebugSupport`. (If `DebugSupport` is not specified, then the release mode versions of all libraries are loaded.)

> For example the following code snippet will load the debug version of `MVCC`:

>         Database.Mode mode = Database.Mode.MVCCTransactionManager | Database.Mode.DebugSupport;
>         Database db = new Database(mode);

> Note that it is recommended to use the debug version of eXtremeDB libraries during development, then switch to the release mode libraries for final release.

> ## Setting the Transaction Priority and Scheduling Policy

> As explained in the Transaction Priorities and Scheduling page, applications can adjust the transaction priority and scheduling policy at runtime. The transaction priority is specified in the call to `StartTransaction()`. Applications can explicitly define the `MURSIW` scheduling policy by setting the desired `Database.TransSchedPolicy` in the `Database.Parameters.SchedPolicy` passed into Database method `Open()`.

> ## MVCC Conflict management

> When `MVCC` is used with an isolation level other than `Serializable`, then `ReadWrite` transactions are executed concurrently. Sometimes concurrent transactions modify the same objects, thus creating transaction conflicts. The transaction manager resolves those conflicts by aborting one of the conflicting transactions and letting the other one commit its updates to the database. When a transaction is aborted, the application receives the `MCO_E_CONFLICT` error code. It is the application’s responsibility to manage this possibility with logic similar to the following:

>         conflicts = true;
>         do {
>             try
>             {
>                 con.StartTransaction(Database.TransactionType.ReadWrite);
>                 ...<update database>...
>                 con.CommitTransaction();
>                 conflicts = false;

>             }
>             catch (DatabaseError dbe)
>             {
>                 if ( dbe.errorCode != MCO_E_CONFLICT )
>                     throw dbe;
>             }
>         } while ( conflicts );

> Note that when MVCC is used, the application must be able to tolerate transaction rollbacks due to conflicts.

> If the number of conflicts is too high, it could lead to sharp performance degradation due to the need to retry transactions. When this occurs, the transaction manager temporarily changes the isolation level to `Serializable`.

> ## Two-phase commit

> Some applications require more elaborate control of 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) (all together, 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.

> To perform the two-phase commit, the application needs to call the commit phases sequentially instead of calling one `CommitTransaction()`. After the first commit phase is returned, the application cannot perform any activities against the database except initiating the second commit phase or rolling back the transaction. This process is illustrated in the following code segment:

>         ...
>         con.StartTransaction(Database.TransactionType.ReadWrite);
>         ...
>         if ( (con.CommitTransaction(1)) && GlobalTransaction() == SUCCESS )	)
>         {
>             con.CommitTransaction(2);
>         }
>         else
>         {
>             con.RollbackTransaction();
>         }

> Note that the two-phase commit API is not supported when using the `MVCC` transaction manager with a persistent database.

> ## Pseudo-nested Transactions

> Nested transactions might be necessary when two different application functions may be called separately or call each other. To facilitate transaction nesting eXtremeDB allows a C++ application to call `StartTransaction()` before the current transaction is committed or aborted. The eXtremeDB runtime maintains an internal counter that is incremented each time `StartTransaction()` is called, and decremented by `CommitTransaction()` and `RollbackTransaction()`. A transaction commit in an inner transaction does not perform any actions except to reduce the nested transaction counter, and the transaction context remains valid until the outer transaction performs a commit or rollback. The runtime will not actually commit the transaction until the counter reaches zero.

> If an “inner” transaction calls `RollbackTransaction()`, the transaction is put into an error state, and any subsequent calls to modify the database in the scope of the outer-most transaction will return immediately.

> Outer and inner transactions will be assigned the stricter transaction type without requiring the application to upgrade the transaction type; each transaction code block should simply call `StartTransaction()` with the appropriate transaction type for the operation being performed within its own body. Note, however, that the inner block’s `StartTransaction()` might fail.

> The following code snippet illustrates a nested transaction implementation:

>         class BankTransaction
>         {
>         #pragma warning disable 0649
>             public uint4 from;
>             public uint4 to;
>         #pragma warning restore 0649
>         };

>         // Insert two BankTransaction objects
>         public static bool InsertTwo(Connection con, uint4 from1, uint4 to1, uint4 from2, uint4 to2)
>         {
>             try
>             {
>                 con.StartTransaction(Database.TransactionType.ReadWrite);

>                 // call nested transaction in InsertOne() to insert first object
>                 InsertOne(con, from2, to2);

>                 // insert second object
>                 con.StartTransaction(Database.TransactionType.ReadWrite);
>                 BankTransaction b2 = new BankTransaction();
>                 b2.from = from1;
>                 b2.to = to1;
>                 con.Insert(b2);
>                 con.CommitTransaction(); // commit second object

>             }
>             catch (DatabaseError dbe)
>             {
>                 throw dbe;
>             }

>             return true;
>         }

>         // insert one BankTransaction record within a read-write transaction */
>         public static void InsertOne(Connection con, uint4 from, uint4 to )
>         {
>             try
>             {
>                 // insert first object
>                 con.StartTransaction(Database.TransactionType.ReadWrite);
>                 BankTransaction b1 = new BankTransaction();
>                 b1.from = from1;
>                 b1.to = to1;
>                 con.Insert(b1);
>                 con.CommitTransaction(); // commit first object

>             }
>             catch (DatabaseError dbe)
>             {
>                 throw dbe;
>             }

>             return true;
>         }

>         int main(int argc, char* argv[])
>         {
>             bool rc;
>             Connect con;
>             ...
>             /* perform a simple nested transaction... */
>             uint4 from1 = 11, to1 = 16, from2 = 7, to2 = 17;

>             rc = InsertTwo(con, from1, to1, from2, to2);
>             ...
>         }

> Note that if the transaction type in module `InsertTwo()` had been `ReadOnly`, the nested transaction in `InsertOne()` would automatically promote the transaction type to `ReadWrite` causing the outer transaction to complete successfully even though it would otherwise fail on the attempt to instantiate a new object within a `ReadOnly` transaction.

> Unfortunately there is no safe way of enforcing the scope of a transaction. Consequently applications can make the mistake of not closing transactions, unintentionally creating psuedo-nested transactions. Care must be taken to assure proper transaction blocking.