15 Enfermería y TARGA:

• Coordinated transactions

Note: The transaction-related interfaces are not exposed if the database is unaudited or if the INDEPENDENTTRANS option is reset. If the database is audited and the INDEPENDENTTRANS option is set, only local transactions are permitted in inquiry mode.

Local Transactions

You must perform all update transactions in transaction state. Either manual-commit or auto-commit mode can be used. In manual-commit mode, the application makes explicit calls to start and commit transactions or to abort a transaction. In this mode, transactions are started using the StartTransaction method of the ITransactionLocal interface, and are committed or aborted using respectively the Commit or Abort methods of the ITransaction interface. In auto-commit mode, transactions are started and committed automatically by the OLE DB Data Provider for each update.

The OLE DB Data Provider supports the following isolation levels for transactions: • Read Uncommitted for both inquiry and update modes

• Read Committed for both inquiry and update modes • Repeatable Read for update mode only

• Serializable for update mode only

The nested transactions supported by the OLE DB Data Provider are not really nested transactions in the strict sense of the term. When a new local transaction is started in a session that is in local transaction state, The OLE DB Data Provider establishes an Enterprise Database Server savepoint. The transaction to be rolled back to the savepoint of this is requested by the data consumer. However, if the data consumer initiates a call to commit on the nested transaction, the updates are not actually committed. The commit disables the savepoint associated with the nested

A nested transaction begins when the data consumer calls

ITransactionLocal::StartTransaction on a session with an existing local transaction. A data consumer cannot start a local transaction on a session with an existing

coordinated transaction, and nested transactions are not supported within coordinated transactions.

Nested transactions do not involve transactions started in auto-commit mode. Transactions can be nested to a maximum depth of 16 levels.

Serializable Isolation Level

Serializable is the most restrictive of the four isolation levels. Isolation level Serializable implies that the database view, as seen by the transaction executing at this level, remains constant throughout the duration of the transaction. A transaction operating at the Serializable level guarantees that all concurrent transactions interact only in ways that produce the same result as if each transaction were entirely executed one after the other. For a transaction executing at this isolation level, dirty reads,

nonrepeatable reads, and phantoms reads are not possible.

The OLE DB Data Provider places structure locks on data sets affected by the

executing application. When a transaction at isolation level Serializable is initiated, the OLE DB worker attempts to obtain structure locks (including the global data set) by executing a SECURE STRUCTURE command. These structure locks prevent other applications from updating, inserting, or deleting rows in the data set for the duration of the transaction.

If any error including a DEADLOCK error is returned, the transaction is not initiated and an error is returned to the client application. The transactiontimeout value determines the maximum amount of time the application waits to begin a transaction at isolation level Serializable. If the application opens other data sets after such a transaction has begun, the OLE DB worker attempts to obtain structure locks on those data sets. A rowset open error is returned to the application if the structure lock cannot be obtained.

Note: Applications should only use the Serializable isolation level when necessary and should keep the duration of the transaction as short as possible, since at this isolation level concurrency can become very low. When a Commit call or an Abort call is made by the application, the worker frees all the secured structures by executing a FREE STRUCTURE command. This isolation level is only allowed for the outermost transaction. That is, nested transactions cannot use this isolation level.

Coordinated Transactions

Coordinated transactions are global transactions that span one or more databases and are controlled by a single transaction manager known as the coordinating transaction manager.

Coordinated transactions are based on a special protocol called two-phase commit. This same two-phase commit protocol is used by Open OLTP.

The OLE DB Data Provider implementation of coordinated transactions is based on the Transaction Internet Protocol (TIP). With TIP, each resource manager is served by a local participating transaction manager that works with the coordinating transaction manager to control the transaction. The essence of TIP is its two-pipe architecture in which one pipe is reserved for data transfer and the other for transaction control communication between transaction managers.

Note: Only the Windows 2000, Windows XP, and Windows Server 2003 operating systems support Transaction Internet Protocol (TIP). Refer to “Using Transaction Internet Protocol (TIP)” later in this section for activation instructions.

The Microsoft Distributed Transaction Coordinator (MS DTC) is the coordinating transaction manager and the MCP transaction manager serves as the participating transaction manager on ClearPath MCP servers.

A client calls the MS DTC and makes a request to start a coordinated transaction. The MS DTC creates a transaction by generating a TIP transaction URL that uniquely identifies the transaction and the computer on which the MS DTC is running and returns a pointer to the transaction.

Then the client calls the provider, requesting it to join the transaction. The provider calls the MS DTC to request the TIP transaction URL and passes this URL to the worker, which passes it to the transaction manager which then “pulls” the transaction from the MS DTC.

After the updates have been completed, the client calls the MS DTC to commit the transaction. The MS DTC calls each participating transaction manager in turn, asking each to prepare to commit. This is the first phase of the two-phase commit protocol. If this phase is successful, the MS DTC calls each transaction manager again, asking each to commit. This is the second phase of the two-phase commit protocol.

If any participating transaction manager rejects the “prepare to commit” request, the transaction is aborted by the MS DTC and all participating transaction managers are told to roll back the transaction.

The TIP transaction URL that identifies the computer on which the MS DTC is running does so by name, which means that the ClearPath MCP server must be able to resolve this computer name to an IP address. This requires that there be a Domain Name Service (DNS) entry in the domain name service for this server. Furthermore, to be practical, the IP address of the computer running the MS DTC must not change. Therefore, this server must be assigned a static IP address.

Coordinated transactions are only supported in the Windows 2000 environment. To access an Enterprise Database Server database within a coordinated transaction, the database must have these options set: INDEPENDENTTRANS, OPENOLTP, and REAPPLYCOMPLETED.

Visual Basic and ADO do not support calls to start and commit coordinated

transactions. These actions must be done with declarative transactions using COM+ (Component Services), which uses the Transaction Server to make the actual calls.

Deadlocks and Global Transactions

Deadlocks involving global transactions across multiple databases can only be resolved by timeouts. Therefore, to avoid deadlocks, applications involved in global transactions should access tables and records in the same order. Failure to do so can result in significant performance degradation should deadlocks occur.

This type of deadlock is not a problem specific to global transactions. It is an

application deadlock that the individual database management system (DBMS) stacks cannot resolve due to each stack maintaining a WAIT-FOR graph. The application deadlock cannot be resolved by a transaction manager because the transaction manager is not the lock manager. Nevertheless, record access in the same order will prevent this application deadlock condition. For example, if clients each want to update or access records 5 and 7 in the four databases A, B, C, D, they should access the records in the following order: A(5), A(7), B(5), B(7), C(5), C(7), D(5), D(7).

Programming Tip

The application must close every record set and every connection that it opened. Failure to do so will cause a buildup in the number of active MCP workers. These workers will not exit the mix until the application exits and Windows closes the ports. The following Visual Basic example is provided to assist you in managing this

condition. Add the following code to your application and call Wrap_Up whenever you have finished with a connection.

Note: Set rs and cn in the following example to names you use in your application: Private Sub Wrap_Up

On Error Resume Next

If Not (rs is Nothing) Then rs.Close

Set rs = Nothing End If

If Not (cn is Nothing) Then cn.Close

Set cn = Nothing End If

Err.Clear End Sub

Using Transaction Internet Protocol (TIP)

Notes:

• Microsoft security bulletin MS05-051 describes security update 902400 which applies some security-related changes to Transaction Internet Protocol (TIP) functionality in Microsoft Distributed Transaction Coordinator (MS-DTC) including the disablement TIP.

• Microsoft Knowledge Base article KB 908620 describes the new method for enabling TIP. If you have installed security update 902400 or are using

Windows Server 2008 or later, you must use the directions in article KB 908620 instead of the following procedure. See

http://support.microsoft.com/kb/908620.

Only the Windows 2000, Windows XP, and Windows Server 2003 operating systems support Transaction Internet Protocol (TIP). For Windows 2000, TIP is active by default. For Windows XP and Windows Server 2003, TIP must be manually activated using the following procedure:

1. From the Control Panel, double-click Administrative Tools and then choose

Component Services.

The Component Services window is displayed.

2. In the Console Root folder, expand the Component Services node and then expand the Computers folder.

3. Right-click My Computer and choose Properties. The My Computer Properties dialog box appears. 4. Select the MSDTC tab.

5. Choose the Security Configuration button in the Transaction Configuration group box.

6. Select Transaction Internet Protocol (TIP) Transactions. 7. Choose OK.

Note: If the MSDTC service is running, a message appears indicating that the service will be stopped and restarted.

8. Choose OK as needed to return to the Component Services window. 9. Choose Exit from the Console menu.