Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count

Question

I am getting this exception about commits and rollbacks but am not sure what exactly is wrong with my Stored Procedure. I have read the answers in other such questions and am unable to find where exactly the commit count is getting messed up.

So, this is the Stored Procedure I use:

-- this is a procedure used for the purge utility. This procedure uses the parameters of a date and lets user select
-- if the leads that should be purge must be closed either before, on or since that date.
-- operator: 0-->less 1-->equal 2-->greater
-- @closed: closing date
-- leadscount: returns the count of leads deleted

IF OBJECT_ID ('LEAD_PURGE', 'P') IS NOT NULL
   DROP PROCEDURE LEAD_PURGE
go

CREATE PROCEDURE LEAD_PURGE
@purgextns INT,
@leadscount INT OUTPUT
AS
BEGIN
BEGIN TRANSACTION
CREATE TABLE #ASSIGNMENTS_DELETED
(
    ID NUMERIC(19, 0)
    PRIMARY KEY (ID)
)

CREATE TABLE #MAPRESULTS_DELETED
(
    ID NUMERIC(19, 0)
    PRIMARY KEY (ID)
)

CREATE TABLE #COMMAND_DELETED
(
    ID NUMERIC(19, 0)
    PRIMARY KEY (ID)
)

CREATE TABLE #PROGRESS_STATUS_DELETED
(
    ID NUMERIC(19, 0)
    PRIMARY KEY (ID)
)

CREATE TABLE #DETAILS_DELETED
(
    ID NUMERIC(19, 0)
    PRIMARY KEY (ID)
)

CREATE TABLE #NEEDS_DELETED
(
    ID NUMERIC(19, 0)
    PRIMARY KEY (ID)
)

insert into #ASSIGNMENTS_DELETED
select SEQID FROM ASSIGNMENT WHERE LEADSEQ IN (SELECT ID FROM PURGE_LEAD);

SELECT @leadscount = (SELECT COUNT(*) FROM PURGE_LEAD);

INSERT INTO #MAPRESULTS_DELETED
SELECT ID FROM MAPRESULT WHERE ASSIGNMENTSEQ IN (SELECT ID FROM #ASSIGNMENTS_DELETED)

INSERT INTO #COMMAND_DELETED
SELECT ID FROM EXECUTERULECOMMAND WHERE MAPRESULTID IN (SELECT ID FROM #MAPRESULTS_DELETED)

INSERT INTO #PROGRESS_STATUS_DELETED
SELECT PROGRESS_STATUS_ID FROM COMMAND WHERE ID IN (SELECT ID FROM #COMMAND_DELETED)

INSERT INTO #DETAILS_DELETED
SELECT DETAILID FROM LEAD WHERE SEQID IN (SELECT ID FROM PURGE_LEAD)

INSERT INTO #NEEDS_DELETED
SELECT NEEDSID FROM LEAD WHERE SEQID IN (SELECT ID FROM PURGE_LEAD)



DELETE FROM PROGRESS_STATUS WHERE ID IN (SELECT ID FROM #PROGRESS_STATUS_DELETED)

DELETE FROM EXECUTERULECOMMAND WHERE ID IN (SELECT ID FROM #COMMAND_DELETED)

DELETE FROM COMMAND WHERE ID IN (SELECT ID FROM #COMMAND_DELETED)

DELETE FROM SIMPLECONDITIONAL WHERE RESULT IN (SELECT ID FROM #MAPRESULTS_DELETED)

DELETE FROM MAPPREDICATE WHERE ROWBP IN (SELECT ID FROM MAPROW WHERE RESULT IN (SELECT ID FROM #MAPRESULTS_DELETED))

DELETE FROM MAPROW WHERE RESULT IN (SELECT ID FROM #MAPRESULTS_DELETED)

DELETE FROM MAPRESULT WHERE ID IN (SELECT ID FROM #MAPRESULTS_DELETED)

DELETE FROM ASSIGNMENTATTACHMENTS WHERE ASSIGNMENTSEQ IN (SELECT ID FROM #ASSIGNMENTS_DELETED)

DELETE FROM LEADOBSERVER WHERE ASSIGNSEQ IN (SELECT ID FROM #ASSIGNMENTS_DELETED)

DELETE FROM MAPDESTINATIONS WHERE SUGGESTEDASSIGNID IN 
    (SELECT ID FROM SUGGESTEDASSIGNMENT WHERE ASSIGNMENT_SEQID IN (SELECT ID FROM #ASSIGNMENTS_DELETED))

DELETE FROM SUGGESTEDASSIGNMENT WHERE ASSIGNMENT_SEQID IN (SELECT ID FROM #ASSIGNMENTS_DELETED)

DELETE FROM PRODUCTINTEREST WHERE LEADSEQ IN (SELECT ID FROM PURGE_LEAD)



CREATE TABLE #SALE_DELETED_EX
(
    ID NUMERIC(19, 0)
    PRIMARY KEY (ID)
)
INSERT into #SALE_DELETED_EX SELECT SALEEXSEQ FROM SALE WHERE SEQID IN (SELECT SALEID FROM LEADSALES WHERE LEADID IN (SELECT ID FROM PURGE_LEAD))


DELETE FROM SALE WHERE SEQID IN (SELECT SALEID FROM LEADSALES WHERE LEADID IN (SELECT ID FROM PURGE_LEAD))


DELETE FROM SALEEXTENSIONS WHERE 
    SEQID IN (SELECT ID FROM #SALE_DELETED_EX)

DELETE FROM LEADSALES WHERE LEADID IN (SELECT ID FROM PURGE_LEAD)


DELETE FROM NOTES WHERE OBJECTID IN (SELECT ID FROM #NEEDS_DELETED) OR OBJECTID IN (SELECT ID FROM #DETAILS_DELETED)

DELETE FROM HISTORYRECORD WHERE OBJECTID IN (SELECT ID FROM #DETAILS_DELETED)

DELETE FROM DETAIL WHERE SEQID IN (SELECT ID FROM #NEEDS_DELETED UNION SELECT ID FROM #DETAILS_DELETED)

DELETE FROM MESSAGES WHERE PROVIDERID IN (SELECT ID FROM PURGE_LEAD)

DELETE FROM ASSIGNMENT WHERE LEADSEQ IN (SELECT ID FROM PURGE_LEAD)

DELETE FROM LEAD WHERE SEQID IN (SELECT ID FROM PURGE_LEAD)

CREATE TABLE #PURGE_LEAD_E
(
    ID NUMERIC(19, 0)
    PRIMARY KEY (ID)
)

INSERT into #PURGE_LEAD_E Select SEQID FROM LEADEXTENSIONS WHERE 
    SEQID NOT IN (SELECT LEADEXSEQ FROM LEAD)

if @purgextns = 1 begin
    DELETE FROM LEADEXTENSIONS WHERE 
        SEQID  IN (SELECT ID FROM PURGE_LEAD_E)
end


DELETE FROM PURGE_LEAD;

DROP TABLE #ASSIGNMENTS_DELETED

DROP TABLE #MAPRESULTS_DELETED

DROP TABLE #COMMAND_DELETED

DROP TABLE #PROGRESS_STATUS_DELETED

DROP TABLE #DETAILS_DELETED

DROP TABLE #NEEDS_DELETED

DROP TABLE #PURGE_LEAD_E

DROP TABLE #SALE_DELETED_EX

COMMIT
END
go

now I call this procedure in the following code:

    try {
        c = new ConnectionHelper().getConnection();
        String sql = "";
        if (shouldPurgeExtns) {
            progressModel.makeProgress("progress.deleting.dependents");
            purgeMultiselect(c, LEAD, isMSSQL);
        }
        sql = "{CALL " + TOPLinkManager.getSchemaPrefix()
                + "LEAD_PURGE (?,?)}";
        cs = c.prepareCall(sql);
        cs.setInt(1, shouldPurgeExtns ? 0 : 1);
        cs.registerOutParameter(2, java.sql.Types.INTEGER);
        cs.executeUpdate();
        int rowcount = cs.getInt(2);
        cs.close();
        progressModel.makeProgress("progress.recording.history");
        recordHistory(c, isMSSQL, LEAD, DateTypeDecorator.CLOSED, date,
                rowcount);
        done(progressModel);
        c.close();
        return true;
    } catch (Exception e) {
        Logs.main.error("Error Purging Leads", e);
        throw new Exception(e.getMessage());
    }

And I get an exception on the line which say int rowcount = cs.getInt(2);

The Exception is:

com.microsoft.sqlserver.jdbc.SQLServerException: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.processResults(SQLServerStatement.java:1083)
    at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getOutParameter(SQLServerCallableStatement.java:112)
    at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getterGetParam(SQLServerCallableStatement.java:387)

Please help me out. at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getValue(SQLServerCallableStatement.java:393) at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getInt(SQLServerCallableStatement.java:437) at marketsoft.tools.purge.PurgeUtils.PurgeLeads(PurgeUtils.java:283)

EDIT:

as I have answered this question myself... I would like to change the question a bit now.

Why was no exception thrown in the execute method???

Solution 1

Sorry Guys! Thanks for all your efforts, In the end, it was a very small mistake on my part in the Stored Procedure:

Look at line:

if @purgextns = 1 begin
    DELETE FROM LEADEXTENSIONS WHERE 
        SEQID  IN (SELECT ID FROM PURGE_LEAD_E)
end

It should be #PURGE_LEAD_E

All your answers helped me get a different perspective of store procedure development. Thanks a lot!

Solution 2

Your COMMIT is not being hit, probably because of an error. The transaction won't be rolled back automatically

The best way (and best practice) is to add some SQL error handling

CREATE PROCEDURE LEAD_PURGE
  @purgextns INT,
  @leadscount INT OUTPUT
AS
SET NOCOUNT, XACT_ABORT ON;

BEGIN TRY
  BEGIN TRANSACTION

  CREATE TABLE #ASSIGNMENTS_DELETED
  (
      ID NUMERIC(19, 0)
      PRIMARY KEY (ID)
  )

  ...
  DROP TABLE #SALE_DELETED_EX

  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  IF XACT_STATE() <> 0 
      ROLLBACK TRANSACTION
  RAISERROR ('it broke', 16, 1)
END CATCH
go

For more details on what is going on here, see my answer here Nested stored procedures containing TRY CATCH ROLLBACK pattern?

Note: you don't need to drop the temp tables as they go out of scope when the stored procedure exits

Solution 3

Try to add in the beginning of procedure

SET XACT_ABORT ON

Or

Wrap your statements with

begin try 
  BEGIN TRANSACTION 
 Your TSQL code

  COMMIT
end try 
begin catch 
  ROLLBACK
  RAISERROR('Gotcha!', 16, 1)
end catch

To check how many uncommitted BEGIN TRAN is opened test the @@TRANCOUNT system variable

Solution 4

This normally happens when the transaction is started and either it is not committed or it is not rollback.

In case the error comes in your stored procedure, this can lock the database tables because transaction is not completed due to some runtime errors in the absence of exception handling You can use Exception handling like below. SET XACT_ABORT

SET XACT_ABORT ON
SET NoCount ON
Begin Try 
     BEGIN TRANSACTION 
        //Insert ,update queries    
     COMMIT
End Try 
Begin Catch 
     ROLLBACK
End Catch

Solution 5

How exactly the @leadscount variable contains the count of leads deleted?

This is the only place I see it beeing used:

SELECT @leadscount = (SELECT COUNT(*) FROM PURGE_LEAD);

Anyhow, to test it, why don't you run the above code outside the context of the transaction?

If you really need it to be inside a transaction, try loading the value into a table variable (create a table with only one column). Since they don't participate in transactions you can test if the transaction is your real problem.

View more solutions