Concurrent Connections / Shared Transaction

Discussion in 'Microsoft ADO .NET' started by Shawn B., Mar 12, 2005.

  1. Shawn B.

    Shawn B. Guest

    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
     
    Shawn B., Mar 12, 2005
    #1
    1. Advertisements

  2. 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." <> wrote in message
    news:...
    > 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
    >
    >
     
    Dumitru Sbenghe, Mar 12, 2005
    #2
    1. Advertisements

  3. 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/default.asp?url=/library/en-us/tsqlref/ts_sp_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" <> wrote in message
    news:...
    > 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." <> wrote in message
    > news:...
    > > 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
    > >
    > >

    >
    >
     
    Angel Saenz-Badillos[MS], Mar 14, 2005
    #3
  4. Shawn B.

    Shawn B. Guest

    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]" <> wrote in message
    news:...
    > 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/default.asp?url=/library/en-us/tsqlref/ts_sp_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" <> wrote in message
    > news:...
    > > 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." <> wrote in message
    > > news:...
    > > > 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
    > > >
    > > >

    > >
    > >

    >
    >
     
    Shawn B., Mar 14, 2005
    #4
  5. 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]" <> wrote in message
    news:...
    > 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/default.asp?url=/library/en-us/tsqlref/ts_sp_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" <> wrote in message
    > news:...
    >> 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." <> wrote in message
    >> news:...
    >> > 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
    >> >
    >> >

    >>
    >>

    >
    >
     
    Pablo Castro [MS], Mar 14, 2005
    #5
  6. 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]" <> wrote in message
    news:...
    > 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]" <> wrote in message
    > news:...
    > > 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/default.asp?url=/library/en-us/tsqlref/ts_sp_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" <> wrote in message
    > > news:...
    > >> 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." <> wrote in message
    > >> news:...
    > >> > 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
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
     
    Angel Saenz-Badillos[MS], Mar 16, 2005
    #6
  7. Shawn B.

    Shawn B. Guest

    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]" <> wrote in message
    news:...
    > 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]" <> wrote in message
    > news:...
    > > 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/default.asp?url=/library/en-us/tsqlref/ts_sp_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" <> wrote in message
    > > news:...
    > >> 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." <> wrote in message
    > >> news:...
    > >> > 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
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
     
    Shawn B., Mar 16, 2005
    #7
  8. 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/default.asp?url=/library/en-us/dnsql90/html/MARSinSQL05.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." <> wrote in message
    news:...
    > 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]" <> wrote in message
    > news:...
    >> 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]" <> wrote in
    >> message
    >> news:...
    >> > 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/default.asp?url=/library/en-us/tsqlref/ts_sp_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" <> wrote in message
    >> > news:...
    >> >> 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." <> wrote in message
    >> >> news:...
    >> >> > 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
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     
    Pablo Castro [MS], Mar 16, 2005
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John

    Number of concurrent users

    John, Aug 31, 2003, in forum: Microsoft ADO .NET
    Replies:
    6
    Views:
    1,028
    William \(Bill\) Vaughn
    Sep 1, 2003
  2. Tim Marsden

    Transaction across different connections.

    Tim Marsden, Sep 21, 2003, in forum: Microsoft ADO .NET
    Replies:
    3
    Views:
    180
    Pablo Castro [MS]
    Sep 25, 2003
  3. Grober
    Replies:
    1
    Views:
    851
    Grober
    Nov 24, 2003
  4. Guest
    Replies:
    8
    Views:
    1,875
    Guest
    May 7, 2004
  5. Ollie Riches
    Replies:
    3
    Views:
    2,760
    Angel Saenz-Badillos[MS]
    Mar 11, 2005
Loading...

Share This Page