locked
Transaction Question RRS feed

  • Question

  • I have the scenario which is related with the transcation question. I'll mimic a scenario which try to explain the problem.
    Two tables "Order" and "OrderDetail"
    "OrderId"  is a identity and primary key in table "Order". The identity will be added automatically.
    "OrderId" is a foreign key in table "OrderDetail" which refers to primary key in table "Order".
    When I create a order, the order detail also need to be created. The step of creation are:
    1. Add one record into table "OrderId".
    2. Get the identity "OrderId" which is added automatically.
    3. Set the link between order and order detail
    4 Add order detail into table "OrderDetail"

    I want the above step into one trasaction so as to ensure the data consistence, such as the order detail addition is failed, the order record will be rolledback.
    How can I do it?

    Thanks.

    Thursday, April 23, 2009 7:30 AM

Answers

  • Hi all,

    I don't believe the standard transaction mechanisms will work as described over ADO.NET Data Services (which is http based, for those unfamiliar with the technology).

    You could enforce a transactional model over the client-side operations using the code described above, but it would not enforce the transaction model on the requests being sent to perform the corresponding server-side operations. By the time you knew to roll-back the changes, some might have already been performed server-side.

    A useful option might be using SaveChangesOption.Batch when calling DataServiceContext.SaveChanges. This option tells the context to send all the operations it needs to perform as a single request, where either all operations must succeed or none will be applied. You could use code like what has been posted above to enforce the transaction on the context, then use the batching feature to convey that intent to the service.

    There is another thread open right now about batching and transactions, I suggest you take a look:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataservices/thread/2d0c6099-f4f6-4329-9da5-54378a138012

    (I know this thread was started first, but some useful information had already been posted there, and I'd prefer not to duplicate it all).
    Matt Meehan, ADO.NET Data Services (Astoria)
    Monday, April 27, 2009 4:07 PM
    Moderator

All replies

  • You have a few options.  You can use the ADO.NET connection members BeginTransaction(), followed by Commit()  ( or Rollback() ), or you can write the same logic into a trigger or SP on the backend (which is slightly faster, but may be less flexible for your needs).  Here's an article on ADO.NET transaction basics:

    http://msdn.microsoft.com/en-us/library/2k2hy99x.aspx

    Note: transactions are expensive and can affect concurrency.  If you don't have to worry about other readers briefly "seeing" a order record without its corresponding detail, then it'll be more efficient to enforce this integrity by simply trapping an exception on the Detail insert, then manually deleting the corresponding Parent record.
    masher
    Saturday, April 25, 2009 1:31 PM
  • Thanks, masher.
    What I want how can i apply the distributed transaction in ado.net service?
    Monday, April 27, 2009 2:40 AM
  • using (TransactionScope scope = new TransactionScope()

    {

       (run commands against datasource A)
       (run commands against datasource B)
       scope.Complete();
    }

    Here's an article that shows a bit more detail, along with exception handling:

    http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

    But...are you sure you need a **distributed** transaction?  It'd be an awfully strange order entry system where the Order table exists in one database, and the Order Detail table exists in another.


    masher
    Monday, April 27, 2009 3:12 AM
  • Hi all,

    I don't believe the standard transaction mechanisms will work as described over ADO.NET Data Services (which is http based, for those unfamiliar with the technology).

    You could enforce a transactional model over the client-side operations using the code described above, but it would not enforce the transaction model on the requests being sent to perform the corresponding server-side operations. By the time you knew to roll-back the changes, some might have already been performed server-side.

    A useful option might be using SaveChangesOption.Batch when calling DataServiceContext.SaveChanges. This option tells the context to send all the operations it needs to perform as a single request, where either all operations must succeed or none will be applied. You could use code like what has been posted above to enforce the transaction on the context, then use the batching feature to convey that intent to the service.

    There is another thread open right now about batching and transactions, I suggest you take a look:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataservices/thread/2d0c6099-f4f6-4329-9da5-54378a138012

    (I know this thread was started first, but some useful information had already been posted there, and I'd prefer not to duplicate it all).
    Matt Meehan, ADO.NET Data Services (Astoria)
    Monday, April 27, 2009 4:07 PM
    Moderator
  • > "I don't believe the standard transaction mechanisms will work as described over ADO.NET Data Services (which is http based..."

    Eh?  The transport protocol doesn't affect SQL Server's transactional semantics.  ADO.NET is indeed using HTTP (which itself doesn't meet the ACID test), but the underlying transaction functionality is being provided by SQL Server; ADO is simply tapping into this.   The other thread isn't relevant in this particular case...we're talking about SQL transactions, not HTTP request transactions.

    I've never tried ADO.NET with **distributed** transactions (though it should work, the TransactionScope class exists for a reason after all) but for standard transactions, I can personally confirm it works fine. 


    masher
    Monday, April 27, 2009 4:29 PM
  • You may be correct regarding SQL (and I certainly didn't mean to imply otherwise), but the ADO.NET data services client library has no notion that there is a database, let alone that it is a particular engine, let alone how to connect to it.

    In order to use the mechanism you are describing, you would have to break several layers of abstraction. While I can't recommend that approach, I certainly cannot stop anyone from doing so.
    Matt Meehan, ADO.NET Data Services (Astoria)
    Monday, April 27, 2009 5:10 PM
    Moderator
  • And to be clear, ADO.NET Data Services is a separate technology from ADO.NET Data Providers, Data Set, and so on. If you are looking for solutions using those technologies, then I apologize for any confusion, and recommend you try asking on their forums.

    Matt Meehan, ADO.NET Data Services (Astoria)
    Monday, April 27, 2009 5:17 PM
    Moderator
  • Hi ,
      If you are looking for transaction support in the client library , you can use the SaveCHangesOptions.Batch option with SaveChanges which rolls back all the changes if any of the changes in the batch fail.
      Take a look  at this thread for additional details.
      http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataservices/thread/a8474b40-c230-4814-905c-98e634797c6e/

     
    Phani Raj Astoria http://blogs.msdn.com/PhaniRaj
    Monday, April 27, 2009 6:33 PM
    Moderator
  • Masher,

      ADO.NET Data Services does not have a concept of transaction , you can set this up at the host level or the provider level . 

      http://msdn.microsoft.com/en-us/library/ms730266.aspx

      The OP is asking about Atomicity support in the client library , with rollback on failure.

      The client library supports Atomicity via Batching ( see section "Batching" of http://msdn.microsoft.com/en-us/library/cc907912.aspx)

     <blurb>
      This reduces the number of roundtrips to the data service and enables a logical scope of atomicity for sets of operations. 
    </blurb>

    Adding in the transaction support you have suggested is prabably a good solution when  you have access to the underlying SQLclient classes which handle the data access for you .
    An ADO.NET Data Service has no concept of the database, it works off of the conceptual model of the data store .
    For more details , see : Astoria data sources and system layering
    Hope this helps
    Phani Raj Astoria http://blogs.msdn.com/PhaniRaj
    Monday, April 27, 2009 7:00 PM
    Moderator
  • You two are absolutely right. I read his question as referring to ADO.NET Data Provider classes, rather than Data Services.  A silly mistake, given what forum this is in.
    masher
    Monday, April 27, 2009 11:54 PM