JTDS and transactions
I see different types of behavior when I call a stored procedure (MSSQL 2008R2) directly from SSMS or when I call it from JTDS.
First, familiarize yourself with 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 that it will not start (or commit) a transaction, unless the record @@TRANCOUNT
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 below 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
The result is what I 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
It is the completion of a procedure call in a transaction that causes StartTranCount to be 1, otherwise it is zero.
However, when I do the same procedures via JTDS / JDBC, according to 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 see the following output:
- Guarded StartTC: 0, TC: 2
- Unguarded StartTC: 0, TC: 2
As I expected to see the same values โโas the above example (as I understand it, JDBC starts a new transaction when called setAutoCommit(false)
, I am really at a loss as to what is going on. Understanding?
Additional Information:
If I switch to Microsoft JDBC driver I get expected results
MSFT Driver - Guarded StartTC: 1, TC: 1
MSFT Driver - Unguarded StartTC: 1, TC: 2
source to share
I discovered the reason for this behavior.
I assumed that jTDS, after the call, setAutoCommit(false)
explicitly started a transaction for the connection. In fact, it doesn't behave like that. What it does is SET IMPLICIT_TRANSACTIONS ON
a connection problem .
According to Microsoft ( http://msdn.microsoft.com/en-us/library/ms187807.aspx ) - "When IMPLICIT_TRANSACTIONS = ON, the 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 is consistent with the output we get when jTDS does these routines.
source to share