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, follow the instructions in the "Running the MS DTC service" section contained in this document:
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:
- 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.
