.NET TransactionScopes 和 SQL 2005 轻型事务管理器

本文介绍了.NET TransactionScopes 和 SQL 2005 轻型事务管理器 - 多个连接相同的 SPID?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

限时送ChatGPT账号..

当多个连接打开到同一个数据库时，有人可以使用 Microsoft 数据访问应用程序块 (DAAB)?

Can someone shed light on what is happening behind the scenes with the SQL Lightweight transaction manager when multiple connections are opened to the same DB, using the Microsoft Data Access Application Block (DAAB)?

通过以下代码，我们验证了在打开到同一数据库的多个连接"时确实不需要 MSDTC.

With the below code, we verified that MSDTC is indeed not required when opening 'multiple connections' to the same database.

这是我测试的第一个场景:(其中 Txn1 和 Txn2 使用 EntLib 4.1 打开到同一个 DB 的连接并调用不同的 SPROCS)

This was the first scenario I tested: (where Txn1 and Txn2 use EntLib 4.1 to open a connection to the same DB and call different SPROCS)

using (var ts = new TransactionScope(TransactionScopeOption.Required))
{
    DAL1.Txn1();
    DAL2.Txn2();
    ts.Complete();
}

从探查器跟踪此发现，Txn1 和 Txn2 使用了相同的连接 SPID.在调用 Txn1() 之后，Sql SPID 将被释放回池中，并且 Txn2() 能够重新使用它.

Tracing this from profiler revealed that the same connection SPID was used for Txn1 and Txn2. After Txn1() was called, the Sql SPID would have been released back into the pool and Txn2() was able to re-use it.

然而，当重复这个实验并且这次保持连接打开时:

However, when repeating this experiment and this time holding the connections open:

using (var ts = new TransactionScope(TransactionScopeOption.Required))
{
    Database db1 = DatabaseFactory.CreateDatabase("db1");
    DAL1.Txn1OnCon(db1);
    Database db2 = DatabaseFactory.CreateDatabase("db1");
    DAL2.Txn2OnCon(db2);
    ts.Complete();
}

从 Profiler 查看此内容表明 2 个事务仍在使用相同的 SPID.我期待 TransactionScope 升级到 DTC，因为应该需要分布式事务来控制 2 个并发连接.我错过了什么?

Viewing this from Profiler indicated that the 2 transactions were STILL using the same SPID. I was expecting the TransactionScope to have escalated to DTC as a distributed transaction should be required to control 2 concurrent connections. What have I missed?

推荐答案

引用自 MSDN http://msdn.microsoft/en-us/library/8xx3tyca(VS.80).aspx

连接池减少了数量新连接需要的次数被打开.池子维护物理连接的所有权.它通过保持连接来管理连接活动的一组活动连接每个给定的连接配置.每当用户调用 Open连接，池化器查看是否有一个可用的连接游泳池.如果池连接是可用，它将它返回给调用者而不是打开一个新的连接.当应用程序调用 Close on连接，池化器返回它到活跃的集合集合连接而不是实际关闭它.一旦连接成功回到游泳池，准备好在下一次 Open 调用中重复使用.

Connection pooling reduces the number of times that new connections need to be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks to see if there is an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of actually closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

仅仅因为在事务中使用了连接并不意味着它不能用于下一次调用.我发现如果连接字符串稍有不同，例如主机名的大写，那么您将获得与数据库的新物理连接.

Just because a connection was used in a transaction doesn't mean it cannot be available for the next call. I found that If the connection string varied by the slightest thing, such as capitalization of a hostname, then you'd get a new physical connection to the db.

这篇关于.NET TransactionScopes 和 SQL 2005 轻型事务管理器 - 多个连接相同的 SPID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！