Understanding XA Transactions
The Microsoft SQL Server 2005 JDBC Driver provides support for J2EE/JDBC 2.0 optional distributed transactions. JDBC connections obtained from the SQLServerXADataSource class can participate in standard distributed transaction processing environments such as J2EE application servers.
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.
When you use XA transactions with SQL Server through the Microsoft SQL Server 2005 JDBC driver, you may notice that performance decreases. 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 Performance and SQL Server.
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.
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.
The Microsoft SQL Server 2005 JDBC driver version 1.2 provides a SSTRANSTIGHTLYCPLD flag to allow 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:
From Control Panel, open Administrative Tools, and then open Component Services.
Expand Component Services, right-click My Computer, and then select Properties.
Click the MSDTC tab, and then click Security Configuration.
Select the Enable XA Transactions check box, and then click OK. This will cause a MS DTC service restart.
Click OK again to close the Properties dialog box, and then close Component Services.
Stop and then restart SQL Server to ensure 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:
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 IA-64 processor, use the sqljdbc_xa.dll file in the IA64 folder.
Execute the database script xa_install.sql on every SQL Server instance that will participate in distributed transactions. This script installs sqljdbc_xa.dll as an extended stored procedure. You will need to run this script as an administrator of the SQL Server instance.
To grant permissions to a specific user to participate in distributed transactions with the JDBC driver, add the user to the SqlJDBCXAUser role.
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 a variable for the connection string.
String connectionUrl = "jdbc:sqlserver://localhost:1433;"
+"databaseName=AdventureWorks;user=UserName;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("UserName");
ds.setPassword("*****");
ds.setServerName("localhost");
ds.setPortNumber(1433);
ds.setDatabaseName("AdventureWorks");
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.
pstmt.close();
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);
}
}