由于我有一个带有 DataSet QueryDB(string spName, DBInputParams inputParams) 方法的“DB util”类，我将其用于对数据库的所有调用，因此我想重用此方法以支持已处理的通话。
因此，最后我将在 SqlTransaction 中拥有一个 SqlDataAdapter.Fill。这是一个不好的做法吗？因为我很少看到事务中使用 DataAdapter.Fill，而更常见的是 ExecuteReader()。有什么陷阱吗？
Edit1：问题是，在我的事务中经常需要检索一些数据（例如自动ID）...这就是为什么我想将其作为数据集获取。
编辑2：奇怪的是，当我在两个不同进程的 for 循环（10000）中使用这种方法时，我得到“事务（进程 ID 55）在与另一个进程的锁定资源上死锁，并已被选择为死锁受害者重新运行事务。” 。这是正确的行为吗？
Edit3：（Edit2的答案）我使用的是IDENT_CURRENT('XTable')，这是错误的根源。当我回到SCOPE_IDENTITY()后，一切都解决了。
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.
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: 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: (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.
发布评论
评论（2）
这不是一个坏习惯。要记住的一件事是，所有语句都将使用隐式事务，它们将在语句结束时自动提交。也就是说，SELECT（如 Fill 使用的 SELECT 中）将总是使用事务，问题是它是否必须自行启动它还是将使用现有的事务。
隐式事务与显式事务中 SELECT 获取的锁的数量、类型和持续时间之间有什么区别吗？在默认事务模型（READ COMMITTED 隔离）NO 下，没有。行为是相同且无法区分的。在其他隔离级别（可重复读、可序列化）下存在差异，但这是实现所需的更高隔离级别所必需的差异，并且使用显式事务是实现此所需隔离的唯一方法水平，必要时。
此外，如果 SELECT 必须读取待处理（尚未提交）的事务的效果，如您的示例中所示（读回生成的 ID），那么没有其他方法。 SELECT 必须是生成 ID 的事务的一部分，否则它将无法看到那些未提交的 ID！
但需要注意的是。我相信您拥有一个出色的工具，可以使所有事务处理变得更加容易：System.Transactions。所有 ADO.Net 代码都是系统事务感知的，如果您简单地声明一个
TransactionScope。也就是说，如果函数 Foo 声明了一个TransactionScope，然后调用函数 Bar，如果 Bar 执行任何 ADO.Net 操作，它将自动成为 Foo 中声明的事务的一部分，甚至如果 Bar 没有显式执行任何操作。TransactionScope挂接到线程上下文中，Bar 调用的所有 ADO.Net 调用都会自动检查此上下文并使用它。请注意，我的真正意思是任何 ADO.Net 调用，包括 Oracle 提供程序的调用。唉，虽然有一个警告： 使用new TransactionScope() 被认为是有害的：TransactionScope的默认构造函数将创建一个可序列化的事务，这是多余的。您必须使用采用TransactionOptions< 的构造函数/code>对象并将行为更改为 ReadCommissed。TransactionScope的第二个问题是，您必须非常小心地管理连接：如果您在一个作用域下打开多个连接，那么它们将被注册到分布式事务中，这很慢并且需要MSDTC 需要配置，并导致各种难以调试的错误。但总的来说，我认为使用 TransactionScope 的好处超过了问题，并且生成的代码总是比显式传递 IDbTransaction 更优雅。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.
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.
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!
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 aTransactionScopeand 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. TheTransactionScopeis 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 ofTransactionScopewill create a serializable transaction, which is overkill. You have to use the constructor that takes aTransactionOptionsobject and change the behavior to ReadCommitted. A second gotcha withTransactionScopeis 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 usingTransactionScopeoutweight the problems, and the resulted code is always more elegant than passing aroundIDbTransactionexplicitly.这是一种不好的做法，因为当事务打开时，您进行更改的记录/页面/表在事务期间被锁定。填充只会使整个过程将这些资源锁定更长时间。根据您的 SQL 设置，这可能会阻止对这些资源的其他访问。
也就是说，如果有必要，那就有必要，只要意识到这样做的惩罚就可以了。
It is a bad practice because while the transaction is open, records/pages/tables that you make changes to are locked for the duration of the transaction. The fill just makes the whole process keep those resources locked longer. Depending on your sql settings, this could block other accesses to those resources.
That said, if it is necessary, it is necessary, just realize the penalty for doing it.