Supporting Local Transactions in SQL Server Native Client

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

A session delimits transaction scope for a SQL Server Native Client OLE DB provider local transaction. When, at the direction of a consumer, the SQL Server 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 Server Native Client OLE DB provider. Local transactions always wrap one or more units of work on a single SQL Server Native Client OLE DB provider session.

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

The SQL Server 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 Server 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 Server 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 Server Native Client OLE DB provider 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 SQL Server Native Client OLE DB provider returns an error for any value other than zero.
pOtherOptions[in]	If not NULL, the SQL Server Native Client OLE DB provider requests the options object from the interface. The SQL Server Native Client OLE DB provider returns XACT_E_NOTIMEOUT if the options object's ulTimeout member is not zero. The SQL Server Native Client OLE DB provider ignores the value of the szDescription member.
pulTransactionLevel[out]	If not NULL, the SQL Server Native Client OLE DB provider returns the nested level of the transaction.

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

For local transactions, the SQL Server 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 Server 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 Server Native Client OLE DB provider. The SQL Server Native Client OLE DB provider returns XACT_E_NOTSUPPORTED for any value other than XACTTC_SYNC.
grfRM[in]	Must be 0.

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

The SQL Server 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

Transactions
Working with Snapshot Isolation