如何使用“页面锁定问题"修复“交易"? [英] How can I fix Transaction with Page Lock Ploblem ?

问题描述

我该如何解决带有页面锁定问题的事务?

我的应用程序有问题，而客户端之一保存了订单文档.
在保存过程完成之前，另一个客户端无法从订单"表中选择数据.
我使用Microsoft SQL Server 2005 sp3开发C#窗口应用程序.
我使用Transaction and Isolation = ReadUncommit保存订单文档.
我的应用程序有3个表，分别是OrderHd，OrderLine和OrderSerial.
每1个订单文档的OrderSerial记录约1,000-5,000条记录

//我的模拟保存过程
string mstrConnStr =数据源= serverzx;初始目录=测试；用户ID = sa;密码= x2y2;";
System.Data.SqlClient.SqlConnection conn =新的SqlConnection(mstrConnStr);
System.Data.SqlClient.SqlCommand cmd1 =新的SqlCommand(插入到OrderHd(OrderID，Code，Name)值(@ Para0，@ Para1，@ Para2)"，conn);
System.Data.SqlClient.SqlCommand cmd2 =新的SqlCommand(插入到OrderLine(OrderLineID，OrderID，Detail)值(@ Para0，@ Para1，@ Para2)"，conn);

System.Data.SqlClient.SqlTransaction tr = null;

尝试
{
conn.Open();

cmd1.Parameters.Clear();

tr = conn.BeginTransaction(IsolationLevel.ReadUncommitted);

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

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

cmd1.Parameters.Add("@ Para0"，strHead);
cmd1.Parameters.Add("@ Para1"，strCode);
cmd1.Parameters.Add("@ Para2"，名称" + strCode);
cmd1.ExecuteNonQuery();

对于(int i = 0; i< 5000; i ++)
{
字符串strItem = Guid.NewGuid().ToString();
cmd2.Parameters.Clear();
cmd2.Parameters.Add("@ Para0"，strItem);
cmd2.Parameters.Add("@ Para1"，strHead);
cmd2.Parameters.Add("@ Para2"，详细信息" + i.ToString());
cmd2.ExecuteNonQuery();
System.Threading.Thread.Sleep(10);
}
tr.Commit();
}
捕获(异常)
{
tr.Rollback();
MessageBox.Show(ex.Message);
}
终于
{
conn.Close();
}

解决方案

我认为您看到的是表在一行一行的INSERT迭代中被锁定.

您需要使此操作更快，为什么要使用Thread.Sleep?

在SqlBulkCopy中查找5000个插入或其他大容量复制选项.一个想法可能是用您需要的数据构建一个或多个临时表，然后根据选择进行插入.

在可以使用日期数据类型的地方，请使用它-代码"列应为日期时间类型.

哦，抱歉.我使用Thread.Sleep来表示一些更新过程时间.
然后尝试优化保存过程.
每个记录大约需要0.05-0.1毫秒(每个文档大约需要3-5分钟)

让SQLServer担心进程/更新时间.

检查索引以查看它们是否可以优化-表上的索引是插入和更新速度的关键部分

如果实际上要创建一个父级，然后再创建5000个子级，则最终可能会产生大量事务日志，从而导致磁盘空间(和日志)空间均出现问题.

如果可以避免逐行插入(查找未记录的批量插入操作).

How can I fix Transaction with Page Lock Ploblem ?

My Application has ploblem while one of client save Order Document.
Another client cannot select data from Order table until saving process is complete.
I develop C# window application with Microsoft SQL Server 2005 sp3.
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 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();
}

解决方案

I think what you are seeing is that the table is being locked during the one by one row INSERT iterations.

You need to make this operation faster, why did you use the Thread.Sleep ?

Look at SqlBulkCopy for the 5000 inserts or other bulk copy options. One idea might be building a temp table or tables with the data you need and THEN doing the insert based on a select.

And where you can use a date datatype please use it - column "Code" should be a datetime type. This won''t speed anything up, just good practice.

Oh Sorry.I use Thread.Sleep for represent some update process time.
and I try to optimize my saving process.
it about 0.05-0.1 ms per record (about 3-5 minute per Document)

Let SQLServer worry about process/update time.

Check your indexes to see if they can be optimised - indexes on the tables are a critical part of the speed of inserts and updates

If you actually are creating one parent and then 5000 children you may end up with a large transaction log causing both disk space (and log) space problems.

If you can avoid inserting row by row (look up non-logged bulk insert operations) do that.

这篇关于如何使用“页面锁定问题"修复“交易"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！