Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 773
  • Last Modified:

Transaction Logging

The following stored procedure gives a messaging at the very end saying "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION".  I have already tried issuing the SET IMPLICIT_TRANSACTION ON statement.  This allows it to work but my understanding is that each SQL statement is then committed.  Therefore if a subsequent insert or update fails, the previous ones will not be rolled back.  How can I over come this?


CREATE PROCEDURE procAddCustomerRequest
 @RecNum Integer OutPut,
 @pv_RequestType varchar(8),
 @pv_RequestDescription varchar(8000),
 @pv_AccountNumber integer,
 @pv_OccupantCode smallint,
 @pv_ContactTeleNo varchar(30),
 @pv_CustomerNumber integer,
 @pv_IDNumber varchar(30),
 @pv_IDType varchar(8),
 @pv_RequestCharge money,
 @pv_DirectionsToPremises varchar(8000),
 @pv_StreetNo varchar(10),
 @pv_StreetName varchar(50),
 @pv_AreaName varchar(50),
 @pv_CityName varchar(50)
 
 
AS

        Set Nocount on;

        SET IMPLICIT_TRANSACTIONS ON

        --Explicitly start the transaction log.      
        BEGIN TRAN

        --First, insert into the customer request table which will store master customer request information
      INSERT INTO OnlineCustomerRequest
        (RequestType, RequestDate, RequestStatus, RequestDescription, AccountNumber, OccupantCode, ContactTeleNo,
         CustomerNumber, IDNumber, IDType, RequestCharge)
        VALUES
        (@pv_RequestType, GetDate(), 'REQ', @pv_RequestDescription, @pv_AccountNumber,@pv_OccupantCode, @pv_ContactTeleNo,
         @pv_CustomerNumber, @pv_IDNumber, @pv_IDType, @pv_RequestCharge)
         
         IF (@@Error <> 0)
         BEGIN
           ROLLBACK TRAN  --Roll Back the transaction if it fails  
           Print 'A role back was encountered'
           RETURN (-1) -- Exit the stored procedure.  The calling app should know that a -1 means error
         END
         ELSE
         BEGIN            
             Select @RecNum = @@identity  --Obtain the serial value inserted
         END


         --Select count form the DirectionsToPremises table to determine If I should update the
         --Table of if I should insert

         Declare @MyVar as Int
       
         SELECT @MyVar = Count(*) FROM DirectionsToPremises
         WHERE DirectionsToPremises.AccountNumber = @pv_AccountNumber
         
         IF (@MyVar>0)
         BEGIN
         
            --A record in the table exists so we need to update
 
            UPDATE DirectionsToPremises
            Set DirectionsToPremises = @pv_DirectionsToPremises
            WHERE DirectionsToPremises.AccountNumber = @pv_AccountNumber

            IF (@@Error <> 0)
            BEGIN
               ROLLBACK TRAN --Roll Back the transaction.  This means that even the insert into the OnlineCustomerRequest table will be rolled back         
               Print 'A role back was encountered'
               RETURN (-1) -- Exit the stored procedure.  The calling app should know that a -1 means error
            END

         END

         ELSE  -- MyVar variable < 1

         BEGIN

           -- No record exists so one will be inserted

           INSERT INTO DirectionsToPremises (AccountNumber, DirectionsToPremises)
           VALUES (@pv_AccountNumber, @pv_DirectionsToPremises)

           IF (@@Error <> 0)
           BEGIN
               ROLLBACK TRAN --Roll Back the transaction.  This means that even the insert into the OnlineCustomerRequest table will be rolled back         
           Print 'A role back was encountered'
               RETURN (-1) -- Exit the stored procedure.  The calling app should know that a -1 means error
           END
   
         END
       
         -- Now determine the Request Type so that I can insert into the proper specific table

         IF (@pv_RequestType = "NEWSVR")
         BEGIN

            -- Insert into the RequestNewService Table
            INSERT INTO RequestNewServiceInstallation (RequestNumber, StreetNo, StreetName, AreaName, CityName)
            VALUES
            (@RecNum, @pv_StreetNo, @pv_StreetName, @pv_AreaName, @pv_CityName)

             IF (@@Error <> 0)
             BEGIN
               ROLLBACK TRAN --Roll Back the transaction.  This means that even the insert into the OnlineCustomerRequest table will be rolled back         
           Print 'A role back was encountered'
               RETURN (-1) -- Exit the stored procedure.  The calling app should know that a -1 means error
             END
   
            -- If No errors then commit the entire transaction to the database and return the request number

        END

        COMMIT TRAN

        RETURN (@RecNum)                        
         
0
stino
Asked:
stino
  • 6
  • 3
  • 2
  • +1
1 Solution
 
mathavraCommented:
I think the SET IMPLICIT_TRANSACTIONS ON will make every statement to automatically issues begin tran. So, if you have this on, you do not need the explicit BEGIN TRANSACTION command. Remove the explicit BEGIN TRAN and see whether it works.
0
 
stinoAuthor Commented:
But then won't each transaction be committed or will it wait until it sees the commit tran statement?
0
 
mathavraCommented:
Yes. If you want to control the transactions yourself, then why don't you turn off the implicit_transactions and issue your own begin and commits.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mathavraCommented:
Just change the set implicit transactions to

SET IMPLICIT_TRANSACTIONS OFF

and run your procedure. This will make sure that the procedure will run fine even if the procedure is called from a connection which has it turned on.
0
 
stinoAuthor Commented:
which brings me back to the original problem.  if you notice, the stored procedure does NOT include the set implicit_transactions statement.  When executing the procedure WITHOUT that statement it gives an error saying that it cannot find a corresponding begin trans statement even though one is clearly there.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
This is only a shot in the dark:

Does any of the tables you INSERT data have triggers?
If yes, and those triggers have rollback statements, this could explain this error message...

CHeers
0
 
stinoAuthor Commented:
angellll, that's exactly the problem! now, how can I bypass this?  I still want the trigger because it does some data integrity checks.  Will the raise error statement help?  Just give me a simple solution then I'll do the research myself.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
In fact, you might use the @@trancount variable to see if a trigger rolled back the transaction: if @@trancount = 0, then a trigger has rolled back the transaction:

Like this:
IF (@@Error <> 0) OR (@@trancount = 0)
           
CHeers
0
 
mironCommented:
let me go back to the original assumption:
"...
but my understanding is that each SQL statement is then committed.
..."

this is incorrect. The only time when something is committed with

"set implicit transaction on"

is when a roolback tran or commit tran
are issued.

the implisit transaction starts "implicitly" on a first update statements and gets finished with first rollback  or commit down the road. This pceudo code illutrates logic
with implicit transaction on

1) select * from A
2) select * from B
3) select @a = name from C
4) if exists( select 1 from D where @a = z )
5)  insert into B select * from A
--- on the previous statement transaction is began.
6) select * from A
7) select * from B
--- transaction still continued
8) insert into B seelct * from C
9) insert into C seelct * from D
-- in the body of an insert trigger on table D
   ... if @a <> @b
    rollback transaction
-- transaction may end here
10) select * from F
11) if exists( select 1 from D where @a = z )
    rollback transaction
-- transaction may end here
12) select * from A
13) select * from B
14) select @a = name from C
15) if exists( select 1 from D where @a = z )
16) insert into B select * from A
17) commit transaction
-- transaction may end here


Again cycle starts with first update statements and finishes with first commit or rollback.

When your original assumption will work - is when the
set implicit transaction off
In this case SQL Server runs in the autocommit mode, when each update statement is considered complete transaction.
0
 
stinoAuthor Commented:
Okay, I've modified my trigger to only rollback if an error occurs.  I'll assume that it will auto commit if successfull and this is done outside of the stored procedure because SQL Server is in auto comit mode by default.  If it is successfull but a previous BEGIN Trans statement was issued then it will not auto commit.  Is this safe to assume?  Then in the stored procedure, if the insert or update on that table fails, I don't rollback because the trigger will have already performed the rollback.



Quick illustration of stored procedure now

Begin Trans

Insert in table A.

If fail then
   rollback trans
   return (-1)
end if

if some_flag then
   update table B
   if fail then
      return (-1) --Notice, I do not rollback because the trigger will have done the rollback
   end if
Else
    insert into table B
   if fail then
      return (-1) --Notice, I do not rollback because the trigger will have done the rollback
   end if  
end if

insert into table c

if fail then
   rollback trans
   return (-1)
else
   commit trans -- I'm done now so I can commit everything
   return (success_flag)
end if
0
 
mironCommented:
"
...
If it is successful but a previous BEGIN Trans statement was issued then it will
not auto commit.  Is this safe to assume?  
...
"

yes.

"
...
I've modified my trigger to only rollback if an error occurs
...
"
I assume that in the trigger's body is a statement,
"if fail and then
     rollback transaction
"

One thing is to make sure that this table updated / inserted only from stored procedure that provide for explicit / implicit transaction. The other, as redundant it may sound, the rollback lifts up changes up to the BEGIN Transaction in the body of the stored procedure. Which is echoing to
"
...
Then in the stored procedure, if the insert or update on
that table fails, I don't rollback because the trigger will have already performed the rollback.
...
"
The only little thing that is still unclear to me is
"
...
I've modified my trigger to only rollback if
...
"
is it to rollback only under some condition, or only rollback without commit, or only rollback when previous logic included nested transaction
BEGIN Trans
...
IF fail ROLLBACK Trans
else
COMMIT TRANS

if the trigger included nested transaction than you need to allow stored procedure to continue and to keep the nested transaction intact. The nested transaction in the body of stored procedure can happen only after explicit statement
BEGIN TRANSACTION
regardless of implicit transaction or autocommit mode.
0
 
stinoAuthor Commented:
the trigger will rollback only after some condition.  basically it checks to see if an inserted field value exists in another table.  If it does, then cool... if it doesn't then rollback the insert or update.
0
 
stinoAuthor Commented:
You guys have been of great help!!!    Miron, I'll post some point for you as well.  Thanks again everyone!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now