InformIT: Visual C++ 6 Unleashed

Table of Contents

Transactions

In many cases, it is necessary to have several different SQL statements operate together as a single transaction—that is, if one of the operations in a transaction fails, none of the other operations should affect the database. For example, if you are processing a sales order, you will want to update both your shipping table and your billing table. If one of these updates fails, and the other is entered into the database, you could easily end up billing for things that were never sent, or worse yet, shipping free stuff. Although creative use of this feature may have some beneficial effects on your short-term revenues, it's probably not a good career move.

ODBC Commit Modes

In ODBC, transactions are handled in one of two different ways, depending on the current commit mode of the connection. The connection can be set to either auto-commit mode (the default) or manual-commit mode. The commit mode for a connection is set by calling SQLSetConnectAttr() for the SQL_ATTR_AUTOCOMMIT option.

Auto-Commit Mode

The default mode for a new connection is auto-commit, which is supported by all drivers. In this mode, each statement operates as a separate transaction, the driver will take care of committing each operation on the database automatically, and you really don't have to worry about transaction processing at all.

If you submit a batch of SQL statements in a single SQLExecute() call, ODBC doesn't define whether this is treated as a single transaction or whether each statement is a separate transaction. If you want to send a batch as a transaction, use manual-commit mode.

Manual-Commit Mode

In cases where you want to ensure that multiple SQL statements can be performed as a transaction, you should always use manual-commit mode, which requires your application to explicitly end a transaction with a call to SQLEndTran().

Once again, this isn't supported by all drivers. You can check on the current driver's transaction support by calling SQLGetInfo() for the SQL_TXN_CAPABLE option. In addition, the SQL_MULTIPLE_ACTIVE_TXN option will tell you whether you can have multiple transactions pending at the same time.

To make use of multiple active transactions, you must have multiple connections because only one transaction is ever in progress for each connection.

Your application doesn't need to explicitly begin a transaction. The driver will begin one automatically when the connection is made, when you switch to manual-commit mode, or when the previous transaction is completed.

You will, however, complete each transaction with a call to SQLEndTran():

SQLRETURN SQLEndTran(SQLSMALLINT HandleType, SQLHANDLE Handle,SQLSMALLINT CompletionType);

In most cases, you will set HandleType to SQL_HANDLE_DBC and pass the current connec tion handle in Handle. This will end the transaction on the current connection handle. In addition, you may also specify SQL_HANDLE_ENV and pass your environment handle. This will end the current transactions for all connections associated with this environment. This doesn't, however, combine the transactions for all connections into a single atomic operation.

The CompletionType parameter specifies how to complete the transaction. If this is set to SQL_COMMIT, any changes made in this transaction are written to the database, whereas a value of SQL_ROLLBACK will cause any changes made in this transaction to be rolled back, as if they never occurred at all.

When you end a transaction with SQLEndTran(), different drivers may choose to close any open cursors for the connection, as well as closing the access plans for these cursors. To find the behavior that the current driver supports, you can call SQLGetInfo() for the SQL_CURSOR_COMMIT_BEHAVIOR and SQL_CURSOR_ROLLBACK_BEHAVIOR options.

Transaction Isolation Levels

When you are working with a data source that may have multiple transactions active at one time (from within your application or from other users) you should be concerned with the transaction isolation level, which determines how concurrent transactions may interact with each other. In general, this interaction is described by which of the following—generally undesirable—conditions may occur:

Dirty reads involve an operation that reads data that isn't yet committed, and may eventually be rolled back.
Nonrepeatable reads occur when a transaction reads the same row twice, but may receive different data because another transaction has changed something.
Phantoms occur when a row has been changed to match a query, but it was not selected in an initial query.

You set the current transaction isolation level for a connection by calling SQLSetConnectAttr() for the SQL_ATTR_TXN_ISOLATION option. This can be set to one of the following values:

SQL_TXN_READ_UNCOMMITTED—Any of the above inconsistencies may occur.
SQL_TXT_READ_COMMITTED—Dirty reads are prevented.
SQL_TXN_REPEATABLE_READ[md]Dirty reads and nonrepeatable reads are prevented.
SQL_TXN_SERIALIZABLE—All of the above anomalies are prevented.

Once again, not all ODBC drivers are created equal—you can determine the isolation modes that are supported by calling SQLGetInfo() for the SQL_TXN_ISOLATION_OPTION. You can also retrieve the default level by using the SQL_DEFAULT_TXN_ISOLATION option.

Although higher isolation levels can prevent some inconsistencies in the data, they may also introduce a greater amount of overhead, thus affecting your application's performance—and that of other apps that use the same data source. When choosing an isolation level to use, you should weigh this performance cost against the potentials for data inconsistency.

Cursor Concurrency Types

It is in the nature of higher isolation levels to limit inconsistencies in the data by limiting the concurrency of the data source—that is, how multiple operations may occur on the data source simultaneously. You can optimize the concurrency of your application's oper ations by setting the concurrency type of the cursor that is used with a statement handle.

This is done by calling SQLSetStmtAttr() for the SQL_ATTR_CONCURRENCY option, which can have the following values:

SQL_CONCUR_READ_ONLY indicates the cursor is read-only.
SQL_CONCUR_LOCK uses the lowest level of locking sufficient to ensure that a row may be updated.
SQL_CONCUR_ROWVER uses optimistic concurrency control, based on row version.
SQL_CONCUR_VALUES uses optimistic concurrency control, using values.

You can determine the available options for the current driver by calling SQLGetInfo()for the SQL_SCROLL_CONCURRENCY option.

The following example uses the menu resource and the ClassWizard to generate menu functions that control transactions. The code added to the four menu options to turn transactions on, turn transactions off, commit, and rollback are defined in gray below:

void CODBCTestView::OnViewUsetransactions() {


        SQLRETURN sr = SQLSetConnectAttr(hDbConn, SQL_ATTR_AUTOCOMMIT,
                (void*)SQL_AUTOCOMMIT_OFF , SQL_IS_UINTEGER);
        if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
                displayODBCError(sr, "Error in using transactions");


}
void CODBCTestView::OnViewUseautocommit() {


        SQLRETURN sr = SQLSetConnectAttr(hDbConn, SQL_ATTR_AUTOCOMMIT,
                (void*)SQL_AUTOCOMMIT_ON , SQL_IS_UINTEGER);
        if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
                displayODBCError(sr, "Error in turning transactions off");


}
void CODBCTestView::OnViewCommit() {


        SQLRETURN sr = SQLEndTran(SQL_HANDLE_DBC, hDbConn, SQL_COMMIT);
        if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
                displayODBCError(sr, "Error in commit");


}
void CODBCTestView::OnViewRollback() {


        SQLRETURN sr = SQLEndTran(SQL_HANDLE_DBC, hDbConn, SQL_ROLLBACK);
        if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
                displayODBCError(sr, "Error in rollback");


}

If you start using manual transactions, you can perform several inserts from the ODBCTest application, and then either commit them or roll them back.

Catalog Functions | Next Section

Previous Section