none
Question about using thread to perform transactional db operation. RRS feed

  • Question

  • Hi,

    Not sure if this is the right forum to post this question.

    I have a C# application which has to insert data into multiple SQL2008 databases and also ensure the insert in completed in a single transaction. Even if one of the insert fails the whole operation has to be rolled back.

    Right now It has been achieved  by using C# Transaction class by calling the databases using a loop and at the end using the dispose or complete method of the transaction class to either rollback or commit the transaction.

    My question is can I fire multiple threads instead of performing these operations sequentially at the same time maintaining the entire operation as a transaction. I need to ensure even if 1 thread fails the entire operation has to be rolled back. I saw the below sample code where a parent thread waits for child thread to commit. Is it possible to rollback the work of a parent thread  and when one of the child thread fails and also to make sure the work done by the other threads are rolled back?


    Thanks in Advance.

    class DependentTx
    {
    /// <summary>
    /// The main entry point for the application.
    /// </summary>
    static void Main(string[] args)
    {
    try
    {
    using (TransactionScope scope = new TransactionScope())
    {
    // Perform transactional work here.

    //Queue work item
    ThreadPool.QueueUserWorkItem(new WaitCallback(WorkerThread), Transaction.Current.DependentClone(DependentCloneOption.BlockCommitUntilComplete));

    //Print the transaction information
    Console.WriteLine("Transaction information:");
    Console.WriteLine("ID: {0}", Transaction.Current.TransactionInformation.LocalIdentifier);
    Console.WriteLine("status: {0}", Transaction.Current.TransactionInformation.Status);
    Console.WriteLine("isolationlevel: {0}", Transaction.Current.IsolationLevel);

    //Call complete on the TransactionScope or not based on input
    ConsoleKeyInfo c;
    while (true)
    {
    Console.Write("Complete the transaction scope? [Y|N] ");
    c = Console.ReadKey();
    Console.WriteLine();

    if ((c.KeyChar == 'Y') || (c.KeyChar == 'y'))
    {
    //Call complete on the scope
    scope.Complete();
    break;
    }
    else if ((c.KeyChar == 'N') || (c.KeyChar == 'n'))
    {
    break;
    }
    }

    }
    }
    catch (System.Transactions.TransactionException ex)
    {
    Console.WriteLine(ex);
    }
    catch
    {
    Console.WriteLine("Cannot complete transaction");
    throw;
    }

    }

    private static void WorkerThread(object transaction)
    {
    //Create the DependentTransaction from the object passed to the WorkerThread
    DependentTransaction dTx = (DependentTransaction)transaction;

    //Sleep for 1 second to force the worker thread to delay
    Thread.Sleep(1000);

    //Pass the DependentTransaction to the scope so work done in the scope will be part of the transaction passed to the worker thread
    using (TransactionScope ts = new TransactionScope(dTx))
    {
    //Perform transactional work here.

    //Call complete on the transaction scope
    ts.Complete();
    }

    //Call complete on the dependent transaction
    dTx.Complete();
    }
    }


    Friday, September 12, 2008 12:21 AM

Answers

All replies