Transaction Commit & Rollback 2

[donutman]

I've been Googling this topic, but I've got a few questions that I don't find answers to:
If a SP has the Commit & Rollback logic within it and the VB program that calls it raises an error during the call because of a connection timeout (eg. possible network cable/router failure) will that trigger the Rollback logic? I do have the check for @@Error after each SQL statement of the multi-stepped SP.
Will any error that might occur in a SP raise an error at the client or is explicit logic required to fully inform the client?
Aren't there errors (short of server power failure) that cause a SP to discontinue processing the next SQL statement so that the Rollback statement is never reached? If so doesn't that imply that best practice is to apply Commit & Rollback logic at the client app rather than within the server's SP?
What are the implications/side effects of a Begin Transaction without a Commit or Rollback Transaction? If there are no negative effects upon the server, then why bother coding Rollback logic in a SP, just don't Commit.
This whole area of programming appears to me to be full of subtle pitfalls or am I just being paranoid?

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

If you are in the middle of the transaction and the session is terminated the transaction should roll back. This would include a client side termination due to timeout.

Any error that the SP throughs will be returned to the client.

I would recommend running the transaction at the T/SQL level unless you need to be able to run a SQL statement, then do some client side processing while the transaction is locked then run more SQL processing.

However this type of client side locking can quickly lead to blocking and deadlocking issues and should be avoided when possible.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[donutman]

Thanks Denny. The reason I asked about the raising of the error to the client is that I'm a bit confused. From my readings on the topic it clearly shows that you need to check @@Error after each SQL step. That implies that an error can be raised that doesn't terminate the entire SP. Yet there are certainly errors that cause premature termination of the SP (power failure for sure).
So you are saying that even when additional SQL steps run (successfully) after the one that causes an error, the error is still passed to the client. How about if you "handle" the error with a Rollback Transaction statement. Does that "fully" handle the error and thereby not pass the error to the client? I wonder if the exception is thrown to the client immediately after the error occurs rather than waiting until the SP has completed? Another words does the server keep track of a "different" @@Error so that it knows to throw the exception when the SP has completed or is control immediately passed back to the client while the server continues processing the SP. The devil is in the details!
It's really quite odd that SPs work this way...continuation of the next step after an unhandled runtime error. Is TSQL the only "language" that does that? I wonder why they programmed it that way. Error handling in TSQL seems to be a bit immature to me. What are your thoughts?

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

When dealing with errors you also need to keep in mind the severity level of the error.

If you have an error that's a data error then your procedure will continue to run. The error will be transmitted back to the client application.

With SQL 2005 you have standard TRY CATCH blocks within the T/SQL so that you can actually catch errors within the T/SQL without the error being passed to the calling application, then you can do something with the error data.

I'm not sure if I answered your question.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[donutman]

Denny,
My curiosity got the best of me, so I did some testing. I created a SP that had three "major" (2 Inserts and 1 Select Into) statements. The 2nd Insert duplicated a primary key so that it would raise an error. I ran the SP using VB6 (SP6) and MSSQL 2K. The results were very interesting.
VB6 does not raise an error UNLESS it is the first "major" statement within the SP that causes the error! Statements like Set @Variable=1 don't count as a "major" statement nor does Select @Variable=1, but Select @Variable does count as a "major" statement.
If you place a Begin Tran statement in a SP but not a Commit or Rollback Tran, then your server is negatively impacted. I don't know how severely, but I did reboot the server to get it back to normal...I didn't want to wait around while it tried to heal itself...EM locked up when trying to access the table used in the SP. Maybe if I had closed QA the thread that ran the SP would have "closed" and the server would have been healed. Didn't think of it at the time and I don't want to test it again!
The SP does run to its end before control is returned to the client even if the error is raised by the 1st "major" statement of the SP.
I've concluded the following from my testing:
1) Code the Begin Tran and Commit Tran within the SP, because you don't want the possibility of the client never issuing a Commit or Rollback Tran.
2) VB6 programmers should be aware of the danger of not explicitly checking @@Error after each and every "major" TSQL statement. If they don't use Commit/Rollback logic, then they should at least be summing @@Error into another @Variable and returning the @Variable with a Return @Variable statement or alternatively branching out of the multi-step sequence after an error. And remember using On Error GoTo ErrorHandler within VB6 doesn't catch all errors within a multi-step SP!
Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

If you place a Begin Tran statement in a SP but not a Commit or Rollback Tran, then your server is negatively impacted. I don't know how severely, but I did reboot the server to get it back to normal...I didn't want to wait around while it tried to heal itself...EM locked up when trying to access the table used in the SP. Maybe if I had closed QA the thread that ran the SP would have "closed" and the server would have been healed. Didn't think of it at the time and I don't want to test it again!

If you had closed the QA window it would have told you that you have an open transaction that needs to be commited. If in that window you had executed a commit or rollback the transaction would have been closed.

Any access to that page or record (depending on what was locked) would have been blocked until you ran the commit or rollback.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[donutman]

Thanks for clearing that up, Denny. I'm not sure how to test that at the client level. It would be easy to issue a Begin Tran in VB and not Commit it by prematurely terminating the application, but how would I know if the server still had the process running?
BTW, in case someone relies on what I've posted, I used the OLEDB provider not ODBC when running the SP. Maybe with the later it would work as expected.
Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

On the SQL Side you could check sp_who2 or sp_lock and see if your process is still running, or if it's still got locks.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ESquared]

Hi Karl!

VB6 does not raise an error UNLESS it is the first "major" statement within the SP that causes the error!

What's happening is that errors are only returned/handled with recordsets (or failed recordset-producing statements). If a single batch returns multiple recordsets, you will only see the error associated with the first recordset until you use the NextRecordset method on your returned recordset object. Then you'll see any errors with that recordset, and so on and so forth.

Also, in VB, assuming you're using ADO, it's not enough to trap for errors and display the VB error. The VB error is accurate, but generic. You have to enumerate the ADO Connection.Errors collection and display all of the errors. If you don't, you run the risk of not seeing the "real" error message. See Extracting Error Information from ADO in VB for more detailed info.

Beginning a transaction has no negative impact unless you acquire some kind of lock (row, page, extent, table, and so on). Until you rollback, commit, or your session closes/is killed/server crashes/computer is powered off (an implied rollback), locked resources will be unavailable for anyone else, forever.

Last, check out the difference between COMMIT and ROLLBACK when dealing with nested transactions:

BEGIN TRAN
   Perform work A
   BEGIN TRAN
      Perform work B
   COMMIT TRAN -- does nothing
COMMIT TRAN -- commits all work A and B

BEGIN TRAN
   Perform work A
   BEGIN TRAN
      Perform work B
   ROLLBACK TRAN -- rolls back work A and B
ROLLBACK TRAN -- error, no transactions outstanding

BEGIN TRAN Name1
   Perform work A
   BEGIN TRAN Name2
      Perform work B
   ROLLBACK TRAN Name2 -- Error, no such (root) transaction
   ROLLBACK TRAN Name1 -- rolls back work A and B

BEGIN TRAN
   Perform work A
   BEGIN TRAN
      Perform work B
      SAVE TRAN Name1
         Perform work C
      ROLLBACK TRAN Name1 -- rolls back work C
   COMMIT TRAN -- does nothing
ROLLBACK TRAN --rolls back work A and B

I learned this lesson just two weeks ago. From now on, if I use a transaction inside a stored procedure, I'm going to do it like this:

CREATE PROC SP1
AS
BEGIN TRAN
SAVE TRAN SP1 -- same name as SP
Perform Work
SELECT @Error = @@Error, @RowCount = @@RowCount
IF @Error <> 0 BEGIN
   ROLLBACK TRAN SP1
   COMMIT TRAN -- commits no work, but reduces trancount
   RETURN @Error
END
ELSE IF @RowCount = 0 BEGIN -- data operation successful, but wrong results
   ROLLBACK TRAN SP1
   COMMIT TRAN
   RAISERROR ('SP1: Expected records not found when doing X.', 16,1 )
   RETURN -1
END

--Repeat work/error block indefinitely

COMMIT TRAN -- Success!
RETURN 0 -- No error

Also, instead of the begin/end error block, you could do:

IF @RowCount = 0 SET @Error = -1
IF @Error <> 0 GOTO SPError
-- Other work

GOTO Final

SPError:
IF @Error = -1 RAISERROR ('Error Message', 16, 1)
ROLLBACK TRAN SP1

Final:
COMMIT TRAN
RETURN @Error -- will contain last error value

This way, the calling SP or client can check the return code and decide what to do, without having his own transaction unexpectedly rolled back (really really nasty behavior!).

I know you asked this question a long time ago... maybe this could help someone else besides you. And I admit I looked you up to see if you were still active on Tek-Tips. And it was interesting how I had just learned some of this myself and thought it worth sharing as it took a fair amount of time to slog through...

 

[donutman]

Nice work E*E. Figures you'd get the real skinny on the issue. I vote for the 2nd version...it violates the "avoid goto" dictum, but it is easier to follow (KISS) and the Return is like a GoTo anyway.
Hope all is well with you and yours.
Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

nice to see you back donutman!

Questions about posting. See faq183-874

 

[donutman]

Yo Sis. Not really back...thread was on email notification. Interesting to see so many of the regulars still on the MVP list. I'm up to my ears in English Muffins, a new account. Best wishes to you too.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

ESquared,
Your comment about using NextRecordSet appears to be VITAL to anyone who thinks he has trapped all errors with just an "On Error GoTo ErrorHandler" statement. I don't want to think about the number of times that I've missed that point! You deserve a lot more stars for your post [IMHO].
But how do you know how many RecordSets to retrieve? A SP with an IF statement could change the expected number. Do you just keep looping until you get an error indicating that you have checked them all? I can't find a RecordSet.Count.
Karl

   Dim adoCnn As ADODB.Connection
   Dim adoCmd As ADODB.Command   
   Set adoCnn = New ADODB.Connection
   Set adoCmd = New ADODB.Command
   Dim adoRS As ADODB.Recordset
   Set adoRS = New ADODB.Recordset
   Dim lng As Long
   With adoCnn
      .ConnectionString = "Provider=SQLOLEDB.1;Password=..."
      .Open
   End With
   On Error GoTo ErrorHandler:
   With adoCmd
      .ActiveConnection = adoCnn
      .CommandType = adCmdStoredProc
      .CommandText = "spTest"
      .Parameters.Refresh 

      Set adoRS = .Execute
      MsgBox .Parameters(0)
 
      Set adoRS = adoRS.NextRecordSet 'Repeat how many times?
      MsgBox .Parameters(0)
    
   End With
   Exit Sub
ErrorHandler:
   MsgBox Err.Description
   Resume Next

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Well, reading about th Recordset object, and the ADO NextRecordset Method (a great web site I refer to often), it doesn't seem there is a way to know.

But shouldn't you know in advance how many recordsets you expect?

If you won't, then why not write a generic function to convert a recorsdet object into an array. You can put all the error handling you want into it because you're only writing it one time. Then you can use UBound and a loop to use the returned recordsets.

You could even make your own object that gave you similar results but with things like RecordsetCount and errors in an array without complex error checking in your calling code...

 

[donutman]

But shouldn't you know in advance how many recordsets you expect?

I'm a fanatic when it comes to SPs. I very rarely run ad hoc queries within VB, so I currently have a total of 517 SPs. They come in all flavors: no selects, selects and updates/inserts/deletes, and multi everything. Sorry to admit that I have one cursor SP. I'm afraid to look how I (didn't) handle errors in that one! I also have SPs with multiple conditional branches even ones with conditional execution of nested SPs! I have no desire to look through 517 SPs. But it wouldn't be unreasonable to slam some type of generic NextRecordSet loop logic into every procedure that calls a SP in old VB code where I THINK it might matter. My most complicated app uses a class to make almost all of the SP calls from a method for SPs with no RS, and a method with SPs that return a RS among a host of other methods. Hehe, that was literally my first (production) windows app (VB6 too - single user, random access file, quickbasic holdout). What can I say, I was naive and thought object oriented programing sounded like a good idea. It's the first and last time I created a class in VB6. Maybe it has finally become useful!
Can you believe that I've done all that and never realized the need for NextRecordSet? I now understand why I was puzzled about the lack of a raised error about 6 months ago in a multistep SP that created some orphaned records (or something else - can't remember exactly). I cleaned up the mess and didn't spend the time to find out the cause! Shame on me.
Cut me some slack though, I do have an excuse. I'm not a professional programmer...I havveee tooo make the donuts.
Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[chrissie1]

Cut me some slack though, I do have an excuse. I'm not a professional programmer...I havveee tooo make the donuts.
Karl

Yeah and I'm a muffin. And you eat to many of them too ;-).

Christiaan Baes
Belgium

"My new site" - Me

 

[donutman]

Well, well if it isn't the from Belgium. What are you doing loitering around the SQL Programing boards...must have smelled the VB action.
You're right about the Muffins. Have to make a million of 'em a year for this new account...fortunately I've been getting tired of their taste.
That reminds me, where is that English Muffin man, vongrunt? I know he's across the pond...isn't it Great Britain? I don't see him on the leader board, so he must be MIA.
BTW, remember our chat about golf? How I suggested that it shouldn't be too difficult to mimic the swing of a professional, just not with the consistency, accuracy and power of a pro. Found out I couldn't even do the mimic part...in fact I'm scheduled to have back surgery in Dec. due to a herniated disc that I acquired trying to mimic the swing a few times too often! It seems to be healing so I may cancel the knife...it's only taken 5 months. So now this old man has lost his mind and body. What's left for me to do? Perhaps a Tek-Tips MVP in the VB.NET forum like you?
Oh, how I miss this place...trying desperately but never quite keeping up with ESquared and vongrunt. And since I'm a master baker, what I miss the most is to re-torte you!
Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gmmastros]

vongrunt is from across the pond, but not Great Britain. He's been swamped lately, so, yeah... MIA. He's says that he's been putting out so many fires that he's had a bluetooth headset surgically implanted so he'll never miss a call. Personally, I hope he comes back soon because I miss him too. I've learned so much from him.

Chrissie doesn't usually post in the SQL Server forum. He heard a rumor that you were back and had to see for himself. Bit sad, really.

Being a golfer myself, my best advice is to slow down your swing. Humble mortals like us shouldn't even try to drive a ball as far as the pro's. Get good at aim and forget about distance. If you keep your ball in the fairway, you'll probably shave 10 strokes off your game.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chrissie1]

Vonnie is from croatia like sqldenis.

Even Por-golfers have back trouble. You could always try curling with sqlsister, but the brushing seems to be bad for the back too. Better stick with the donut making.


Does each muffin get a GUID?

Christiaan Baes
Belgium

"My new site" - Me

 

[donutman]

Yes, George, it is a bit sad. I apparently have a similar affect upon women. Went to my local watering hole and eatery last week and this nice young waitress nearly threw an entire pint at me. As she tried to lower the mug to the table and slide it toward me (while warmly looking into my eyes), the bottom caught an edge and toppled directly into my lap. And it was a Guinness no less.
Fortunate for the other smurfs in Belgium, Chrissie can't hoist an entire pint. Yes, Chrissie they do have a GUID. So if you ever need a job more fitting to your skills, come to America and you can match up my donuts with their respective holes.
Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]