JDBC Snapshot isolation level

I have gotten some questions about using Sql Server 2005 with the 2005 JDBC driver. There are unfortunately a lot of misconceptions in this area. Here are some notes to make this a little bit easier to understand.

The first thing you need to understand about Snapshot isolation level is that it only works in a Sql Server 2005 database that has been setup for Snapshot isolation level. The second thing that you need to understand is that you can only see a snapshot of the data locked by other transactions when running from inside a transaction with snapshot isolation level. This is different from Oracle where you can always get a snapshot of the data currently locked.

The basics:

Setting up a Sql Server 2005 database for Snapshot goes something like this:

ALTER DATABASE <database name> SET ALLOW_SNAPSHOT_ISOLATION ON

Another option is to modify the default isolation level to behave like Snapshot isolation. The ALTER DATABASE statement has been upgraded to allow you to get snapshots benefits for free:

ALTER DATABASE <database name> SET READ_COMMITTED_SNAPSHOT ON

Once you have set up the database you can test it with the following code, note the following line:

conn2.setTransactionIsolation(4096); //4096 corresponds to SQLServerConnection.TRANSACTION_SNAPSHOT

This is where we set the JDBC isolation level to Snapshot, remove this line and the code below will deadlock!

create table foo (myid int primary key)

insert into foo values (1)

insert into foo values (2)

insert into foo values (3)

insert into foo values (4)

import java.sql.*;

public class test

{

      public static void main(String args[])

      throws Exception

      {

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            Connection conn = DriverManager.getConnection("jdbc:sqlserver://yourserver;integratedsecurity=true;");

            DatabaseMetaData m = conn.getMetaData();

            System.out.println("getDriverVersion "+m.getDriverVersion());

            //<Lock the table foo>

            conn.setAutoCommit(false);

            Statement stmt = conn.createStatement();

            stmt.executeQuery("select * from foo");

            stmt.execute("update foo set myid=100 where myid =1");

            //</Lock the table foo>

            //Now use snapshot isolation level to see the data.

            //Note: if you change the isolation level here to anything else the application will lock

            //unless you have set up the database to treat Read Committed as Snapshot.

            Connection conn2 = DriverManager.getConnection("jdbc:sqlserver:// yourserver;integratedsecurity=true;");

            conn2.setTransactionIsolation(4096); //4096 corresponds to SQLServerConnection.TRANSACTION_SNAPSHOT

            conn2.setAutoCommit(false);

            Statement stmt2 = conn2.createStatement();

            ResultSet rs2 = stmt2.executeQuery("select * from foo");

            while (rs2.next()){System.out.println("foo value= "+rs2.getString(1));}

            conn.rollback();

            conn.close();

            conn2.close();

            System.out.println("Done");

      }

}