I see different behaviors when I call a stored procedure (MSSQL 2008R2) directly from SSMS or when I call it from JTDS.
First, please see these two procedures.
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
The second is very similar to the first, except it will not begin (nor commit) a transaction except if the @@TRANCOUNT on entry is 0.
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
If I call them from SSMS, with the code below
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
The output is what I'd expect.
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
That is wrapping the call to the procedure within a transaction causes the StartTranCount to be 1, otherwise, it is zero.
However, when I execute the same procedures via JTDS/JDBC, as per the code below, I see strange behavior.
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();
I'm seeing the following output:
- Guarded StartTC: 0, TC: 2
- Unguarded StartTC: 0, TC: 2
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 setAutoCommit(false), I'm really at a loss as to what is going on. Any insight?
Additional info:
If I switch to the Microsoft JDBC driver, I get the results I expect
MSFT Driver - Guarded StartTC: 1, TC: 1
MSFT Driver - Unguarded StartTC: 1, TC: 2
I've discovered the cause of this behavior.
I assumed that jTDS, after setAutoCommit(false) was called, was explicitly beginning a transaction for the connection. In fact, it doesn't behave that way. What it does do is issue a SET IMPLICIT_TRANSACTIONS ON on the connection.
According to Microsoft (http://msdn.microsoft.com/en-us/library/ms187807.aspx) - "When IMPLICIT_TRANSACTIONS = ON an explicit BEGIN TRANSACTION will start two nested transactions."
For example, if we execute the following in SSMS
SET IMPLICIT_TRANSACTIONS ON
EXECUTE [Template].[UnguardedTest] @outparam_StartTransactionCount OUTPUT, @outparam_TransactionCount OUTPUT
SELECT 'UNGUARDED_IMPLICIT' AS Description, @outparam_StartTransactionCount AS [StartTranCount], @outparam_TransactionCount AS [TranCount]
COMMIT TRAN
SET IMPLICIT_TRANSACTIONS ON
EXECUTE [Template].[GuardedTest] @outparam_StartTransactionCount OUTPUT, @outparam_TransactionCount OUTPUT
SELECT 'GUARDED_IMPLICIT' AS Description, @outparam_StartTransactionCount AS [StartTranCount], @outparam_TransactionCount AS [TranCount]
COMMIT TRAN
We get the following:
Description StartTranCount TranCount
------------------ -------------- -----------
UNGUARDED_IMPLICIT 0 2
Description StartTranCount TranCount
---------------- -------------- -----------
GUARDED_IMPLICIT 0 2
which agrees with the output we get when jTDS executes those procedures.