Solved

Transaction Logging

Posted on 2001-09-07
13
760 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
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 3

Expert Comment

by:mathavra
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 3

Expert Comment

by:mathavra
Comment Utility
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
Comment Utility
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:stino
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
"
...
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
Comment Utility
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
Comment Utility
You guys have been of great help!!!    Miron, I'll post some point for you as well.  Thanks again everyone!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now