Supporting Local Transactions

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Download OLE DB driver

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

Using the default OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server begins a new local transaction for the session so that all work is conducted within a transaction.

The OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server supports ITransactionLocal::StartTransaction parameters as follows.

Parameter	Description
isoLevel[in]	The isolation level to be used with this transaction. In local transactions, the OLE DB Driver for SQL Server supports the following: ISOLATIONLEVEL_UNSPECIFIED ISOLATIONLEVEL_CHAOS ISOLATIONLEVEL_READUNCOMMITTED ISOLATIONLEVEL_READCOMMITTED ISOLATIONLEVEL_REPEATABLEREAD ISOLATIONLEVEL_CURSORSTABILITY ISOLATIONLEVEL_REPEATABLEREAD ISOLATIONLEVEL_SERIALIZABLE ISOLATIONLEVEL_ISOLATED ISOLATIONLEVEL_SNAPSHOT Note: Beginning with SQL Server 2005 (9.x), 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 earlier than SQL Server 2005 (9.x).
isoFlags[in]	The OLE DB Driver for SQL Server returns an error for any value other than zero.
pOtherOptions[in]	If not NULL, the OLE DB Driver for SQL Server requests the options object from the interface. The OLE DB Driver for SQL Server returns XACT_E_NOTIMEOUT if the options object's ulTimeout member is not zero. The OLE DB Driver for SQL Server ignores the value of the szDescription member.
pulTransactionLevel[out]	If not NULL, the OLE DB Driver for SQL Server returns the nested level of the transaction.

For local transactions, the OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server reverts to autocommit mode for the session.
fAsync[in]	Asynchronous abort is not supported by the OLE DB Driver for SQL Server. The OLE DB Driver for SQL Server returns XACT_E_NOTSUPPORTED if the value is not FALSE.

For local transactions, the OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server reverts to autocommit mode for the session.
grfTC[in]	Asynchronous and phase one returns are not supported by the OLE DB Driver for SQL Server. The OLE DB Driver for SQL Server returns XACT_E_NOTSUPPORTED for any value other than XACTTC_SYNC.
grfRM[in]	Must be 0.

The OLE DB Driver for SQL Server 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 OLE DB Driver for SQL Server rowsets on the session are lost following an abort or commit operation.

The OLE DB Driver for SQL Server 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

Transactions
Working with Snapshot Isolation