title: JTDS and Transactions
body: <p>I see different behaviors when I call a stored procedure (MSSQL 2008R2) directly from SSMS or when I call it from JTDS.  </p>
  
  <p>First, please see these two procedures.  </p>
  
  <pre><code>CREATE PROCEDURE [Template].[UnguardedTest]
      @outparam_StartTransactionCount INT OUTPUT,
      @outparam_TransactionCount INT OUTPUT 
  
  AS
  
  BEGIN 
  
      SET NOCOUNT ON;
      SET XACT_ABORT ON;
  
      DECLARE @StartTranCount INT
  
      SELECT @StartTranCount = @@TRANCOUNT
  
      BEGIN TRANSACTION
  
      BEGIN
          SELECT @outparam_StartTransactionCount = @StartTranCount
          SELECT @outparam_TransactionCount = @@TRANCOUNT
      END
  
      COMMIT TRANSACTION
  
  END 
  </code></pre>
  
  <p>The second is very similar to the first, except it will not begin (nor commit) a transaction except if the <code>@@TRANCOUNT</code> on entry is 0.</p>
  
  <pre><code>CREATE PROCEDURE [Template].[GuardedTest]
     @outparam_StartTransactionCount INT OUTPUT,
     @outparam_TransactionCount INT OUTPUT 
  
  AS
  
  BEGIN 
  
      SET NOCOUNT ON;
      SET XACT_ABORT ON;
  
      DECLARE @StartTranCount INT
  
      -- Record the @@TRANCOUNT at the beginning of this procedure / trigger.
      SELECT @StartTranCount = @@TRANCOUNT
  
      IF @StartTranCount = 0
          BEGIN TRANSACTION
  
      BEGIN
          SELECT @outparam_StartTransactionCount = @StartTranCount
          SELECT @outparam_TransactionCount = @@TRANCOUNT
      END
  
      IF @StartTranCount = 0
          COMMIT TRANSACTION
  
  END 
  </code></pre>
  
  <p>If I call them from SSMS, with the code below</p>
  
  <pre><code>DECLARE @outparam_TransactionCount INT
  DECLARE @outparam_StartTransactionCount INT
  
  EXECUTE [Template].[UnguardedTest] @outparam_StartTransactionCount OUTPUT, @outparam_TransactionCount OUTPUT
  SELECT 'UNGUARDED_NOT_WRAPPED' AS Description, @outparam_StartTransactionCount AS [StartTranCount],  @outparam_TransactionCount AS [TranCount]
  
  BEGIN TRAN
      EXECUTE [Template].[UnguardedTest] @outparam_StartTransactionCount OUTPUT, @outparam_TransactionCount OUTPUT
      SELECT 'UNGUARDED_WRAPPED' AS Description, @outparam_StartTransactionCount AS [StartTranCount],  @outparam_TransactionCount AS [TranCount]
  COMMIT TRAN
  
  EXECUTE [Template].[GuardedTest] @outparam_StartTransactionCount OUTPUT, @outparam_TransactionCount OUTPUT
  SELECT 'GUARDED_NOT_WRAPPED' AS Description, @outparam_StartTransactionCount AS [StartTranCount],  @outparam_TransactionCount AS [TranCount]
  
  BEGIN TRAN
      EXECUTE [Template].[GuardedTest] @outparam_StartTransactionCount OUTPUT, @outparam_TransactionCount OUTPUT
      SELECT 'GUARDED_WRAPPED' AS Description, @outparam_StartTransactionCount AS [StartTranCount],  @outparam_TransactionCount AS [TranCount]
  COMMIT TRAN
  </code></pre>
  
  <p>The output is what I'd expect.  </p>
  
  <pre><code>Description           StartTranCount TranCount
  --------------------- -------------- -----------
  UNGUARDED_NOT_WRAPPED 0              1
  
  Description       StartTranCount TranCount
  ----------------- -------------- -----------
  UNGUARDED_WRAPPED 1              2
  
  Description         StartTranCount TranCount
  ------------------- -------------- -----------
  GUARDED_NOT_WRAPPED 0              1
  
  Description     StartTranCount TranCount
  --------------- -------------- -----------
  GUARDED_WRAPPED 1              1
  </code></pre>
  
  <p>That is wrapping the call to the procedure within a transaction causes the StartTranCount to be 1, otherwise, it is zero.</p>
  
  <p>However, when I execute the same procedures via JTDS/JDBC, as per the code below, I see strange behavior.</p>
  
  <pre><code>    int tc = -1, startTC = -1;
  
      final Connection con2 = DriverManager.getConnection(url);
      con2.setAutoCommit(false);
      final CallableStatement proc2 = con2.prepareCall("{ call Template.GuardedTest(?,?) }");
      proc2.registerOutParameter("@outparam_StartTransactionCount", Types.INTEGER);
      proc2.registerOutParameter("@outparam_TransactionCount", Types.INTEGER);
      proc2.execute();
      startTC = proc2.getInt("@outparam_StartTransactionCount");
      tc = proc2.getInt("@outparam_TransactionCount");
      log.info("Guarded StartTC: " + startTC + ", TC: " + tc);
      proc2.close();          
      con2.commit();
      con2.close();
  
      final Connection con1 = DriverManager.getConnection(url);
      con1.setAutoCommit(false);
      final CallableStatement proc1 = con1.prepareCall("{ call Template.UnguardedTest(?,?) }");
      proc1.registerOutParameter("@outparam_StartTransactionCount", Types.INTEGER);
      proc1.registerOutParameter("@outparam_TransactionCount", Types.INTEGER);
      proc1.execute();
      startTC = proc1.getInt("@outparam_StartTransactionCount");
      tc = proc1.getInt("@outparam_TransactionCount");
      log.info("Unguarded StartTC: " + startTC + ", TC: " + tc);
      proc1.close();
      con1.commit();
      con1.close();
  </code></pre>
  
  <p>I'm seeing the following output:</p>
  
  <pre><code>- Guarded StartTC: 0, TC: 2
  - Unguarded StartTC: 0, TC: 2
  </code></pre>
  
  <p>As I was expecting to see the same values as the 'wrapped' example above (as I understand that JDBC begins a new transaction when calling <code>setAutoCommit(false)</code>, I'm really at a loss as to what is going on.   Any insight?</p>
  
  <p>Additional info:</p>
  
  <p>If I switch to the Microsoft JDBC driver, I get the results I expect</p>
  
  <pre><code>MSFT Driver - Guarded StartTC: 1, TC: 1
  MSFT Driver - Unguarded StartTC: 1, TC: 2
  </code></pre>