MDAC 2.5 SDK - OLE DB Providers
OLE DB Provider for SQL Server


 

Supporting Local Transactions

A session delimits transaction scope for a SQLOLEDB local transaction. When, at a consumer's direction, SQLOLEDB submits a request to a connected Microsoft® SQL Server™ installation, the request constitutes a unit of work for SQLOLEDB. Local transactions always wrap one or more units of work on a single SQLOLEDB session.

Using the default SQLOLEDB auto-commit 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, SQLOLEDB 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, SQLOLEDB begins a new local transaction for the session so that all work is conducted within a transaction.

The SQLOLEDB 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 ITransaction::Commit or ITransaction::Abort method calls are treated as an atomic unit. SQLOLEDB 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 auto-commit mode.

SQLOLEDB supports ITransactionLocal::StartTransaction parameters as described in the following table.

Parameter Description
isoLevel In local transactions, SQLOLEDB supports ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_ISOLATED, and the synonyms ISOLATIONLEVEL_CURSORSTABILITY and ISOLATIONLEVEL_SERIALIZABLE.
IsoFlags SQLOLEDB returns an error for any value other than zero.
POtherOptions If not NULL, SQLOLEDB requests the options object from the interface. SQLOLEDB returns XACT_E_NOTIMEOUT if the options object's ulTimeout member is not zero. SQLOLEDB ignores the value of the szDescription member.
PulTransactionLevel If not NULL, SQLOLEDB returns the nested level of the transaction.

For local transactions, SQLOLEDB implements ITransaction::Abort parameters as follows.

Parameter Description
pboidReason Ignored if set. Can safely be NULL.
Fretaining When TRUE, a new transaction is implicitly begun for the session. The transaction must be committed or terminated by the consumer. When FALSE, SQLOLEDB reverts to auto-commit mode for the session.
Fasync Asynchronous abort is not supported by SQLOLEDB. SQLOLEDB returns XACT_E_NOTSUPPORTED if the value is not FALSE.

For local transactions, SQLOLEDB implements ITransaction::Commit parameters as described in the following table.

Parameter Description
fRetaining When TRUE, a new transaction is implicitly begun for the session. The transaction must be committed or terminated by the consumer. When FALSE, SQLOLEDB reverts to auto-commit mode for the session.
GrfTC Asynchronous and phase one returns are not supported by SQLOLEDB. SQLOLEDB returns XACT_E_NOTSUPPORTED for any value other than XACTTC_SYNC.
GrfRM Must be 0.

SQLOLEDB 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, both of these properties are VARIANT_FALSE, and all SQLOLEDB rowsets on the session are lost following an abort or commit operation.

SQLOLEDB does not implement the ITransactionObject interface. A consumer attempt to retrieve a reference on the interface returns E_NOINTERFACE.

The following 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, and 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, 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.