7. Transaction Control

7.1 Introduction

In this chapter, we will describe the concepts of transactions and savepoints and their characteristics. We will also show you how to use ODBC functions to end a transaction and set up options for transaction control. In this chapter you will learn how to:

terminate a transaction by using the SQLTransact function. The effects that occur when a transaction is terminated are also explained.

set and use two different commit modes, auto-commit and manual-commit, by using the SQLSetConnectOption and SQLGetConnectOption functions.

7.2 Transactions and Savepoints

A transaction is a sequence of one or more SQL statements that form a logical unit of work. Each SQL statement in the transaction performs part of a task and all of them are necessary for the task. Only when all SQL statements in the transaction are executed successfully we can treat the task as completed.

For example, to manage a deposit to a bank account, a program should:

Query the account table to make sure the account name is valid.
Query the branch table to make sure the branch number is valid.
Query the teller table to check if the teller exists.
Insert a record into the history table for this deposit.
Update the balance of this account name in the account table, and add the money for this deposit.
Update the balance of this teller in the teller table.
Update the balance of this branch in the branch table.

These seven operations compose a complete transaction, and each operation is an SQL statement. If any one of these statements fails, the execution of this entire transaction must be discarded or inconsistencies in the data may result.

The general flow of a transaction is shown below:

Start a transaction.
Execute the statements.
Roll back the changes if any of the statements fail.
Commit the changes if all the statements succeed.

When you connect to DBMaker, a transaction starts automatically. You can execute as many SQL statements as you need. After these SQL statements are processed, if you want to commit the transaction, which means all changes made by DML operations (INSERT, DELETE or UPDATE) in the transaction are permanently entered in the database, you call the ODBC function SQLTransact with the option SQL_COMMIT (described in the next section). On the other hand, if you want to abort the transaction, you can call the ODBC function SQLTransact with the option SQL_ROLLBACK. After one transaction is terminated, DBMaker will start a new transaction automatically.

Sometimes if the transaction is very long, you can use savepoints to divide the long transaction into several parts so that it's easier to manage the whole transaction. A savepoint is a logical marker that can be declared at the specified point within the context of a transaction. Using a savepoint allows you to undo all updates after the specified point in a transaction without undoing the whole transaction.

For example, if you execute a transaction which is composed of fifteen SQL statements, and you mark a savepoint between the 10^th and 11^th statement, you can rollback to the savepoint if an error occurs while executing the 12^th statement. Then you only have to correct the statement the error occurred in and redo the 10^th,11^th and 12^th statement instead of redoing all the statements in current transaction. The following example illustrates this:

statement 1;
...
statement 5;

SAVEPOINT SVP1;      -> point A: define the first savepoint

statement 6;
...
statement 10;

SAVEPOINT SVP2;      -> point B: define the second savepoint

statement 11;
...
statement 13;        -> error occurs

ROLLBACK TO SVP2;    -> point C: when error occurs, rollback to nearest
                        savepoint

/* at this point, all the statements before SVP2 are preserved */
/* only statements 11 to 13 need to be re-executed.            */

statement 14;
statement 15;
statement 16;

COMMIT WORK;         -> if all statements are ok, commit the transaction

In this example, we can see how savepoints can help us manage a long transaction. In DBMaker, you can define up to 32 savepoints in a transaction. After the transaction is terminated, all the defined savepoints in this transaction will be cleared.

Note that the savepoint ID must be unique in a transaction, e.g. if you have defined a savepoint named SVP1 at point A, you cannot define another savepoint named SVP1 at point B. Another important fact to remember when using savepoints is that when you roll back to a pre-defined savepoint, all the savepoints defined after that point are discarded.

e.g. At point C in the above example, if you rollback to savepoint SVP1, then SVP2 is discarded and cannot be used any more. However, you can then define a new savepoint called SVP2.

7.3 Terminating a Transaction

As described in previous section, you can use SQLTransact to commit or rollback a transaction.

The prototype for SQLTransact is:

RETCODE SQLTransact(
                    HENV  henv,
                    HDBC  hdbc,
                    UWORD fType
                   );

where fType can be either SQL_COMMIT or SQL_ROLLBACK. As their names imply, SQL_COMMIT commits the transaction and SQL_ROLLBACK rolls back the transaction.

In DBMaker, unless the value of connection option SQL_CB_MODE is set to SQL_CB_PRESERVE after a transaction is terminated (either commited or rolled back), or the user rolls back to a defined savepoint, all pending results associated with all the statement handles in the current connection handle are cleared. The following sample code illustrates this:

SQLAllocEnv(&henv);
SQLAllocConnect(henv,&hdbc);

/* connect to a database */

SQLConnect(hdbc, ...)
SQLAllocStmt(hdbc, &hstmt1);
SQLAllocStmt(hdbc, &hstmt2);
.
.
.

/* fetch one tuple from account table */


SQLExecDirect(hstmt1, "select * from account", SQL_NTS);
SQLBindCol(hstmt1, 1, ....)
SQLBindCol(hstmt1, 2, ....)
SQLFetch(hstmt1);

/* fetch one tuple from branch table */

SQLExecDirect(hstmt2, "select * from branch", SQL_NTS);
SQLBindCol(hstmt2, 1, ....)
SQLBindCol(hstmt2, 2, ....)
SQLFetch(hstmt2);

/* commit the transaction */

SQLTransact(henv, hdbc, SQL_COMMIT);

When a transaction is committed the unfetched data in the result sets associated with hstmt1 and hstmt2 are cleared (suppose the account and branch tables have more than one row).

7.4 Auto-Commit and Manual-Commit

In general, application programs want to control the termination of transactions. In this situation, manual-commit mode is needed.

ODBC defines many connection options, and one of them is SQL_AUTOCOMMIT. This connection option indicates whether auto-commit mode should be turned on or off. The default value of the SQL_AUTOCOMMIT option is on. This means that every statement is committed automatically.

To start transaction processing, you should turn off the SQL_AUTOCOMMIT option by using the SQLSetConnectOption ODBC function as follows:

SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF)

After this function call, the commit action is controlled by user. If the auto-commit mode is off and the transaction is not committed when the user calls SQLDisconnect, DBMaker will rollback the transaction and return a warning.

To get the value of the current auto-commit mode, you can use SQLGetConnectOption as follows:

SQLGetConnectOption(hdbc, SQL_AUTOCOMMIT, &optVal);

If the option value in optVal is SQL_AUTOCOMMIT_ON, then each SQL statement will be committed automatically after it is successfully executed.