Supporting Local Transactions

A session delimits transaction scope for a SQL Native Client OLE DB provider local transaction. When, at the direction of a consumer, the SQL Native Client OLE DB provider submits a request to a connected instance of SQL Server, the request constitutes a unit of work for the SQL Native Client OLE DB provider. Local transactions always wrap one or more units of work on a single SQL Native Client OLE DB provider session.

Using the default SQL Native Client OLE DB provider autocommit mode, a single unit of work is treated as the scope of a local transaction. Only one unit participates in the local transaction. When a session is created, the SQL Native Client OLE DB provider begins a transaction for the session. Upon successful completion of a work unit, the work is committed. On failure, any work begun is rolled back and the error is reported to the consumer. In either case, the SQL Native Client OLE DB provider begins a new local transaction for the session so that all work is conducted within a transaction.

The SQL Native Client OLE DB provider consumer can direct more precise control over local transaction scope by using the ITransactionLocal interface. When a consumer session initiates a transaction, all session work units between the transaction start point and the eventual Commit or Abort method calls are treated as an atomic unit. The SQL Native Client OLE DB provider implicitly begins a transaction when directed to do so by the consumer. If the consumer does not request retention, the session reverts to parent transaction-level behavior, most commonly autocommit mode.

The SQL Native Client OLE DB provider supports ITransactionLocal::StartTransaction parameters as follows.

Parameter

Description

isoLevel[in]

The isolation level to be used with this transaction. In local transactions, The SQL Native Client OLE DB provider supports:

• ISOLATIONLEVEL_UNSPECIFIED
• ISOLATIONLEVEL_CHAOS
• ISOLATIONLEVEL_READUNCOMMITTED
• ISOLATIONLEVEL_READCOMMITTED
• ISOLATIONLEVEL_REPEATABLEREAD
• ISOLATIONLEVEL_CURSORSTABILITY
• ISOLATIONLEVEL_REPEATABLEREAD
• ISOLATIONLEVEL_SERIALIZABLE
• ISOLATIONLEVEL_ISOLATED
• ISOLATIONLEVEL_SNAPSHOT

Note:

For SQL Server 2005 databases, ISOLATIONLEVEL_SNAPSHOT is valid for the isoLevel argument whether or not versioning is enabled for the database. However, an error will occur if the user attempts to execute a statement and versioning is not enabled and/or the database is not read-only. In addition, the error XACT_E_ISOLATIONLEVEL will occur if ISOLATIONLEVEL_SNAPSHOT is specified as the isoLevel when connected to a version of SQL Server prior to SQL Server 2005.

isoFlags[in]

The SQL Native Client OLE DB provider returns an error for any value other than zero.

pOtherOptions[in]

If not NULL, the SQL Native Client OLE DB provider requests the options object from the interface. The SQL Native Client OLE DB provider returns XACT_E_NOTIMEOUT if the options object's ulTimeout member is not zero. The SQL Native Client OLE DB provider ignores the value of the szDescription member.

pulTransactionLevel[out]

If not NULL, the SQL Native Client OLE DB provider returns the nested level of the transaction.

For local transactions, the SQL Native Client OLE DB provider implements ITransaction::Abort parameters as follows.

Parameter	Description
pboidReason[in]	Ignored if set. Can safely be NULL.
fRetaining[in]	When TRUE, a new transaction is implicitly begun for the session. The transaction must be committed or terminated by the consumer. When FALSE, the SQL Native Client OLE DB provider reverts to autocommit mode for the session.
fAsync[in]	Asynchronous abort is not supported by the SQL Native Client OLE DB provider. The SQL Native Client OLE DB provider returns XACT_E_NOTSUPPORTED if the value is not FALSE.

For local transactions, the SQL Native Client OLE DB provider implements ITransaction::Commit parameters as follows.

Parameter	Description
fRetaining[in]	When TRUE, a new transaction is implicitly begun for the session. The transaction must be committed or terminated by the consumer. When FALSE, the SQL Native Client OLE DB provider reverts to autocommit mode for the session.
grfTC[in]	Asynchronous and phase one returns are not supported by the SQL Native Client OLE DB provider. The SQL Native Client OLE DB provider returns XACT_E_NOTSUPPORTED for any value other than XACTTC_SYNC.
grfRM[in]	Must be 0.

The SQL Native Client OLE DB provider rowsets on the session are preserved on a local commit or abort operation based on the values of the rowset properties DBPROP_ABORTPRESERVE and DBPROP_COMMITPRESERVE. By default, these properties are both VARIANT_FALSE and all SQL Native Client OLE DB provider rowsets on the session are lost following an abort or commit operation.

The SQL Native Client OLE DB provider does not implement the ITransactionObject interface. A consumer attempt to retrieve a reference on the interface returns E_NOINTERFACE.

This example uses ITransactionLocal.

// Interfaces used in the example.
IDBCreateSession*   pIDBCreateSession   = NULL;
ITransaction*       pITransaction       = NULL;
IDBCreateCommand*   pIDBCreateCommand   = NULL;
IRowset*            pIRowset            = NULL;

HRESULT             hr;

// Get the command creation and local transaction interfaces for the
// session.
if (FAILED(hr = pIDBCreateSession->CreateSession(NULL,
     IID_IDBCreateCommand, (IUnknown**) &pIDBCreateCommand)))
    {
    // Process error from session creation. Release any references and
    // return.
    }

if (FAILED(hr = pIDBCreateCommand->QueryInterface(IID_ITransactionLocal,
    (void**) &pITransaction)))
    {
    // Process error. Release any references and return.
    }

// Start the local transaction.
if (FAILED(hr = ((ITransactionLocal*) pITransaction)->StartTransaction(
    ISOLATIONLEVEL_REPEATABLEREAD, 0, NULL, NULL)))
    {
    // Process error from StartTransaction. Release any references and
    // return.
    }

// Get data into a rowset, then update the data. Functions are not
// illustrated in this example.
if (FAILED(hr = ExecuteCommand(pIDBCreateCommand, &pIRowset)))
    {
    // Release any references and return.
    }

// If rowset data update fails, then terminate the transaction, else
// commit. The example doesn't retain the rowset.
if (FAILED(hr = UpdateDataInRowset(pIRowset, bDelayedUpdate)))
    {
    // Get error from update, then terminate.
    pITransaction->Abort(NULL, FALSE, FALSE);
    }
else
    {
    if (FAILED(hr = pITransaction->Commit(FALSE, XACTTC_SYNC, 0)))
        {
        // Get error from failed commit.
        }
    }

if (FAILED(hr))
    {
    // Update of data or commit failed. Release any references and
    // return.
    }

// Release any references and continue.

See Also

Concepts

Transactions
Working with Snapshot Isolation

Other Resources

Understanding Row Versioning-Based Isolation Levels
Isolation Levels in the Database Engine
Locking in the Database Engine

Help and Information

Getting SQL Server 2005 Assistance