Enlisting SQL Server in a distributed XA transaction

How to access SQL Server in the context of an XA transaction with the Easysoft SQL Server ODBC driver and Oracle Tuxedo.

Introduction

Why distributed transactions are needed

A transaction is a series of actions performed as a single operation in which either all of the actions are performed or none of them are. A transaction ends with a commit action that makes the changes permanent. If any of the changes cannot be committed, the transaction will roll back, reversing all the changes.

A distributed transaction is a transaction that may span multiple resources. For example, one or more databases or a database and a message queue. For the transaction to commit successfully, all of the individual resources must commit successfully; if any of them are unsuccessful, the transaction must roll back in all of the resources. For example, a distributed transaction might consist of a money transfer between two bank accounts, hosted by different banks, and so also on different databases. You would not want either transaction committed without a guarantee that both will complete successfully. Otherwise, data may be duplicated (if the insert completes and the delete fails) or lost (if the delete completes and the insert fails).

Whenever an application needs to access or update the data in multiple transactional resources therefore, it should use a distributed transaction. It is possible to use a separate transaction on each of the resources, but this approach is error-prone. If the transaction in one resource commits successfully, but another fails and must roll back, the first transaction can no longer be rolled back, so the state of the application becomes inconsistent. If one resource commits successfully but the system crashes before the other resource can commit successfully, the application again is inconsistent.

XA

The X/Open Distributed Transaction Processing (DTP) model defines an architecture for distributed transaction processing. In the DTP architecture, a coordinating transaction manager tells each resource how to process a transaction, based on its knowledge of all the resources participating in the transaction. Resources that normally manage their own transaction commit and recovery delegate this task to the transaction manager.

The architecture's XA specification provides an open standard that ensures interoperability across conformant transactional middleware and database products. These different resources are therefore able to participate together in a distributed transaction.

The DTP model includes three interrelated components:

An Application Program that defines transaction boundaries and specifies actions that constitute a transaction.
Resource Managers such as databases or file systems that provide access to shared resources.
A Transaction Manager that assigns identifiers to transactions, monitors their progress, and takes responsibility for transaction completion and failure recovery.

The XA standard defines the two-phase commit protocol and the interface used for communication between a Transaction Manager and a Resource Manager. The two-phase commit protocol provides an all-or-nothing guarantee that all participants involved in the transaction either commit or roll back together. The entire transaction commits or the entire transaction rolls back, therefore.

The two-phase commit consists of a prepare phase and a commit phase. During the prepare phase, all participants in the transaction must agree to complete the changes required by the transaction. If any of the participants report a problem, the prepare phase will fail and the transaction will roll back. If the prepare phase is successful, phase two, the commit phase starts. During the commit phase, the Transaction Manager instructs all participants to commit the transaction.

SQL Server and XA

To enable XA support in SQL Server 2019 and later, follow the instructions in the "Running the MS DTC service" section contained in this document:

Understanding XA transactions

To enable XA support in earlier versions of SQL Server, follow the instructions in this document:

Configuring XA transactions in Microsoft SQL Server for IBM Business Process Manager (BPM)

The SQL Server ODBC driver has been tested with XA-enabled SQL Server 2016 and 2019 instances.

The Easysoft SQL Server ODBC driver

XA support was added to SQL Server ODBC driver in version 1.11.3. The driver's XA support has been tested with Oracle Tuxedo and SQL Server 2016 and 2019.

To enlist the SQL Server ODBC driver in an XA transaction, you need to use a structure named es_xa_context in your application. es_xa_context connects to the ODBC data source you have specified in your XA resource manager configuration and returns a connection handle. For example:

int ret;
SQLHANDLE hEnv, hConn;
ret = es_xa_context( NULL, &hEnv, &hConn );

In Tuxedo, the ODBC data source that es_xa_context connects to is specified in the Resource Manager OPENINFO string in the Tuxedo config file. In this example, it's "SQLSERVER_SAMPLE":

OPENINFO="EASYSOFT_SQLSERVER_ODBC:DSN=SQLSERVER_SAMPLE"

The driver-defined XA Resource Manager name and XA switch are EASYSOFT_SQLSERVER_ODBC and essql_xaosw.

In Tuxedo, you specify these in the Tuxedo Resource Manager definition file, ${TUXDIR}/udataobj/RM. For example:

EASYSOFT_SQLSERVER_ODBC:essql_xaosw:-L/usr/local/easysoft/sqlserver/lib -lessqlsrv -lodbcinst

Sample XA application

First, set up a SQL Server ODBC driver data source that connects to an XA-enabled SQL Server instance:

On your Tuxedo machine, install the SQL Server ODBC driver.

Create a SQL Server ODBC driver data source in odbc.ini. For example:

[SQLSERVER_SAMPLE]
Driver=Easysoft ODBC-SQL Server
Description=Easysoft SQL Server ODBC driver
Server=mymachine\myxaenabledinstance
User=mydomain\myuser
Password=mypassword
Database=XA1

Create a sample table for the Tuxedo application:

$ /usr/local/easysoft/unixODBC/bin/isql.sh -v SQLSERVER_SAMPLE
SQL> CREATE TABLE [dbo].[tx_test1]([i] [int] NULL,[c] [varchar](100) NULL)

Create and run the sample Tuxedo XA Application.

$ cd ~
$ mkdir simpdir
$ cd simpdir
$ touch simpcl.c simpserv.c ubbsimple

Add these lines to simpcl.c:

#include <stdio.h>
#include "atmi.h"               /* TUXEDO  Header File */


#if defined(__STDC__) || defined(__cplusplus)
main(int argc, char *argv[])
#else
main(argc, argv)
int argc;
char *argv[];
#endif

{

        char *sendbuf, *rcvbuf;
        long sendlen, rcvlen;
        int ret;

        if(argc != 2) {
                (void) fprintf(stderr, "Usage: simpcl <SQL>\n");
                exit(1);
        }

        /* Attach to System/T as a Client Process */
        if (tpinit((TPINIT *) NULL) == -1) {
                (void) fprintf(stderr, "Tpinit failed\n");
                exit(1);
        }

        sendlen = strlen(argv[1]);

        /* Allocate STRING buffers for the request and the reply */

        if((sendbuf = (char *) tpalloc("STRING", NULL, sendlen+1)) == NULL) {
                (void) fprintf(stderr,"Error allocating send buffer\n");
                tpterm();
                exit(1);
        }

        if((rcvbuf = (char *) tpalloc("STRING", NULL, sendlen+1)) == NULL) {
                (void) fprintf(stderr,"Error allocating receive buffer\n");
                tpfree(sendbuf);
                tpterm();
                exit(1);
        }

        (void) strcpy(sendbuf, argv[1]);

        /* Request the service EXECUTE, waiting for a reply */
        ret = tpcall("EXECUTE", (char *)sendbuf, 0, (char **)&rcvbuf, &rcvlen, (long)0);

        if(ret == -1) {
                (void) fprintf(stderr, "Can't send request to service EXECUTE\n");
                (void) fprintf(stderr, "Tperrno = %d\n", tperrno);
                tpfree(sendbuf);
                tpfree(rcvbuf);
                tpterm();
                exit(1);
        }

        (void) fprintf(stdout, "Returned string is: %s\n", rcvbuf);

        /* Free Buffers & Detach from System/T */
        tpfree(sendbuf);
        tpfree(rcvbuf);
        tpterm();
        return(0);
}

Add these lines to simpserv.c:

#include <stdio.h>
#include <ctype.h>
#include <atmi.h>       /* TUXEDO Header File */
#include <userlog.h>    /* TUXEDO Header File */
#include <xa.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>


/* tpsvrinit is executed when a server is booted, before it begins
   processing requests.  It is not necessary to have this function.
   Also available is tpsvrdone (not used in this example), which is
   called at server shutdown time.
*/


int tpsvrinit(int argc, char *argv[])
{
        int ret;

        /* Some compilers warn if argc and argv aren't used. */
        argc = argc;
        argv = argv;

        /* simpapp is non-transactional, so there is no need for tpsvrinit()
           to call tx_open() or tpopen().  However, if this code is modified
           to run in a Tuxedo group associated with a Resource Manager then
           either a call to tx_open() or a call to tpopen() must be inserted
           here.
        */

        /* userlog writes to the central TUXEDO message log */
        userlog("Welcome to the simple server");

        ret = tpopen();

        userlog("tpopen returned %d, error=%x", ret, tperrno );

        return(0);
}

void tpsvrdone( void )
{
        int ret;

        ret = tpclose();

        userlog("tpclose returned %d", ret);
}

/* This function performs the actual service requested by the client.
   Its argument is a structure containing among other things a pointer
   to the data buffer, and the length of the data buffer.
*/

xa_open_entry() call.
int es_xa_context( int* rmid, SQLHANDLE* henv, SQLHANDLE* hdbc );

void EXECUTE(TPSVCINFO *rqst)
{
        int ret;
        char *result;
        SQLHANDLE hStmt;
        char str[ 256 ];
        SQLHANDLE hEnv, hConn;
        SQLSMALLINT slen;

        ret = es_xa_context( NULL, &hEnv, &hConn );

        userlog("es_xa_context returns %d, hEnv = %p, hConn = %p", ret, hEnv, hConn );

        if ( ret != 0 ) {
                result = tpalloc( "STRING", "*", 128 );
                sprintf( result, "es_xa_context returned %d", ret );

                /* Return the transformed buffer to the requestor. */
                tpreturn(TPSUCCESS, 0, result, strlen( result ), 0);
        }
        else {

                ret = tpbegin( 0, 0 );

                ret = SQLAllocHandle( SQL_HANDLE_STMT, hConn, &hStmt );

                ret = SQLExecDirect( hStmt, rqst -> data, rqst -> len );

                ret = SQLFreeHandle( SQL_HANDLE_STMT, hStmt );

                ret = tpcommit( 0 );

                result = tpalloc( "STRING", "*", 128 );
                sprintf( result, "tpcommit returns %d", ret );

                /* Return the transformed buffer to the requestor. */
                tpreturn(TPSUCCESS, 0, result, strlen( result ), 0);
        }
}

Add these lines to ubbsimple:

*RESOURCES
IPCKEY          123456

DOMAINID        simpapp
MASTER          simple
MAXACCESSERS    20
MAXSERVERS      10
MAXSERVICES     10
MODEL           SHM
LDBAL           N

*MACHINES
DEFAULT:
                APPDIR="/home/myuser/simpdir"
                TUXCONFIG="/home/myuser/simpdir/tuxconfig"
                TUXDIR="/home/myuser/OraHome/tuxedo12.2.2.0.0"

mymachine         LMID=simple

TLOGNAME=TLOG
TLOGDEVICE="/home/myuser/simpdir/tuxlog"


*GROUPS
GROUP1
        LMID=simple     GRPNO=1 OPENINFO=NONE
        TMSNAME=mySQLSERVER_TMS
        OPENINFO="EASYSOFT_SQLSERVER_ODBC:DSN=SQLSERVER_SAMPLE"

*SERVERS
DEFAULT:
                CLOPT="-A"

simpserv        SRVGRP=GROUP1 SRVID=1

*SERVICES
EXECUTE

Set your environment:

export TUXDIR=/home/myuser/OraHome/tuxedo12.2.2.0.0
export TUXCONFIG=/home/myuser/simpdir/tuxconfig
export PATH=$PATH:$TUXDIR/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$TUXDIR/lib:/usr/local/easysoft/unixODBC/lib: \
/usr/local/easysoft/sqlserver/lib:/usr/local/easysoft/lib

Build the sample client:
```
buildclient -o simpcl -f simpcl.c
```
If you get the error "undefined reference to dlopen" when building the client, try this command instead:
```
buildclient -o simpcl -f "-Xlinker --no-as-needed simpcl.c"
```

Build the sample server:

buildserver -r EASYSOFT_SQLSERVER_ODBC -s EXECUTE -o simpserv -f "simpserv.c \
-L/usr/local/easysoft/sqlserver/lib -lessqlsrv -lodbc"

Create the TUXCONFIG file for the sample application:
```
tmloadcf ubbsimple
```
Create a Tuxedo logging device for the sample application:
```
$ tmadmin -c
> crdl -z /home/myuser/simpdir/tuxlog -b 512
```
Build a Tuxedo transaction manager that interfaces with the SQL Server ODBC driver:
```
$ buildtms -o mySQLSERVER_TMS -r EASYSOFT_SQLSERVER_ODBC
```
Boot the sample server:
```
$ tmboot
```

Test the sample application:

./simpcl "insert into tx_test1 values( 1, 'hello world' )"
/usr/local/easysoft/unixODBC/bin/isql.sh -v SQLSERVER_SAMPLE
SQL> select * from tx_test1
+------------+--------------+
| i          | c            |                                                                                                   
+------------+--------------+
| 1          | hello world  |                                                                                         
+------------+--------------+

If you see the data in the SQL Server table, shut down the sample server:
```
tmshutdown
```
Otherwise, consult ULOG.nnn in sample application directory.

About Easysoft
Contact us
About us
Clients
Blog
Careers

Legal
Privacy statement

© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.

Enlisting SQL Server in a distributed XA transaction

Introduction

Why distributed transactions are needed

XA

SQL Server and XA

The Easysoft SQL Server ODBC driver

Sample XA application

About Easysoft

Products

Services

Licensing

Support

Search

Popular Resources

Legal