Solved

Stored Procedure Commit.

Posted on 2001-07-24
6
833 Views
Last Modified: 2008-03-17
Does a SQL 7.0 stored proc rollback if part of the procedure fails or do you have to use Begin Trans..., Commit Trans... and Rollback Trans...

I am just curious if I don't include any Transaction statements if my stored procedure has 4 different DELETE calls and say the third one fails will the first two be reversed?

A reference for the answer would also be appreciated.

Thanks!
0
Comment
Question by:MTroutwine
[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
  • 3
  • 2
6 Comments
 
LVL 1

Accepted Solution

by:
deestuar earned 50 total points
ID: 6313436
not unless you wrap it in transactions
sample included. You can use a label with goto lable when the error occurs and have the rollback code at the end


CREATE PROCEDURE sp_delCompOccasion
@p_nIdCompany int,
@p_nIdOccasion int
 AS

DECLARE @error int,
         @lcMessage nvarchar(255),
         @lcProcedure nvarchar(255),
         @strCompany nvarchar(255)

SELECT @error=0,
     @lcMessage='',
     @lcProcedure=OBJECT_NAME(@@procid)

IF EXISTS(SELECT *
            FROM compOccasion
           WHERE idCompany=@p_nIdCompany
          AND idOccasion=@p_nIdOccasion)
BEGIN

BEGIN TRANSACTION delCompOcc

     DELETE FROM  compOccasion
     WHERE idCompany=@p_nidCompany
      AND  idOccasion=@p_nIdOccasion

     SELECT @error=@@error

     IF @error<>0
     BEGIN

          ROLLBACK TRANSACTION delCompOcc
          SET @strCompany=convert(nvarchar,@p_nIdCompany)

          EXEC sp_writeto_errorlog @strCompany,
                         @error,
                         'Unable to delete data in compOccasion',
                         @lcProcedure

          RETURN @error          
     END

COMMIT TRANSACTION delCompOcc
END
RETURN @error



0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6315213
depends how your connection is set up.
If you set it to use implicit transactions then you need to explicitly commit at the end. If not then you need to start a transaction and commit it or rollback. In both cases you need to check the error status after each statement. The error status will be reset at the next statement so you can not leave it until the end
not that
delete tbl1
if @@rowcount = 0 return
if @@error <> 0 ...
will not work because the if @@error will lose the error status.
To do this put @@error and @@rowcount into variables after the delete.


begin tran
   delete tbl1
   if @@error <> 0
   begin
     raiserror('failed delete tbl1',16,-1)
     rollback tran
     return
   end
   delete tbl2
   if @@error <> 0
   begin
     raiserror('failed delete tbl2',16,-1)
     rollback tran
     return
   end
   delete tbl3
   if @@error <> 0
   begin
     raiserror('failed delete tbl3',16,-1)
     rollback tran
     return
   end
   delete tbl4
   if @@error <> 0
   begin
     raiserror('failed delete tbl4',16,-1)
     rollback tran
     return
   end
commit tran
0
 
LVL 3

Author Comment

by:MTroutwine
ID: 6315309
The problem I am having is I have a stored proc that deletes records from several different tables, one delete after another, etc.  Using the Begin Transaction statement apparently locks the entire table as I am not deleting any rows that are related.  What is occuring is if two users delete at the same time than I get a deadlock and one of the two is the victom (yuck).  

If I remove the Begin Transaction it works fine, but I want to confirm that it worked fine and if not do a rollback....

So is there a way to do this without using Begin Transaction or setting a different isolation level.

Thanks.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 18

Expert Comment

by:nigelrivett
ID: 6315327
You can use the with rowlock hint to stop page/table locks being taken. Will still take locks on the indexes though.

delete tbl (rowlock)

You can reduce deadlocks by making sure that good indexes are available for all statements to reduce the number of pages accessed and making sure that deletes, inserts, updates are actioned in the same order so that if two spids are getting table locks then the other will wait for the first rather than getting a deadlock.
Another way of stopping deadlocks at the expense of slowing updates is to have a resource table and every SP tha wishes to update contentious tables takes an exclusive lock (inside a transaction) to make sure that all other spids wait.

begin tran
select * from resourcetbl (tablockx, holdlock) where 1 = 0
delete
...
0
 
LVL 3

Author Comment

by:MTroutwine
ID: 6315350
I tried the rowlock, but let me take a look at the indexes tomorrow and give you a shout.

I really appreciate your help on this!

:>)
0
 
LVL 3

Author Comment

by:MTroutwine
ID: 6356974
I worked around this by setting the Isolation level in the stored procedure.  Thanks for your help...
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

734 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