PC Review


Reply
Thread Tools Rate Thread

Concurrent Connections / Shared Transaction

 
 
Shawn B.
Guest
Posts: n/a
 
      12th Mar 2005
Greetings,

I have two SqlCommands objects that each need to execute concurrently but I
want them both to be a part of the same transaction.

What I'm doing is created one SqlCommand and Beginning a transaction.

I have another SqlCommand that is being executed from within an Asynchronous
delegate that needs to participate in the same transaction. The problem is
that I get the infamous exception stating that the connection is already in
use.

COM+ is not an answer here.

What I want to know is if there is a way to make this work correctly using
only ADO.NET?


Thanks,
Shawn


 
Reply With Quote
 
 
 
 
Dumitru Sbenghe
Guest
Posts: n/a
 
      12th Mar 2005
Given the current status of ADO.NET (version 1.1), you can not do what you
want. Because the SqlClient provider permit only one command running on a
given connection at a time, the only solution will be a distributed
transaction coordinator + 2 connections with 2 commands executed
concurrently aka ES/COM+ in .NET . or wait until ADO.NET 2.0 will be
released , version which will permit this behavior.



Dumitru


"Shawn B." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Greetings,
>
> I have two SqlCommands objects that each need to execute concurrently but
> I
> want them both to be a part of the same transaction.
>
> What I'm doing is created one SqlCommand and Beginning a transaction.
>
> I have another SqlCommand that is being executed from within an
> Asynchronous
> delegate that needs to participate in the same transaction. The problem
> is
> that I get the infamous exception stating that the connection is already
> in
> use.
>
> COM+ is not an answer here.
>
> What I want to know is if there is a way to make this work correctly using
> only ADO.NET?
>
>
> Thanks,
> Shawn
>
>



 
Reply With Quote
 
 
 
 
Angel Saenz-Badillos[MS]
Guest
Posts: n/a
 
      14th Mar 2005
As long as both connections are to the same instance of Sql Server you can
use sp_bindsession to get this behavior without having to use distributed
transactions.
http://msdn.microsoft.com/library/de...ba-bz_9ini.asp

Hope this helps.
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/




"Dumitru Sbenghe" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Given the current status of ADO.NET (version 1.1), you can not do what you
> want. Because the SqlClient provider permit only one command running on a
> given connection at a time, the only solution will be a distributed
> transaction coordinator + 2 connections with 2 commands executed
> concurrently aka ES/COM+ in .NET . or wait until ADO.NET 2.0 will be
> released , version which will permit this behavior.
>
>
>
> Dumitru
>
>
> "Shawn B." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Greetings,
> >
> > I have two SqlCommands objects that each need to execute concurrently

but
> > I
> > want them both to be a part of the same transaction.
> >
> > What I'm doing is created one SqlCommand and Beginning a transaction.
> >
> > I have another SqlCommand that is being executed from within an
> > Asynchronous
> > delegate that needs to participate in the same transaction. The problem
> > is
> > that I get the infamous exception stating that the connection is already
> > in
> > use.
> >
> > COM+ is not an answer here.
> >
> > What I want to know is if there is a way to make this work correctly

using
> > only ADO.NET?
> >
> >
> > Thanks,
> > Shawn
> >
> >

>
>



 
Reply With Quote
 
Shawn B.
Guest
Posts: n/a
 
      14th Mar 2005
Angel, you are amazing. I read your blog frequently, this is certainly in
your area of expertise. Thanks for the help.


Thanks,
Shawn.



"Angel Saenz-Badillos[MS]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> As long as both connections are to the same instance of Sql Server you can
> use sp_bindsession to get this behavior without having to use distributed
> transactions.
>

http://msdn.microsoft.com/library/de...ba-bz_9ini.asp
>
> Hope this helps.
> --
> Angel Saenz-Badillos [MS] Managed Providers
> This posting is provided "AS IS", with no warranties, and confers no
> rights.Please do not send email directly to this alias.
> This alias is for newsgroup purposes only.
> I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/
>
>
>
>
> "Dumitru Sbenghe" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Given the current status of ADO.NET (version 1.1), you can not do what

you
> > want. Because the SqlClient provider permit only one command running on

a
> > given connection at a time, the only solution will be a distributed
> > transaction coordinator + 2 connections with 2 commands executed
> > concurrently aka ES/COM+ in .NET . or wait until ADO.NET 2.0 will be
> > released , version which will permit this behavior.
> >
> >
> >
> > Dumitru
> >
> >
> > "Shawn B." <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Greetings,
> > >
> > > I have two SqlCommands objects that each need to execute concurrently

> but
> > > I
> > > want them both to be a part of the same transaction.
> > >
> > > What I'm doing is created one SqlCommand and Beginning a transaction.
> > >
> > > I have another SqlCommand that is being executed from within an
> > > Asynchronous
> > > delegate that needs to participate in the same transaction. The

problem
> > > is
> > > that I get the infamous exception stating that the connection is

already
> > > in
> > > use.
> > >
> > > COM+ is not an answer here.
> > >
> > > What I want to know is if there is a way to make this work correctly

> using
> > > only ADO.NET?
> > >
> > >
> > > Thanks,
> > > Shawn
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
Pablo Castro [MS]
Guest
Posts: n/a
 
      15th Mar 2005
Actually, bound sessions allow you to share the transaction space among two
connections but doesn't give you concurrent access to it. If you try to
execute a batch on a connection while the other is actively doing something
within the transaction the server will return an error saying that the
transaction context is in use.

COM+ is the only solution I know of for this scenario. Even with COM+/DTC
you'll see some serialization around the transaction. In ADO.NET 2.0 + SQL
Server 2005 you can mix MARS plus asynchronous command execution to submit
two requests concurrently within the same transaction; however, note that in
that case you won't get parallelism in the server; multiple MARS sessions
within a connection are interleaved, not executed in parallel.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.


"Angel Saenz-Badillos[MS]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> As long as both connections are to the same instance of Sql Server you can
> use sp_bindsession to get this behavior without having to use distributed
> transactions.
> http://msdn.microsoft.com/library/de...ba-bz_9ini.asp
>
> Hope this helps.
> --
> Angel Saenz-Badillos [MS] Managed Providers
> This posting is provided "AS IS", with no warranties, and confers no
> rights.Please do not send email directly to this alias.
> This alias is for newsgroup purposes only.
> I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/
>
>
>
>
> "Dumitru Sbenghe" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Given the current status of ADO.NET (version 1.1), you can not do what
>> you
>> want. Because the SqlClient provider permit only one command running on a
>> given connection at a time, the only solution will be a distributed
>> transaction coordinator + 2 connections with 2 commands executed
>> concurrently aka ES/COM+ in .NET . or wait until ADO.NET 2.0 will be
>> released , version which will permit this behavior.
>>
>>
>>
>> Dumitru
>>
>>
>> "Shawn B." <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Greetings,
>> >
>> > I have two SqlCommands objects that each need to execute concurrently

> but
>> > I
>> > want them both to be a part of the same transaction.
>> >
>> > What I'm doing is created one SqlCommand and Beginning a transaction.
>> >
>> > I have another SqlCommand that is being executed from within an
>> > Asynchronous
>> > delegate that needs to participate in the same transaction. The
>> > problem
>> > is
>> > that I get the infamous exception stating that the connection is
>> > already
>> > in
>> > use.
>> >
>> > COM+ is not an answer here.
>> >
>> > What I want to know is if there is a way to make this work correctly

> using
>> > only ADO.NET?
>> >
>> >
>> > Thanks,
>> > Shawn
>> >
>> >

>>
>>

>
>



 
Reply With Quote
 
Angel Saenz-Badillos[MS]
Guest
Posts: n/a
 
      16th Mar 2005
That's why Pablo gets paid the big bucks Seriously if you want the best
info always look for his posts and articles.

He is completelly correct by the way, you will not be able to use this with
concurrently operating connections, sorry.
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/




"Pablo Castro [MS]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Actually, bound sessions allow you to share the transaction space among

two
> connections but doesn't give you concurrent access to it. If you try to
> execute a batch on a connection while the other is actively doing

something
> within the transaction the server will return an error saying that the
> transaction context is in use.
>
> COM+ is the only solution I know of for this scenario. Even with COM+/DTC
> you'll see some serialization around the transaction. In ADO.NET 2.0 + SQL
> Server 2005 you can mix MARS plus asynchronous command execution to submit
> two requests concurrently within the same transaction; however, note that

in
> that case you won't get parallelism in the server; multiple MARS sessions
> within a connection are interleaved, not executed in parallel.
>
> --
> Pablo Castro
> Program Manager - ADO.NET Team
> Microsoft Corp.
>
> This posting is provided "AS IS" with no warranties, and confers no

rights.
>
>
> "Angel Saenz-Badillos[MS]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > As long as both connections are to the same instance of Sql Server you

can
> > use sp_bindsession to get this behavior without having to use

distributed
> > transactions.
> >

http://msdn.microsoft.com/library/de...ba-bz_9ini.asp
> >
> > Hope this helps.
> > --
> > Angel Saenz-Badillos [MS] Managed Providers
> > This posting is provided "AS IS", with no warranties, and confers no
> > rights.Please do not send email directly to this alias.
> > This alias is for newsgroup purposes only.
> > I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/
> >
> >
> >
> >
> > "Dumitru Sbenghe" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Given the current status of ADO.NET (version 1.1), you can not do what
> >> you
> >> want. Because the SqlClient provider permit only one command running on

a
> >> given connection at a time, the only solution will be a distributed
> >> transaction coordinator + 2 connections with 2 commands executed
> >> concurrently aka ES/COM+ in .NET . or wait until ADO.NET 2.0 will be
> >> released , version which will permit this behavior.
> >>
> >>
> >>
> >> Dumitru
> >>
> >>
> >> "Shawn B." <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > Greetings,
> >> >
> >> > I have two SqlCommands objects that each need to execute concurrently

> > but
> >> > I
> >> > want them both to be a part of the same transaction.
> >> >
> >> > What I'm doing is created one SqlCommand and Beginning a transaction.
> >> >
> >> > I have another SqlCommand that is being executed from within an
> >> > Asynchronous
> >> > delegate that needs to participate in the same transaction. The
> >> > problem
> >> > is
> >> > that I get the infamous exception stating that the connection is
> >> > already
> >> > in
> >> > use.
> >> >
> >> > COM+ is not an answer here.
> >> >
> >> > What I want to know is if there is a way to make this work correctly

> > using
> >> > only ADO.NET?
> >> >
> >> >
> >> > Thanks,
> >> > Shawn
> >> >
> >> >
> >>
> >>

> >
> >

>
>



 
Reply With Quote
 
Shawn B.
Guest
Posts: n/a
 
      16th Mar 2005
What do you mean by interleaved? Do you mean that one will execute and
complete and then the other command in the transaction will execute and
complete and then the next one, from a queue of some sort? versus executing
parallel?


Thanks,
Shawn


"Pablo Castro [MS]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Actually, bound sessions allow you to share the transaction space among

two
> connections but doesn't give you concurrent access to it. If you try to
> execute a batch on a connection while the other is actively doing

something
> within the transaction the server will return an error saying that the
> transaction context is in use.
>
> COM+ is the only solution I know of for this scenario. Even with COM+/DTC
> you'll see some serialization around the transaction. In ADO.NET 2.0 + SQL
> Server 2005 you can mix MARS plus asynchronous command execution to submit
> two requests concurrently within the same transaction; however, note that

in
> that case you won't get parallelism in the server; multiple MARS sessions
> within a connection are interleaved, not executed in parallel.
>
> --
> Pablo Castro
> Program Manager - ADO.NET Team
> Microsoft Corp.
>
> This posting is provided "AS IS" with no warranties, and confers no

rights.
>
>
> "Angel Saenz-Badillos[MS]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > As long as both connections are to the same instance of Sql Server you

can
> > use sp_bindsession to get this behavior without having to use

distributed
> > transactions.
> >

http://msdn.microsoft.com/library/de...ba-bz_9ini.asp
> >
> > Hope this helps.
> > --
> > Angel Saenz-Badillos [MS] Managed Providers
> > This posting is provided "AS IS", with no warranties, and confers no
> > rights.Please do not send email directly to this alias.
> > This alias is for newsgroup purposes only.
> > I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/
> >
> >
> >
> >
> > "Dumitru Sbenghe" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Given the current status of ADO.NET (version 1.1), you can not do what
> >> you
> >> want. Because the SqlClient provider permit only one command running on

a
> >> given connection at a time, the only solution will be a distributed
> >> transaction coordinator + 2 connections with 2 commands executed
> >> concurrently aka ES/COM+ in .NET . or wait until ADO.NET 2.0 will be
> >> released , version which will permit this behavior.
> >>
> >>
> >>
> >> Dumitru
> >>
> >>
> >> "Shawn B." <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > Greetings,
> >> >
> >> > I have two SqlCommands objects that each need to execute concurrently

> > but
> >> > I
> >> > want them both to be a part of the same transaction.
> >> >
> >> > What I'm doing is created one SqlCommand and Beginning a transaction.
> >> >
> >> > I have another SqlCommand that is being executed from within an
> >> > Asynchronous
> >> > delegate that needs to participate in the same transaction. The
> >> > problem
> >> > is
> >> > that I get the infamous exception stating that the connection is
> >> > already
> >> > in
> >> > use.
> >> >
> >> > COM+ is not an answer here.
> >> >
> >> > What I want to know is if there is a way to make this work correctly

> > using
> >> > only ADO.NET?
> >> >
> >> >
> >> > Thanks,
> >> > Shawn
> >> >
> >> >
> >>
> >>

> >
> >

>
>



 
Reply With Quote
 
Pablo Castro [MS]
Guest
Posts: n/a
 
      16th Mar 2005
In MARS there are fixed interleave points in the server. For DML statements
(e.g. UPDATEs), the whole statement will run without yielding. For SELECT,
FETCH and a few other statements we'll yield on network writes so if we
return lots of rows you'll see intra-statement interleaving.

There is a great article on MARS here which includes more details on how
statements are interleaved:
http://msdn.microsoft.com/library/de...ARSinSQL05.asp

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.


"Shawn B." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> What do you mean by interleaved? Do you mean that one will execute and
> complete and then the other command in the transaction will execute and
> complete and then the next one, from a queue of some sort? versus
> executing
> parallel?
>
>
> Thanks,
> Shawn
>
>
> "Pablo Castro [MS]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Actually, bound sessions allow you to share the transaction space among

> two
>> connections but doesn't give you concurrent access to it. If you try to
>> execute a batch on a connection while the other is actively doing

> something
>> within the transaction the server will return an error saying that the
>> transaction context is in use.
>>
>> COM+ is the only solution I know of for this scenario. Even with COM+/DTC
>> you'll see some serialization around the transaction. In ADO.NET 2.0 +
>> SQL
>> Server 2005 you can mix MARS plus asynchronous command execution to
>> submit
>> two requests concurrently within the same transaction; however, note that

> in
>> that case you won't get parallelism in the server; multiple MARS sessions
>> within a connection are interleaved, not executed in parallel.
>>
>> --
>> Pablo Castro
>> Program Manager - ADO.NET Team
>> Microsoft Corp.
>>
>> This posting is provided "AS IS" with no warranties, and confers no

> rights.
>>
>>
>> "Angel Saenz-Badillos[MS]" <(E-Mail Removed)> wrote in
>> message
>> news:(E-Mail Removed)...
>> > As long as both connections are to the same instance of Sql Server you

> can
>> > use sp_bindsession to get this behavior without having to use

> distributed
>> > transactions.
>> >

> http://msdn.microsoft.com/library/de...ba-bz_9ini.asp
>> >
>> > Hope this helps.
>> > --
>> > Angel Saenz-Badillos [MS] Managed Providers
>> > This posting is provided "AS IS", with no warranties, and confers no
>> > rights.Please do not send email directly to this alias.
>> > This alias is for newsgroup purposes only.
>> > I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/
>> >
>> >
>> >
>> >
>> > "Dumitru Sbenghe" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> Given the current status of ADO.NET (version 1.1), you can not do what
>> >> you
>> >> want. Because the SqlClient provider permit only one command running
>> >> on

> a
>> >> given connection at a time, the only solution will be a distributed
>> >> transaction coordinator + 2 connections with 2 commands executed
>> >> concurrently aka ES/COM+ in .NET . or wait until ADO.NET 2.0 will be
>> >> released , version which will permit this behavior.
>> >>
>> >>
>> >>
>> >> Dumitru
>> >>
>> >>
>> >> "Shawn B." <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> > Greetings,
>> >> >
>> >> > I have two SqlCommands objects that each need to execute
>> >> > concurrently
>> > but
>> >> > I
>> >> > want them both to be a part of the same transaction.
>> >> >
>> >> > What I'm doing is created one SqlCommand and Beginning a
>> >> > transaction.
>> >> >
>> >> > I have another SqlCommand that is being executed from within an
>> >> > Asynchronous
>> >> > delegate that needs to participate in the same transaction. The
>> >> > problem
>> >> > is
>> >> > that I get the infamous exception stating that the connection is
>> >> > already
>> >> > in
>> >> > use.
>> >> >
>> >> > COM+ is not an answer here.
>> >> >
>> >> > What I want to know is if there is a way to make this work correctly
>> > using
>> >> > only ADO.NET?
>> >> >
>> >> >
>> >> > Thanks,
>> >> > Shawn
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >

>>
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Maximum concurrent connections to shared resources =?Utf-8?B?SmVyZW15IEdvbGRlbg==?= Windows XP Networking 4 29th Apr 2005 10:24 PM
"Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction." Ollie Riches Microsoft C# .NET 3 11th Mar 2005 06:23 PM
"Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction." Ollie Riches Microsoft ADO .NET 3 11th Mar 2005 06:23 PM
Root transaction wanted to commit, but transaction aborted =?Utf-8?B?S2FydW4gS2FydW5ha2FyYW4=?= Microsoft Dot NET Framework 2 23rd Jul 2004 07:33 PM
OdbcConnection : concurrent transaction, IsolationLevel Hakłna kł Microsoft Dot NET Framework 3 7th May 2004 02:30 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:29 PM.