Question

We are facing a situation in one of our production server. We have a particular Store procedure which perform an Insert operation on one of the biggest tables in the DB (It has more than a few million rows). This table is the most busy table in the DB and has many operations dependent upon it.

Recently we have been facing an issue on one particular production server.

We execute a Insert SP along with some other update SPs in one single transaction, and we are facing Long running transaction issue for the Insert SP quite regularly. When ever we get this issue we find a typical behaviour in the data that gets inserted into the table. The datetime column value is getting inserted as null . It happens some times for all rows and some time for a few rows. The datetime value is passed from the application. But the other update operations that are performed before and after the insert operation work well.

我们的测试环境(生产服务器中无)有色谱仪,但发现日值每时都得到适当传递。

Also when we face the issue in production, we observe that:

@@trancount is equal to 0 but the DBCC OPENTRAN displays the particular open transaction.
The Last Wait type has value NETWORKIO .
The Waittype is 0x0000 .
The Status is sleeping .
The ISOLATION LEVEL is READ UNCOMMITTED.

So our concern is

Why the datetime is getting inserted as NULL in this particular situation only?
How to avoid this situation and also the long running transaction?
What may be the cause for such situation to arise in one particular server?

Thanks in advance for the help,

Abhijit

Answer 1

我认为,需要在此同时处理几个问题。首先,最重要的是,我强烈建议你审视这一交易,看看你能够做些什么来减少它给你的系统带来的负担。能否将其分成多个较小的交易? 增加更好的指数是否有助于? 收集一组数据并将其列入一个温表是否会减少从主要表格中选用的计算机数量? 这里的问题单可以同时提出。

其次,看看正在打断交易的“申请”。日期是否通过? 如果是,它如何这样做? 如果它刚刚通过<代码>GetDate(),那么它就会让Serk从事这项工作。然而,如果申请按日期价值计算,我将确保这一日期价值是ALWAYS。如果是的话,看上表可以确保适当确定日期的格式。例如,如果你的申请以<代码>14-05-2011的欧洲日期格式通过,那么,如果您的申请预计从14点起算为月。

第三,通过您的表格看看看你是否设立了任何触发点。如果是,非常仔细地审视每个触发点。触发因素很可能在你的交易中造成冲突。也许你是书面数据,而你的触发器正在回头并更新数据(或正在评估日期和说其无效——见上文)。

Fourth, check the data before it leaves the transaction. Read it when it comes in and read it after the INSERT has been performed. Maybe there is a client operation going on after the transaction is complete that wipes out the date value.

最后,你需要看看你的测试环境。如果这项工作在试验中进行,但生产中却有 t,这两种系统之间就存在某种差异。这是直接或间接的原因。是否与硬件相关(坏的RAM?)或者在环境中可能有所不同(证据、客户试图对数据采取行动等)

Beyond this, here is a link to a forum with some other potential answers for you:

http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/1063b65df1f97492/8649bee2002646a2

Answer 2

Sounds like you ve got a mess of code. Isolating your inserts and updates, etc. into smaller transactions would be advisable.

To test where your hang-up is happening, one thing you can try is simply to add some milestones into your procedure in the form of print( Made it this far... ). Then execute the procedure from SSMS and check the messages window. It s an ugly way to debug, but if you re not wanting to use smaller transactions or trap @@error, etc., it might be your best choice.

Answer 3

"We execute a Insert SP along with some other update SPs in one single transaction"

I d recommend decoupling your insert and updates. If you know you need to make some updates post-insert, triggers for the updates may be the right answer.

我也建议你介绍锁定/封锁的情况。我之所以这样说,是因为从你所说的话来看,你很可能在你根本交易中不止一次地接过了,其依据是可能把所有这些活动捆绑在一起的理由。

友情链接