SqlTransaction.BeginTransaction() within TransactionScope bad?

[haroldsphsu]

Hi all,

I have some existing code that updates the database by creating a
SqlCommand and SqlTransaction:

public class GroupMapper
{
public static void Update(Group g)
{
// Gets SqlConnection, creates SqlCommand and calls
SqlConnection.BeginTransaction().
}
}

That works fine what I'm only updating one object at a time. But now
I need to update a bunch of objects (of different types) in a single
transaction, so I wrap the Update() calls in a TransactionScope
(within the TransactionScope, there might be calls to read the
database that doesn't require a transaction):

public void foo()
{
using (TransactionScope scope = new TransactionScope())
{
GroupMapper.Update(group);
FooMapper.Update(foo);
....
scope.Complete();
}
}

But I read in this article (http://msdn2.microsoft.com/en-us/library/
ms973865(d=printer).aspx) that:

"When using System.Transactions, applications should not directly
utilize transactional programming interfaces on resource managers--for
example the T-SQL BEGIN TRANSACTION or COMMIT TRANSACTION verbs, or
the MessageQueueTransaction() object in System.Messaging namespace,
when dealing with MSMQ. Those mechanisms would bypass the distributed
transaction management handled by System.Transactions, and combining
the use of System.Transactions with these resource manager "internal"
transactions will lead to inconsistent results. As a rule, use
System.Transactions in the general case, and use resource manager
internal transactions only in specific cases where you are certain the
transaction will not span multiple resources, and will not be composed
into a larger transaction. Never mix the two."

My transaction will not span multiple resources, so I should be ok?

The reason I'm asking this is that when running my unit tests,
occasionally, I see the following exception message:

"ExecuteReader requires the command to have a transaction when the
connection assigned to the command is in a pending local transaction.
The Transaction property of the command has not been initialized."

Which makes me wonder if I should delete all the code that creates
SqlTransaction and wrap each database update method in its own
TransactionScope.

Any ideas will be greatly appreciated!

Thanks,
Harold

 

[Mary Chipman [MSFT]]

If you are performing multiple actions within the scope of a single
connection, you can use the following pattern:

TransactionScope {
Open Connection
Do action 1
Do action 2
Close Connection
}

You don't need SqlTransaction objects and wrapping each update in its
own TransactionScope misses the point of using them to begin with.
Here's a link for more information:
http://msdn2.microsoft.com/en-us/library/ms172152.aspx

--Mary

 

[haroldsphsu]

Hi Mary,

Thanks for the reply. The pattern you have doesn't work for me
because in my case, "action 1" and "action 2" can be called
individually, and each of them need to be transactional by
themselves. In my example, I will call GroupMapper.Update(group) and
the update has to be transactional. I also have some other code that
will call both GroupMapper.Update(group) and FooMapper.Update(foo).
Sure, they can be done in a single connection, but again, because the
Update() methods can be called individually in a different context,
they need to open their own connection.

I'm just not sure if wrapping the Update() calls within a
TransactionScope while each one create its own SqlTransaction (legacy
code...so they can be called individually) is the right way to do
things, or I should take away the SqlTransaction, create a new
TransactionScope within each Update(), so it will still be
transactional when called individually, and become a nested
TransactionScope when called together within another TransactionScope.

Thanks,
Harold

 

[Mary Chipman [MSFT]]

If your method call only performs a single, atomic operation, then
there is no need to wrap it in any kind of transaction. You only need
explicit transactions when you want multiple operations to be
processed as a single unit of work. I hope I am understanding you
correctly

-Mary

 

[haroldsphsu]

"action 1" (or in my example, GroupMapper.Update()), execute updates
to multiple tables, so it is not a single atomic operation. =)

 

[Mary Chipman [MSFT]]

I was assuming you were asking about the other operations, not
GroupMapper.Update -- obviously, that needs to be in a transaction.

It's hard to give decent advice here because it looks like a
complicated scenario that can't easliy be put into a newsgroup post.
The one thing i would advise is to try to keep your transactions as
short as possible, involving the fewest number of resources at a time.

All of these client APIs are wrapping SQL Server transactions, so at
the end of the day you're going to get the best bang for your buck by
wrapping them all in stored procedures that you call from your client
code.

-Mary

"action 1" (or in my example, GroupMapper.Update()), execute updates
to multiple tables, so it is not a single atomic operation. =)

If your method call only performs a single, atomic operation, then
there is no need to wrap it in any kind of transaction. You only need
explicit transactions when you want multiple operations to be
processed as a single unit of work. I hope I am understanding you
correctly

-Mary

Hi Mary,

Thanks for the reply. The pattern you have doesn't work for me
because in my case, "action 1" and "action 2" can be called
individually, and each of them need to be transactional by
themselves. In my example, I will call GroupMapper.Update(group) and
the update has to be transactional. I also have some other code that
will call both GroupMapper.Update(group) and FooMapper.Update(foo).
Sure, they can be done in a single connection, but again, because the
Update() methods can be called individually in a different context,
they need to open their own connection.

I'm just not sure if wrapping the Update() calls within a
TransactionScope while each one create its own SqlTransaction (legacy
code...so they can be called individually) is the right way to do
things, or I should take away the SqlTransaction, create a new
TransactionScope within each Update(), so it will still be
transactional when called individually, and become a nested
TransactionScope when called together within another TransactionScope.

Thanks,
Harold

On Nov 23, 8:39 am, "Mary Chipman [MSFT]" <[email protected]>
wrote:
If you are performing multiple actions within the scope of a single
connection, you can use the following pattern:

TransactionScope {
Open Connection
Do action 1
Do action 2
Close Connection

You don't need SqlTransaction objects and wrapping each update in its
own TransactionScope misses the point of using them to begin with.
Here's a link for more information:http://msdn2.microsoft.com/en-us/library/ms172152.aspx

On Wed, 21 Nov 2007 16:57:54 -0800 (PST), (e-mail address removed) wrote:
Hi all,

I have some existing code that updates the database by creating a
SqlCommand and SqlTransaction:

public class GroupMapper
{
public static void Update(Group g)
{
// Gets SqlConnection, creates SqlCommand and calls
SqlConnection.BeginTransaction().
}
}

That works fine what I'm only updating one object at a time. But now
I need to update a bunch of objects (of different types) in a single
transaction, so I wrap the Update() calls in a TransactionScope
(within the TransactionScope, there might be calls to read the
database that doesn't require a transaction):

public void foo()
{
using (TransactionScope scope = new TransactionScope())
{
GroupMapper.Update(group);
FooMapper.Update(foo);
....
scope.Complete();
}
}

But I read in this article (http://msdn2.microsoft.com/en-us/library/
ms973865(d=printer).aspx) that:

"When using System.Transactions, applications should not directly
utilize transactional programming interfaces on resource managers--for
example the T-SQL BEGIN TRANSACTION or COMMIT TRANSACTION verbs, or
the MessageQueueTransaction() object in System.Messaging namespace,
when dealing with MSMQ. Those mechanisms would bypass the distributed
transaction management handled by System.Transactions, and combining
the use of System.Transactions with these resource manager "internal"
transactions will lead to inconsistent results. As a rule, use
System.Transactions in the general case, and use resource manager
internal transactions only in specific cases where you are certain the
transaction will not span multiple resources, and will not be composed
into a larger transaction. Never mix the two."

My transaction will not span multiple resources, so I should be ok?

The reason I'm asking this is that when running my unit tests,
occasionally, I see the following exception message:

"ExecuteReader requires the command to have a transaction when the
connection assigned to the command is in a pending local transaction.
The Transaction property of the command has not been initialized."

Which makes me wonder if I should delete all the code that creates
SqlTransaction and wrap each database update method in its own
TransactionScope.

Any ideas will be greatly appreciated!

Thanks,
Harold

 

[haroldsphsu]

Thanks for the help!