Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

Transaction Processing

A transaction is an essential part of database programming. It defines the beginning and end of a series of database operations that are regarded as a single unit.

For example, to transfer money between two bank accounts, an amount is subtracted from one account and the same amount is added to the other account. It is essential that either both of these operations succeed or neither does.

Mimer SQL uses a method for transaction management called Optimistic Concurrency Control (OCC). OCC does not involve any locking of rows as such, and therefore cannot cause a deadlock.

Transactions in ODBC are usually managed at the connection level, although there is the option of applying a commit or rollback for all connections within an environment.

Transaction Management Mode

There are two modes for managing transactions within ODBC, Autocommit and Manual-commit. SQLSetConnectAttr is used to switch between the modes.

Autocommit Mode

Autocommit mode is the default transaction mode for ODBC; when a connection is made, it is in autocommit mode until SQLSetConnectAttr is used to switch to manual commit mode.

In autocommit mode each individual statement is automatically committed when it completes successfully, no explicit transaction management functions are necessary.

However, the return code from the function must still be checked as it is possible for the implicit transaction to fail.

Manual-commit Mode

When in manual commit mode, all executed statements are included in the same transaction until calling SQLEndTran specifically completes it.

When an application turns autocommit off, the next statement against the database starts a transaction. The transaction continues until SQLEndTran is called with either SQL_COMMIT or SQL_ROLLBACK. The next command sent to the database after that starts a new transaction.

Completing Transactions

Transactions are completed (either committed or rolled back) by use of the ODBC function SQLEndTran rather than using the SQL COMMIT or ROLLBACK statements.

Calling SQLEndTran with a request to rollback a transaction causes Mimer SQL to discard any changes made since the start of the transaction and to end the transaction.

Example Transaction

 /* Disable transaction autocommit mode */
 SQLSetConnectAttr( hdbc, SQL_ATTR_AUTOCOMMIT,
                    (SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0 );
 SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt );
 /* First statement against Mimer SQL starts a transaction */
 SQLExecDirect( hstmt,
                "UPDATE mimer_store.currencies \
                    SET exchange_rate = exchange_rate * 1.05 \
                    WHERE code = 'USD'", SQL_NTS );
 SQLExecDirect( hstmt,
                "UPDATE mimer_store.currencies \
                    SET exchange_rate = exchange_rate * 1.08 \
                    WHERE code = 'GBP'", SQL_NTS );
 printf( "Commit transaction? : " );
 scanf( "%s", ans );
 if (ans[0] == 'Y'
 || ans[0] == 'y')
    retcode = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT );
    if (retcode == SQL_ERROR)
       /* Check SQLSTATE for transaction conflict */
       SQLGetDiagField (hdbc,
                        &msglen );
       if (strcmp( sqlstatus, "40001" ) == 0) goto retry;
       goto display_error;

Setting the Transaction Isolation Level

To set the transaction isolation level, use the SQL_ATTR_TXN_ISOLATION connection attribute.

The default isolation level for Mimer SQL is SQL_TXN_REPEATABLE_READ.

Mimer Information Technology AB
Phone: +46 18 780 92 00
Mimer SQL Documentation TOC PREV NEXT INDEX