TransactionScope across Multiple Methods is not ACID?

Question

I have a code like this:

using (TransactionScope transactionScope = new TransactionScope())
        {
            SetDefaults(products);

            // Map the SizeCollectionIds of the products
            _dataAccess.MapProductSizes(products);

            // Mass update and insert missing parent records to the database
            _dataAccess.UpdateParents(products);

            // Get ids of parent products that were newly inserted
            _dataAccess.PopulateParentProductByParentSku(products);

            // Insert children into database
            _dataAccess.InsertProducts(products);

            // Insert the UPCs into the database
            _dataAccess.InsertUPCs(products);

            // Get Product Ids of newly inserted records
            _dataAccess.PopulateProductIds(products);

            // Get just the parent products to insert the brands
            List<ParentProduct> parents = (from prod in products
                                           select prod.ParentProduct).Distinct().ToList();

            // Insert ParentProductBrand record
            _dataAccess.InsertParentProductBrands(parents);

            // Insert the custom attribute records
            _dataAccess.InsertProductCustomAttributes(products);

            transactionScope.Complete();
        }

What I intend, is that if an error occurs anywhere in the methods called in the transaction scope, that the transaction is rolled back, but after some testing it seems that this is not the case and my data ends up half baked. Is there something I'm missing? Do I have to wrap the data access calls within the methods themselves in their own TransactionScopes to get this to work?

What are you doing in your data access methods? you should post at least one. If your data access methods are correct the above should work as you expect — undefined, Jun 21 '12 at 00:50
The transaction will be rolled back if 1) `ts.Complete()` is not called *and* 2) the Connections used *have enlisted in* the TS. Note that **already open connections will not automatically enlist in a TransactionScope**. Also verify the DA provider is using the TS [correctly] and the "half baked" isn't the result of side-effects outside the DA provider. — , Jun 21 '12 at 00:57
(That bold bit is very important. Some DALs like LINQ2SQL will, by default, open up new connections at each `SubmitChanges`. Other providers might reuse the same connection. Knowing more about the provider used here -- and perhaps a peak at the DAL -- would lead to more useful answers.) — , Jun 21 '12 at 01:10
Ohhhh, so I have to open the connection AFTER I create the TransactionScope, is that correct? — jjm340, Jun 21 '12 at 01:25
@jjm340 For auto-enlist, yes :) You can manually enlist it (see the appropriate SO questions). — , Jun 21 '12 at 05:17
e.g. http://stackoverflow.com/questions/934316/is-there-a-way-to-use-transactionscope-with-an-existing-connection?rq=1 (but I recommend using a design that can take advantage of the auto-enlist) — , Jun 21 '12 at 05:28
Ok, thanks everyone, I marked the post below this one as the answer even though my question really was answered in these comments. Thanks again! — jjm340, Jun 21 '12 at 16:45

score 0 · Accepted Answer · answered Jun 21 '12 at 01:37

0

Looks like in your DataAccess layer several instances of the database connections are created. Try to instantiate your database connection in the DataAccess class constructor and use it across your DataAccess methods. You may want to read this blog post

answered Jun 21 '12 at 01:37

Prashanth Thurairatnam

4,353
2
14
17

1

No, my data access layer opens only one connection for the entirety of it's existence, but according to pst from above it looks like I need to create that connection AFTER the TransactionScope is created, or the connection won't know what transaction to enlist in – jjm340 Jun 21 '12 at 01:42
Yes; that's the most appropriate approach. Check this discussion `http://stackoverflow.com/questions/934316/is-there-a-way-to-use-transactionscope-with-an-existing-connection` – Prashanth Thurairatnam Jun 21 '12 at 01:52

TransactionScope across Multiple Methods is not ACID?

1 Answers1