none
SQLTransaction usage RRS feed

  • Question

  • I want to call three different stored procedures within a SQLTransaction. These three stored procedures insert data. This data is typically header data with detail data. My question is, if I use a SQLTransaction client side and call these three stored procs and one of them fails, will rolling back the transaction client side cause the data that got inserted from the other two procs roll back? or would I have to set this all up as inline sql code to get all of the data to roll back?

     

    Thanks,

    George

    Thursday, January 3, 2008 7:05 PM

Answers

  • (What I need to happen is if one of them fails, I need everything to roll back)
     

    I would not use SavePoint because it gives you rollback control but you are looking for automatic rollback so don't use SavePoint but I think you need to run some tests with SavePoints and some without because of the DB transactions which may need the SavePoint because more than one transactions is technically nesting and SavePoint was created to give control of the nesting process. 


    (Should I have transacations within each stored procedure and also the client side or will having the client side transaction be good enough or should I have both?)

     

    I think the ADO.NET transaction will do but run some tests so you don’t run redundant code in transaction that will affect your performance.

     

    Friday, January 4, 2008 6:52 PM

All replies

  • If you are calling three stored procs you can use transactions in the proc and use SavePoints which lets you keep all the none commited transactions before the error and rollback only the one with error.  You can also use SavePoint in ADO.NET transactions as in the link below.  The main thing to know in the database transaction is a unit of work either commited or rolled back if there is no SavePoint defined.   Which means an error at transaction 99 without SavePoints will roll back to 1. Hope this helps.

     

    http://msdn2.microsoft.com/en-us/library/ms971557.aspx

     

    Thursday, January 3, 2008 8:42 PM
  • yes,

     

    once you started a transaction and didnt commit and didnt abort/rollback, the subsequent call to database will run in the same transaction context owner by your database session.

     

    if the 3o. command fails, all the other will rollback

     

    detail: each command must be associated with the transaction within current connection.

     

    bests regards

    Friday, January 4, 2008 12:48 AM
  • (if the 3o. command fails, all the other will rollback)

     

    The above is only correct if SavePoint is not used SavePoint is implemented in ADO.NET and T-SQL but not System.Transaction because those transactions are none atomic.


    http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction_members.aspx

     

    Friday, January 4, 2008 1:07 AM
  • Thanks Caddre,

     

    i did not consider SavePoints because i ansewered just the original question, that asks directly on fail results,

     

    but you consideration is clear and is very important.

     

     

    thanks in advanced

    Friday, January 4, 2008 1:39 AM
  • Thanks for your response. What I need to happen is if one of them fails, I need everything to roll back. Would I need SavePoints to do this or will this happen automatically when I roll back the transaction on the client side?

    In other words, if one insert fails, nothing will be saved...


    Should I have transacations within each stored procedure and also the client side or will having the client side transaction be good enough or should I have both?
    Friday, January 4, 2008 6:28 PM
  • (What I need to happen is if one of them fails, I need everything to roll back)
     

    I would not use SavePoint because it gives you rollback control but you are looking for automatic rollback so don't use SavePoint but I think you need to run some tests with SavePoints and some without because of the DB transactions which may need the SavePoint because more than one transactions is technically nesting and SavePoint was created to give control of the nesting process. 


    (Should I have transacations within each stored procedure and also the client side or will having the client side transaction be good enough or should I have both?)

     

    I think the ADO.NET transaction will do but run some tests so you don’t run redundant code in transaction that will affect your performance.

     

    Friday, January 4, 2008 6:52 PM
  • Yes, if one fails and they are all part of the same transaction that you create client side, then a client side rollback will undo the other ones.  And if you don't call Commit as well, they won't be committed.

    . Here's an example I wrote a while ago that 'proves' it's working as expected.
    Sunday, January 6, 2008 6:48 AM
  • Savepoints aren't needed to accomplish what you want  and unless I misunderstand what you're doing, I think they would largely complicate things.  You *can* handle the whole scenario just from client side code - I just posted a link to an article I wrote that shows how to do this and proves it works as advertised. Many will argue you should do the whole thing on the server and I usually lean that way, but there's nothing wrong with doing it totally client side and the only rule there is that you should mix them b/c it's very easy to forget one or the other.  So handle the whole thing client side or server side - but not both unless you're positive niether you nor anyone else will ever get confused.

     

    So the short answer is that Yes, just go client side - if one fails and you call RollBack, they'll all rollback as long as they are all participating  in the same transaction.

     

    Sunday, January 6, 2008 6:52 AM
  • I don't want to get picky, but doing the transactions in .NET code doesn't necessarily mean it's "client side". This is probably just all semantics anyway, but hopefully you see my point.

     

    I see that your point is if it's not done in TSQL by the SQL Server itself, you're considering it to be "client side". What I'm saying is that if you have .NET code that resides on a server (a DataAccess class that is accessed through Web Service methods or Remoting) then I would consider that code to be "server side".

     

    Sorry, I'm really being picky, but I thought I should mention it.

    Sunday, January 6, 2008 9:25 PM
  • I was successful in getting the client side code to roll back the transaction whenever there was an error. ADO.NET rocks!

    Thanks for the help
    George
    Thursday, January 10, 2008 2:51 PM