1

I was assigned to make a stop button for our system transaction. Upon clicking the button STOP, I must be able to interrupt the stored procedure A and rollback. So I made a few checkers in my stored procedure.

Here's an example flow of my stored procedure: 

START PROCEDURE
   BEGIN TRANSACTION A

      BUSINESS LOGIC...

      -- STOP CHECKER
      IF((SELECT STATUS FROM TABLENAME WITH(NOLOCK) WHERE CODE = @Code) = 'STOPPED')
         GOTO STOP_FUNC

      BUSINESS LOGIC...

      -- STOP CHECKER
      IF((SELECT STATUS FROM TABLENAME WITH(NOLOCK) WHERE CODE = @Code) = 'STOPPED')
         GOTO STOP_FUNC

   COMMIT TRANSACTION A
   RETURN 1  

   STOP_FUNC : 
   BEGIN
     ROLLBACK TRANSACTION A
     RETURN -1000
   END

END PROCEDURE

And here's my query for my Stop button : 

UPDATE TABLENAME SET STATUS = 'STOPPED' WHERE CODE = '50'

I made a few test runs

TEST RUN 1
When executing the UPDATE first, the stored procedure will then stop at the first checker -- expected behaviour

TEST RUN 2
When executing the stored procedure first and executing the UPDATE later, the UPDATE goes to a deadlock according to the Activity Monitor. It will then wait for the SP to finish before updating the status as STOPPED.

I really am stuck right now. I know that the transaction may be locking my table but that's why I put the 'WITH (NOLOCK)' in there as advised by my coworkers. Can someone please advise a new workaround? I have little experience to this since I am originally a front end developer

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
AutumnMelody
  • 53
  • 4
  • @ϻᴇᴛᴀʟ is it the Read uncommitted? – AutumnMelody Dec 04 '19 at 07:49
  • It's still not working :(( I also set the deadlock priority after the isolation but it didnt work – AutumnMelody Dec 04 '19 at 07:56
  • i'm guessing a bit here, but what fails is not your `select`, but the `update`. You will probably have to use nolock hints in the business logic queries that modify the table. – George Menoutis Dec 04 '19 at 08:41
  • @GeorgeMenoutis I dont think nolocks are possible with UPDATE so i tried WITH(ROWLOCK) but it didnt work :(( – AutumnMelody Dec 05 '19 at 01:22
  • It is a little suspicious that the same table you want to use for the stop function gets updated from the procedure itself. One possibility is that you are using the table for many different things, which is poor design, so in that case you should make a new table. Another possibility is that you use this procedure to stop another. This...is complicated. I am not sure trans-proc flow control can, or should, be implemented like that. – George Menoutis Dec 05 '19 at 08:32
  • I tried to create a different table and it worked :) I am able to stop it properly now :) It was a long old SP that was given out to me and yes I do agree that there are some approach that I think is not right. But I am still new, can you possibly recommend me some good learning websites/materials to studyl? – AutumnMelody Dec 06 '19 at 00:00
  • 1
    Google and stackoverflow are your friends. Just make sure to search first before posting. This question was deep/complicated enough, so it is OK that you asked this as a newbie. http://www.sommarskog.se/ has a collection of excellent, advanced level articles. – George Menoutis Dec 06 '19 at 07:50

0 Answers0