JDBC V1.1 Distributed Transactions with Read Committed and Snapshot isolation.
One of the biggest changes you may not have noticed in the v1.1 2005 JDBC driver is that we have changed the default isolation level for Distributed Transactions from Serializable to Read Committed. This is in line with other JDBC drivers like our 2000 jdbc driver.
In this blog I am going to discuss the two questions that I had when I first heard about this change.
Q1>How do I set the isolation level of distributed transactions back to Serializable for those applications that require it (boring!)
Answer> To change the isolation level you just need to set the following connection property:
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
Make sure you set this _before_ calling XAResource start!
Of course you can set any isolation level you want here, transaction_none will just give you the default isolation level . To specify snapshot isolation level (_after_ you have set up the database to use snapshot with ALTER DATABASE <database name> SET ALLOW_SNAPSHOT_ISOLATION ON) you can do the following:
import com.microsoft.sqlserver.jdbc.*;
/…/
conn.setTransactionIsolation(SQLServerConnection.TRANSACTION_SNAPSHOT);
If you don’t want to import the sqlserver.jdbc namespace you can use the following:
conn.setTransactionIsolation(4096);
Q2>how do I set the default isolation level of Distributed Transactions to Snapshot (woo!)
Answer> This is what you are most likely going to want if you are looking to minimize lock contention.
The first thing you need to do is to set the _default_ isolation level of the database to snapshot like this:
ALTER DATABASE <databasename> SET READ_COMMITTED_SNAPSHOT ON
Once this is done all Read Committed (the default isolation level for v1.1 2005 JDBC driver!) transactions will behave as snapshot. You are done.
Here is some code for you to play with, I realize that it may look a little intimidating since we have to roll our own XIDs, but you can pretty much just ignore anything outside of the DoDTC method.
import java.net.Inet4Address;
import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;
import javax.sql.XAConnection;
import javax.transaction.xa.XAResource;
import javax.transaction.xa.Xid;
import com.microsoft.sqlserver.jdbc.SQLServerXADataSource;
import java.util.*;
public class transaction2 {
public static void main(String[] args) {
try{
transaction2 t = new transaction2();
t.DoDTC(1);
System.out.println("done");
}catch(Exception e){
e.printStackTrace();
}
}
private void DoDTC(int id) throws Exception
{
SQLServerXADataSource ds = new SQLServerXADataSource();
ds.setURL("jdbc:sqlserver://servername;user=username;password=password");
XAConnection xaConn = ds.getXAConnection();
Connection conn = xaConn.getConnection();
System.out.println("connected");
//This is a good place to change the default isolation level of the transaction. Ex:
//conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
XAResource xaRes = null;
Xid xid = null;
try{
xaRes = xaConn.getXAResource();
xid = getUniqueXID();
System.out.println("[a] xid=" + xid.toString());
xaRes.start(xid,XAResource.TMNOFLAGS);
ResultSet rs = conn.createStatement().executeQuery("DBCC USEROPTIONS");
while (rs.next()) {
if (rs.getString(1).equals("isolation level")) {
System.out.println("isolation level " + rs.getString(2));
}
}
xaRes.end(xid,XAResource.TMSUCCESS);
xaRes.commit(xid,true);
//xaRes.rollback(xid);
}catch(Exception ex){
//Important. On certain exceptions we may leak transactions!
//Make sure to reclaim them.
xaRes.forget(xid);
throw ex;
}
}
// Returns a globally unique transaction id
byte [] localIP = null;
private int txnUniqueID = 0;
private int tid=1;
private Xid getUniqueXID()
{
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 (unique to our class instance).
// Bytes 8 ->11 - thread id (unique to our thread).
// Bytes 12->15 - random number generated 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);
}
}
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();
}
}