Transaction on two DB

Question

I would like to know how could I do on this case, and if anyone can help me I will really appreciate it!

I use a DB for register and roles user, etc. I also use another DB for all the data for one user. I only have one DB for all user but I have for each user one DB for his data. When I create a user I inser data on the UserDB and also to the UserData. I am using membership api for the registering.

I want to know how could I use transaction to check that all data was insert correctly(the first time a user is created).

For example:

I register a user(create a a row for each table on my UserDB). I crate a Data data base for that user with primary info at one table of his Data DB.

How could I rollback transaction if I could insert on Data DB?

use a distributed transaction (DTC must be turned on). If same server, DTC probably won't be required, just BEGIN a Transaction... — Mitch Wheat, Apr 22 '13 at 08:54
I think you can find [this](http://stackoverflow.com/a/224702/1912688) answer useful: you can manage a transaction on multiple servers. — misleadingTitle, Apr 22 '13 at 08:54
I cant use distributed transaction because I use merbership api. — user2112420, Apr 22 '13 at 09:16

score 3 · Accepted Answer · edited May 23 '17 at 12:28

As noted by others in comments, you will need to do use a distributed transaction to ensure both database transactions are conducted under a single Unit of Work (or even if updating the same database, because Membership will use its own connection, DTC is still needed). By wrapping both your calls to the ASP Net Membership API and to your other database connection (e.g. Linq2SQL or Entity Framework) under a System.Transactions.TransactionScope, you can ensure that your updates are ACID.

Note that you'll need to have MS DTC running and configured on your app server and both database servers.

Below is an example:

using (var ts = new TransactionScope())
{
    // Update Membership user   
    MembershipUser currentUser = Membership.GetUser(someUserId);    
    currentUser.Comment = "HairColor is changing";
    Membership.UpdateUser(currentUser);

    // Update equivalent record in other database (assumed EF Context and Same Primary Key)
    var otherUser = _db.OtherUserTable.Where(ut => ut.UserId == someUserId)
                                      .FirstOrDefault();
    otherUser.HairColor = "Orange";
    _db.SaveChanges();

    // Commit  
    ts.Complete();
}

One caveat with TransactionScope to note is that the default isolation level is Read Serializable, which can be overkill in many situations - Read Committed is usually sufficient for most cases. See here for more: Why is System.Transactions TransactionScope default Isolationlevel Serializable.

Transaction on two DB

1 Answers1