Understanding XA Transactions

The Microsoft SQL Server JDBC Driver provides support for Java Platform, Enterprise Edition/JDBC 2.0 optional distributed transactions. JDBC connections obtained from the SQLServerXADataSource class can participate in standard distributed transaction processing environments such as Java Platform, Enterprise Edition (Java EE) application servers. 

Remarks

The classes for the distributed transaction implementation are as follows:

Class

Implements

Description

com.microsoft.sqlserver.jdbc.SQLServerXADataSource

javax.sql.XADataSource

The class factory for distributed connections.

com.microsoft.sqlserver.jdbc.SQLServerXAResource

javax.transaction.xa.XAResource

The resource adaptor for the transaction manager.

Note

XA distributed transaction connections default to the Read Committed isolation level.

Guidelines and Limitations when Using XA Transactions

The following list provides information about specific limitations and guidance when using XA transactions:

  • On Windows XP:

    When you use XA transactions with SQL Server through the Microsoft SQL Server JDBC driver, you may notice that XA transactions might not work. This issue only applies when the SQL Server that is participating in the XA transaction is running on Windows XP. On the other hand, the client applications running on Windows XP connecting to a remote SQL Server that is not running on Windows XP can participate in XA transactions. For more information about how to resolve this issue, see a hotfix provided at Windows XP and XA Transactions.

  • On Windows Server 2003:

    When you use XA transactions together with Microsoft Distributed Transaction Coordinator (MS DTC) on Windows Server 2003, you may notice that the XAResource.setTransactionTimeout method does not work. To resolve this issue, you must apply a hotfix provided at MSDTC and XA Transactions to each SQL Server machine that is participating in XA transactions. Without this fix the only valid timeout value is the default value of 0, meaning infinite timeout.

The following additional guidelines apply to tightly coupled transactions:

  • When you use XA transactions together with Microsoft Distributed Transaction Coordinator (MS DTC), you may notice that the current version of MS DTC does not support tightly coupled XA branch behavior. For example, MS DTC has a one-to-one mapping between an XA branch transaction ID (XID) and an MS DTC transaction ID and the work that is performed by loosely coupled XA branches is isolated from one another.

    The hotfix provided at MSDTC and Tightly Coupled Transactions enables the support for tightly coupled XA branches where multiple XA branches with same global transaction ID (GTRID) are mapped to a single MS DTC transaction ID. This support enables multiple tightly coupled XA branches to see one another's changes in the resource manager, such as SQL Server.

  • A SSTRANSTIGHTLYCPLD flag allows the applications to use the tightly coupled XA transactions, which have different XA branch transaction IDs (XIDs) but have the same global transaction ID (GTRID). In order to use that feature, you must set the SSTRANSTIGHTLYCPLD on the flags parameter of the XAResource.start method:

    xaRes.start(xid, SQLServerXAResource.SSTRANSTIGHTLYCPLD);
    

Configuration Instructions

The following steps are required if you want to use XA data sources together with Microsoft Distributed Transaction Coordinator (MS DTC) for handling distributed transactions.

Note

The JDBC distributed transaction components are included in the xa directory of the JDBC driver installation. These components include the xa_install.sql and sqljdbc_xa.dll files.

Running the MS DTC Service

The MS DTC service should be marked Automatic in Service Manager to make sure that it is running when the SQL Server service is started. To enable MS DTC for XA transactions, you must follow these steps:

On Windows XP and Windows Server 2003:

  1. From Control Panel, open Administrative Tools, and then open Component Services. You can also click the Start button, click Run, type dcomcnfg in the Open box, and then press OK to open Component Services.

  2. Expand Component Services, Computers and right-click My Computer, and then select Properties.

  3. Click the MSDTC tab, and then click Security Configuration.

  4. Select the Enable XA Transactions check box, and then click OK. This will cause a MS DTC service restart.

  5. Click OK again to close the Properties dialog box, and then close Component Services.

  6. Stop and then restart SQL Server to ensure that it syncs up with the MS DTC changes.

On Windows Vista:

  1. Click the Start button, type dcomcnfg in the Start Search box, and then press ENTER to open Component Services. You can also type %windir%\system32\comexp.msc in the Start Search box to open Component Services.

  2. Expand Component Services, Computers, My Computer, and then Distributed Transaction Coordinator.

  3. Right-click Local DTC and then select Properties.

  4. Click the Security tab on the Local DTC Properties dialog box.

  5. Select the Enable XA Transactions check box, and then click OK. This will cause a MS DTC service restart.

  6. Click OK again to close the Properties dialog box, and then close Component Services.

  7. Stop and then restart SQL Server to make sure that it syncs up with the MS DTC changes.

Configuring the JDBC Distributed Transaction Components

You can configure the JDBC driver distributed transaction components by following these steps:

  1. Copy the sqljdbc_xa.dll from the JDBC installation directory to the Binn directory of every SQL Server computer that will participate in distributed transactions.

    Note

    If you are using XA transactions with a 32-bit SQL Server, use the sqljdbc_xa.dll file in the x86 folder, even if the SQL Server is installed on a x64 processor. If you are using XA transactions with a 64-bit SQL Server on the x64 processor, use the sqljdbc_xa.dll file in the x64 folder. If you are using XA transactions with a 64-bit SQL Server on an Itanium processor, use the sqljdbc_xa.dll file in the IA64 folder.

  2. Execute the database script xa_install.sql on every SQL Server instance that will participate in distributed transactions. This script installs the extended stored procedures that are called by sqljdbc_xa.dll. These extended stored procedures implement distributed transaction and XA support for the Microsoft SQL Server JDBC Driver. You will need to run this script as an administrator of the SQL Server instance.

  3. To grant permissions to a specific user to participate in distributed transactions with the JDBC driver, add the user to the SqlJDBCXAUser role.

You can configure only one version of the sqljdbc_xa.dll assembly on each SQL Server instance at a time. Applications may need to use different versions of the JDBC driver to connect to the same SQL Server instance by using the XA connection. In that case, sqljdbc_xa.dll, which comes with the newest JDBC driver, must be installed on the SQL Server instance.

There are three ways to verify the version of sqljdbc_xa.dll is currently installed on the SQL Server instance:

  1. Open the LOG directory of SQL Server computer that will participate in distributed transactions. Select and open the SQL Server "ERRORLOG" file. Search for "Using 'SQLJDBC_XA.dll' version ..." phrase in the "ERRORLOG" file.

  2. Open the Binn directory of SQL Server computer that will participate in distributed transactions.Select sqljdbc_xa.dll assembly.

    1. On Windows Vista: Right-click sqljdbc_xa.dll and then select Properties. Then click the Details tab. The File Version field shows the version of sqljdbc_xa.dll that is currently installed on the SQL Server instance.

    2. On Windows XP and Windows 2003 Server: Right-click sqljdbc_xa.dll and then select Properties. Next, click the Version tab. The File Version field shows the version of sqljdbc_xa.dll that is currently installed on the SQL Server instance.

  3. Set the logging functionality as shown in the code example in the next section. Search for "Server XA DLL version:..." phrase in the output log file.

Configuring the User-Defined Roles

To grant permissions to a specific user to participate in distributed transactions with the JDBC driver, add the user to the SqlJDBCXAUser role. For example, use the following Transact-SQL code to add a user named 'shelby' (SQL standard login user named 'shelby') to the SqlJDBCXAUser role:

USE master
GO
EXEC sp_grantdbaccess 'shelby', 'shelby'
GO
EXEC sp_addrolemember [SqlJDBCXAUser], 'shelby'

SQL user-defined roles are defined per database. To create your own role for security purposes, you will have to define the role in each database, and add users in a per database manner. The SqlJDBCXAUser role is strictly defined in the master database because it is used to grant access to the SQL JDBC extended stored procedures that reside in master. You will have to first grant individual users access to master, and then grant them access to the SqlJDBCXAUser role while you are logged into the master database.

Example

import java.net.Inet4Address;
import java.sql.*;
import java.util.Random;
import javax.transaction.xa.*;
import javax.sql.*;
import com.microsoft.sqlserver.jdbc.*;

public class testXA {

   public static void main(String[] args) throws Exception {

      // Create variables for the connection string.
      String prefix = "jdbc:sqlserver://";
      String serverName = "localhost";
      int portNumber = 1433;
      String databaseName = "AdventureWorks"; 
      String user = "UserName"; 
      String password = "*****";
      String connectionUrl = prefix + serverName + ":" + portNumber
         + ";databaseName=" + databaseName + ";user=" + user + ";password=" + password;

      try {
         // Establish the connection.
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         Connection con = DriverManager.getConnection(connectionUrl);

         // Create a test table.
         Statement stmt = con.createStatement();
         try {
            stmt.executeUpdate("DROP TABLE XAMin"); 
         }
         catch (Exception e) {
         }
         stmt.executeUpdate("CREATE TABLE XAMin (f1 int, f2 varchar(max))");
         stmt.close();
         con.close();

         // Create the XA data source and XA ready connection.
         SQLServerXADataSource ds = new SQLServerXADataSource();
         ds.setUser(user);
         ds.setPassword(password);
         ds.setServerName(serverName);
         ds.setPortNumber(portNumber);
         ds.setDatabaseName(databaseName);
         XAConnection xaCon = ds.getXAConnection();
         con = xaCon.getConnection();

         // Get a unique Xid object for testing.
         XAResource xaRes = null;
         Xid xid = null;
         xid = XidImpl.getUniqueXid(1);

         // Get the XAResource object and set the timeout value.
         xaRes = xaCon.getXAResource();
         xaRes.setTransactionTimeout(0);

         // Perform the XA transaction.
         System.out.println("Write -> xid = " + xid.toString());
         xaRes.start(xid,XAResource.TMNOFLAGS);
         PreparedStatement pstmt = 
         con.prepareStatement("INSERT INTO XAMin (f1,f2) VALUES (?, ?)");
         pstmt.setInt(1,1);
         pstmt.setString(2,xid.toString());
         pstmt.executeUpdate();

         // Commit the transaction.
         xaRes.end(xid,XAResource.TMSUCCESS);
         xaRes.commit(xid,true);

         // Cleanup.
         con.close();
         xaCon.close();

         // Open a new connection and read back the record to verify that it worked.
         con = DriverManager.getConnection(connectionUrl);
         ResultSet rs = con.createStatement().executeQuery("SELECT * FROM XAMin");
         rs.next();
         System.out.println("Read -> xid = " + rs.getString(2));
         rs.close();
         con.close();
      } 

      // Handle any errors that may have occurred.
      catch (Exception e) {
         e.printStackTrace();
      }
   }
}


class XidImpl implements Xid {

   public int formatId;
   public byte[] gtrid;
   public byte[] bqual;
   public byte[] getGlobalTransactionId() {return gtrid;}
   public byte[] getBranchQualifier() {return bqual;}
   public int getFormatId() {return formatId;}

   XidImpl(int formatId, byte[] gtrid, byte[] bqual) {
      this.formatId = formatId;
      this.gtrid = gtrid;
      this.bqual = bqual;
   }

   public String toString() {
      int hexVal;
      StringBuffer sb = new StringBuffer(512);
      sb.append("formatId=" + formatId);
      sb.append(" gtrid(" + gtrid.length + ")={0x");
      for (int i=0; i<gtrid.length; i++) {
         hexVal = gtrid[i]&0xFF;
         if ( hexVal < 0x10 )
            sb.append("0" + Integer.toHexString(gtrid[i]&0xFF));
         else
            sb.append(Integer.toHexString(gtrid[i]&0xFF));
         }
         sb.append("} bqual(" + bqual.length + ")={0x");
         for (int i=0; i<bqual.length; i++) {
            hexVal = bqual[i]&0xFF;
            if ( hexVal < 0x10 )
               sb.append("0" + Integer.toHexString(bqual[i]&0xFF));
            else
               sb.append(Integer.toHexString(bqual[i]&0xFF));
         }
         sb.append("}");
         return sb.toString();
      }

      // Returns a globally unique transaction id.
      static byte [] localIP = null;
      static int txnUniqueID = 0;
      static Xid getUniqueXid(int tid) {

      Random rnd = new Random(System.currentTimeMillis());
      txnUniqueID++;
      int txnUID = txnUniqueID;
      int tidID = tid;
      int randID = rnd.nextInt();
      byte[] gtrid = new byte[64];
      byte[] bqual = new byte[64];
      if ( null == localIP) {
         try {
            localIP = Inet4Address.getLocalHost().getAddress();
         }
         catch ( Exception ex ) {
            localIP =  new byte[] { 0x01,0x02,0x03,0x04 };
         }
      }
      System.arraycopy(localIP,0,gtrid,0,4);
      System.arraycopy(localIP,0,bqual,0,4);

      // Bytes 4 -> 7 - unique transaction id.
      // Bytes 8 ->11 - thread id.
      // Bytes 12->15 - random number generated by using seed from current time in milliseconds.
      for (int i=0; i<=3; i++) {
         gtrid[i+4] = (byte)(txnUID%0x100);
         bqual[i+4] = (byte)(txnUID%0x100);
         txnUID >>= 8;
         gtrid[i+8] = (byte)(tidID%0x100);
         bqual[i+8] = (byte)(tidID%0x100);
         tidID >>= 8;
         gtrid[i+12] = (byte)(randID%0x100);
         bqual[i+12] = (byte)(randID%0x100);
         randID >>= 8;
      }
      return new XidImpl(0x1234, gtrid, bqual);
   }
}

See Also

Other Resources

Performing Transactions with the JDBC Driver