Easysoft Blog

Thu, 31 October 2019

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:

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, 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 Easysoft / Tuxedo / SQL Server XA Application

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

  1. On your Tuxedo machine, install the SQL Server ODBC driver.
  2. 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
  3. 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.

  1. $ cd ~
    $ mkdir simpdir
    $ cd simpdir
    $ touch simpcl.c simpserv.c ubbsimple
  2. 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);
    }
  3. 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);
            }
    }
  4. 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
  5. 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
  6. 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"
  7. Build the sample server:
    buildserver -r EASYSOFT_SQLSERVER_ODBC -s EXECUTE -o simpserv -f "simpserv.c \
    -L/usr/local/easysoft/sqlserver/lib -lessqlsrv -lodbc"
  8. Create the TUXCONFIG file for the sample application:
    tmloadcf ubbsimple
  9. Create a Tuxedo logging device for the sample application:
    $ tmadmin -c
    > crdl -z /home/myuser/simpdir/tuxlog -b 512
  10. Build a Tuxedo transaction manager that interfaces with the SQL Server ODBC driver:
    $ buildtms -o mySQLSERVER_TMS -r EASYSOFT_SQLSERVER_ODBC
  11. Boot the sample server:
    $ tmboot
  12. 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  |                                                                                         
    +------------+--------------+
  13. If you see the data in the SQL Server table, shut down the sample server:
    tmshutdown

    Otherwise, consult ULOG.nnn in sample application directory.