Solved

Transaction Logging

Posted on 2001-09-07
13
768 Views
Last Modified: 2008-03-10
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
Comment
Question by:stino
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 3

Expert Comment

by:mathavra
ID: 6465600
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
 
LVL 1

Author Comment

by:stino
ID: 6465706
But then won't each transaction be committed or will it wait until it sees the commit tran statement?
0
 
LVL 3

Expert Comment

by:mathavra
ID: 6465713
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 3

Expert Comment

by:mathavra
ID: 6465797
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
 
LVL 1

Author Comment

by:stino
ID: 6465803
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
ID: 6466673
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
 
LVL 1

Author Comment

by:stino
ID: 6466873
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6467045
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
 
LVL 9

Expert Comment

by:miron
ID: 6467486
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
 
LVL 1

Author Comment

by:stino
ID: 6467808
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
 
LVL 9

Expert Comment

by:miron
ID: 6468006
"
...
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
 
LVL 1

Author Comment

by:stino
ID: 6468468
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
 
LVL 1

Author Comment

by:stino
ID: 6468616
You guys have been of great help!!!    Miron, I'll post some point for you as well.  Thanks again everyone!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question