Need Help about ADO.Net Transaction

Question

I develop C# window application with Microsoft SQL Server 2005 sp3.
My Application has ploblem while one of client save Order Document.
Another client cannot select data from Order table util saving process is complete.
I used Transaction and Isolation=ReadUncommit to save Order Document.
My Application has 3 table is OrderHd, OrderLine and OrderSerial.
Record of OrderSerial per 1 Order Document about 1,000-5,000 record

//My Saving Process
try
{
dbConn.Open();
dbTran = dbConn.BeginTransaction(IsolationLevel.ReadUncommitted);
//1) Save OrderLine
//2) Save OrderSerial
//3) Save OrderHd
dbTran.Commit();
}
exception
{
dbTran.Rollback();
}
finally
{
dbConn.Close();
}

How can I fix this Ploblem ??? _ _"

Answer 1

Not sure what your question is exactly, but have you considered using Snapshot isolation level?

Answer 2

If you want unpredictable results saved to your database, continue using a transaction level of READ UNCOMMITTED, otherwise you would be better ensuring that the tables involved have appropriate indexes, and that indexes and statistics are up to date.

Missing or sub-optimal indexes are one of the most common (and overlooked) causes of excessive database locking.

Answer 3

        //My Simulate Saving Process
        string mstrConnStr = "Data Source=serverzx; Initial Catalog=Test;User ID=sa;Password=x2y2;";
        System.Data.SqlClient.SqlConnection conn = new SqlConnection(mstrConnStr);
        System.Data.SqlClient.SqlCommand cmd1 = new SqlCommand("Insert Into OrderHd (OrderID , Code,Name) values (@Para0,@Para1,@Para2)",conn);
        System.Data.SqlClient.SqlCommand cmd2 = new SqlCommand("Insert Into OrderLine (OrderLineID , OrderID,Detail) values (@Para0,@Para1,@Para2)",conn);

        System.Data.SqlClient.SqlTransaction tr = null;

        try
        {
            conn.Open();

            cmd1.Parameters.Clear();

            tr = conn.BeginTransaction(IsolationLevel.ReadUncommitted);

            string strCode = DateTime.Now.ToString("yyyyMMdd : HHmmss");
            string strHead = Guid.NewGuid().ToString();

            cmd1.Transaction=tr;
            cmd2.Transaction=tr;

            cmd1.Parameters.Add("@Para0",strHead);
            cmd1.Parameters.Add("@Para1",strCode);
            cmd1.Parameters.Add("@Para2","Name "+strCode);
            cmd1.ExecuteNonQuery();

            for (int i=0;i<5000;i++)
            {
                string strItem = Guid.NewGuid().ToString();
                cmd2.Parameters.Clear();
                cmd2.Parameters.Add("@Para0",strItem);
                cmd2.Parameters.Add("@Para1",strHead);
                cmd2.Parameters.Add("@Para2","Detail "+i.ToString());
                cmd2.ExecuteNonQuery();
                System.Threading.Thread.Sleep(10);
            }
            tr.Commit();
        }
        catch (Exception ex)
        {
            tr.Rollback();
            MessageBox.Show(ex.Message);
        }
        finally
        {
            conn.Close();
        }

Answer 4

Try using the ReadUncommitted isolation level on the code which reads your data, not within the code that saves it.