Transactions and conn pooling

  • Thread starter Thread starter Geoffp
  • Start date Start date
G

Geoffp

Apparently conn pooling maintains separate pools for the
same connstring, depending on whther the instance has a
transaction opened on it. The actual connection supplied
depends on the "transaction context"

-Does this only work for COM+ transactions? There seems to
be no way of opening a new conn under an existing
sqlTransaction object?

-to allow a trans to span several objects and methods in
the middle tier, I might open a global trans object and a
global conn, and use them for methods I want to be part of
the transaction(disposing when the trans is complete). All
this would happen within one high-level method.
Has this any downside, other than maintaining state and an
open conn for a while longer?
 
Hello Geoffp,

Thanks for posting in the group.

ADO.NET connection pooling also supports transactions. Connections are
drawn from the pool and assigned based on transaction context. The context
of the requesting thread and the assigned connection must match. Therefore,
each connection pool is actually subdivided into connections with no
transaction context associated with them, and into N subdivisions that each
contain connections with a particular transaction context.

When a connection is closed, it is released back into the pool and into the
appropriate subdivision based on its transaction context. Therefore, you
can close the connection without generating an error, even though a
distributed transaction is still pending. This allows you to commit or
abort the distributed transaction at a later time.

For an example, for SQL Server data provider, to get current transaction
contect, we could call ContextUtil. You can also enlist in an existing
distributed transaction using Connection.EnlistDistributedTransaction.

For details, please refer to MSDN topic:
"Performing a Transaction Using ADO.NET"
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconperformingtransact
ions.asp?frame=true
"Enlisting in a Distributed Transaction"
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconenlistingindistrib
utedtransaction.asp?frame=true

For the second question, I think it is fine. You could refer to the second
article of the above to see how to achieve it.

Does that answer your question?

Best regards,
Yanhong Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top