SqlDataAdapter.Fill（）一个的SqlTransaction内 - 这是一个不好的做法？ [英] SqlDataAdapter.Fill() within a SqlTransaction - is this a bad practice?

问题描述

因为我有一个DB util的类与的DataSet QueryDB（字符串spName，DBInputParams inputParams）方法，我用我所有对数据库的调用，我会想以支持事务处理呼叫重用此方法。

Since I have a "DB util" class with a DataSet QueryDB(string spName, DBInputParams inputParams) method which I use for all my calls to the database, I would like to reuse this method in order to support transacted calls.

所以，最后我将有一个的SqlTransaction内SqlDataAdapter.Fill。这会不会是一个不好的做法？因为我很少看到DataAdapter.Fill方法的使用的事务中，更常见的ExecuteReader（）。有没有什么收获？

So, at the end I will have a SqlDataAdapter.Fill within a SqlTransaction. Will this be a bad practice? Because rarely I see usage of DataAdapter.Fill within a transaction and more often ExecuteReader(). Is there any catch?

EDIT1：的事情是，在我的交易往往需要也找回了一些数据（如自动编号）...这就是为什么我希望把它作为数据集

The thing is that inside my transaction is often needed to retrieve also some data (e.g auto-IDs)... that's why I would like to get it as DataSet.

EDIT2：奇怪的是，当我用这个方法在2个不同的流程环（10000），我得到事务（进程ID 55）已被死锁的锁资源与另一个进程，并有被选作死锁牺牲品。请重新运行该交易。 。这是正确的行为吗？

Strange is when I use this approach in a for loop (10000) from 2 different processes, I get "Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." . Is this the right behaviour?

EDIT3：（答案EDIT2）我用 IDENT_CURRENT（'XTable'）这是错误的来源。之后，我又回到了 SCOPE_IDENTITY（），一切都已经解决了。

(answer for Edit2) I was using IDENT_CURRENT('XTable') which was the source of the error. After I went back to SCOPE_IDENTITY(), everything has been solved.

推荐答案

这是不会一种不好的做法。有一点要记住的是，所有的语句将使用隐式事务的语句结束时，他们将自动提交。这是一个SELECT（如使用的填充SELECT）会的总是的使用事务，问题是，它是否要启动它本身，否则将使用现有之一。

It is not a bad practice. One thing to remember is that all statements will use an implicit transaction that they will automatically commit when the statement ends. That is a SELECT (as in the SELECT used by Fill) will always use a transaction, the question is whether it will have to start it on itself or it will use the existing one.

是否有一个隐含的收购交易由SELECT与明确的事务的数量，类型和锁持续时间之间有什么区别？在默认的交易模式（读已提交隔离）否，是没有的。的行为是相同的，并且难以区分。在其他隔离级别（重复读，序列化）是有区别的，但是这是对所需更高的隔离水平发生，并且利用显式事务必要区别是仅的方式实现这一目标所需的隔离平，当必要

Is there any difference between the number, type and duration of locks acquired by a SELECT in an implicit transaction vs. an explicit transaction? Under the default transaction model (READ COMMITTED isolation) NO, there is none. The behavior is identical and indistinguishable. Under other isolation levels (repeatable read, serializable) there is a difference, but that is the necessary difference for the desired higher isolation level to occur and using an explicit transaction is the only way to achieve this desired isolation level, when necessary.

在另外如果SELECT具有读取事务的待决（尚未提交）的影响，如在实施例（读回生成的ID），那么没有其他方式的。在选择必须是生成的ID交易的一部分，否则将无法看到那些未提交的ID！

In addition if the SELECT has to read the effects of a transaction that is pending (not yet committed), as in your example (read back the generated IDs) then there is no other way. The SELECT must be part of the transaction that generated the IDs, otherwise it will not be able to see those uncommitted IDs!

有一个值得注意的问题虽然。相信您在您的处置一个很好的工具，可以使这一切的事务处理变得容易多了的 System.Transactions的的。所有ADO.Net code是系统事务感知，并会自动注册的任何连接和命令进入挂起的交易，如果你只是声明的 的TransactionScope 。也就是说，如果函数foo声明了一个的TransactionScope ，然后调用函数吧，如果酒吧做的任意的ADO.Net化经营，它会自动成为团队的一分子交易宣布美孚，即使酒吧做的没有的明确。该的TransactionScope 挂钩到线程上下文和所有ADO.Net呼叫称为栏将自动检查此背景下，并使用它。请注意，我真正的意思的任意的ADO.Net通话，包括Oracle提供的。唉，虽然有一个警告：<一href="http://blogs.msdn.com/b/dbrowne/archive/2010/06/03/using-new-transactionscope-considered-harmful.aspx"相对=nofollow>使用新的TransactionScope（）是有害的：的TransactionScope 将创建一个串行化事务，这是矫枉过正。你必须使用，需要一个 TransactionOptions 对象，改变行为READCOMMITTED。第二个疑难杂症与的TransactionScope 是，你有你如何管理连接要非常小心：如果你打开​​下一个范围，那么他们将参加在分布式事务多个连接，这是缓慢的，需要MSDTC进行配置，并且导致所有排序难以调试错误。但总体来说，我跌，使用的好处的TransactionScope outweight的问题，并导致code总是比身边掠过 IDbTransaction 明确。

A note of caution though. I believe you have at your disposal a great tool that can make all this transaction handling much easier: the System.Transactions. All ADO.Net code is system transaction aware and will automatically enroll any connection and command into the pending transaction, if you simply declare a TransactionScope. That is if function Foo declares a TransactionScope and then calls function Bar, if Bar does any ADO.Net operatio, it will automatically be part of the transaction declared in Foo, even if Bar does nothing explicitly. The TransactionScope is hooked into the thread context and all ADO.Net call called by Bar will check for this context automatically, and use it. Note that I really mean any ADO.Net call, including Oracle provider ones. Alas though there is a warning: using new TransactionScope() Considered Harmful: the default constructor of TransactionScope will create a serializable transaction, which is overkill. You have to use the constructor that takes a TransactionOptions object and change the behavior to ReadCommitted. A second gotcha with TransactionScope is that you have to be very careful how you manage connections: if you open more than one connection under a scope then they will be enrolled in a distributed transaction, which is slow and requires MSDTC to be configured, and leads to all sort of hard to debug errors. But overall I fell that the benefits of using TransactionScope outweight the problems, and the resulted code is always more elegant than passing around IDbTransaction explicitly.

这篇关于SqlDataAdapter.Fill（）一个的SqlTransaction内 - 这是一个不好的做法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！