Is there a way to determine if JDBC transaction is in effect?

I am calling several SQLServer 2008 stored procedures through JDBC.

I would like all these proc calls to be a part of the same transaction.

On java side I have the equivalent of the following:

con.setAutoCommit( false );
boolean hasFailed = true;

try
{
  ... call PROC_1

  ... call PROC_2

  con.commit( );
  hasFailed = false;
}
finally
{
  if ( hasFailed )
  {
    con.rollback( );
  }
  con.setAutoCommit( true );
}

In PROC_1 I have a transaction guard that only starts a new transaction if there is no transaction in effect.

The guard is implemented by checking the value of @@TRANCOUNT

declare @owns_transaction int = 0

begin try

  if @@TRANCOUNT = 0
  begin
    begin transaction
    set @owns_transaction = 1
  end

  ... do work

  if @owns_transaction = 1
  begin
    commit transaction
    set @owns_transaction = 0
  end
end try
begin catch
  if @owns_transaction = 1
  begin
    rollback transaction
    set @owns_transaction = 0
  end
  ... handle error
end catch

However, when I enter PROC_1 the @@TRANCOUNT is still 0, so it starts a new transaction and I think it may have some dire consequences later on.

I've tried to use XACT_STATE(), but it also returns a result of 0, which means 'There is no active user transaction for the current request.', and according to this I have to start a manual transaction as well.

Am I doing something wrong?

BTW, I think SQLServer does know that it is in transaction, because if I add a name to transaction statement it balks in rollback that it coul开发者_如何学Pythond not rollback transaction with that name. This gives me a clue that the TOP-LEVEL transaction is not started in PROC_1, but is indeed starts in JDBC.

Found a way.

Turns out when you calling

con.setAutoCommit(false)

is equivalent to setting IMPLICIT_TRANSACTIONS option on the connection, like this

SET IMPLICIT_TRANSACTIONS ON;

Then you can check implicit transaction status with the help of @@OPTIONS function:

if ((@@OPTIONS & 2) != 2) and (@@TRANCOUNT = 0)
begin
    select 'No transaction'
end
else begin
    select 'Already in transaction'
end