| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Supporting local transactions (Native Client OLE DB provider) |
Supporting Local Transactions in SQL Server Native Client |
markingmyname |
maghan |
03/14/2017 |
sql |
native-client |
reference |
|
[!INCLUDESQL Server Azure SQL Database Synapse Analytics PDW]
A session delimits transaction scope for a [!INCLUDEssNoVersion] Native Client OLE DB provider local transaction. When, at the direction of a consumer, the [!INCLUDEssNoVersion] Native Client OLE DB provider submits a request to a connected instance of [!INCLUDEssNoVersion], the request constitutes a unit of work for the [!INCLUDEssNoVersion] Native Client OLE DB provider. Local transactions always wrap one or more units of work on a single [!INCLUDEssNoVersion] Native Client OLE DB provider session.
Using the default [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] Native Client OLE DB provider begins a new local transaction for the session so that all work is conducted within a transaction.
The [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssVersion2005], 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 [!INCLUDEssNoVersion] earlier than [!INCLUDEssVersion2005]. |
| isoFlags[in] | The [!INCLUDEssNoVersion] Native Client OLE DB provider returns an error for any value other than zero. |
| pOtherOptions[in] | If not NULL, the [!INCLUDEssNoVersion] Native Client OLE DB provider requests the options object from the interface. The [!INCLUDEssNoVersion] Native Client OLE DB provider returns XACT_E_NOTIMEOUT if the options object's ulTimeout member is not zero. The [!INCLUDEssNoVersion] Native Client OLE DB provider ignores the value of the szDescription member. |
| pulTransactionLevel[out] | If not NULL, the [!INCLUDEssNoVersion] Native Client OLE DB provider returns the nested level of the transaction. |
For local transactions, the [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] Native Client OLE DB provider reverts to autocommit mode for the session. |
| fAsync[in] | Asynchronous abort is not supported by the [!INCLUDEssNoVersion] Native Client OLE DB provider. The [!INCLUDEssNoVersion] Native Client OLE DB provider returns XACT_E_NOTSUPPORTED if the value is not FALSE. |
For local transactions, the [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] Native Client OLE DB provider reverts to autocommit mode for the session. |
| grfTC[in] | Asynchronous and phase one returns are not supported by the [!INCLUDEssNoVersion] Native Client OLE DB provider. The [!INCLUDEssNoVersion] Native Client OLE DB provider returns XACT_E_NOTSUPPORTED for any value other than XACTTC_SYNC. |
| grfRM[in] | Must be 0. |
The [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] Native Client OLE DB provider rowsets on the session are lost following an abort or commit operation.
The [!INCLUDEssNoVersion] 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.