BUG? SqlCommand incorrectly reports 'The SqlCommand is currently b

Discussion in 'Microsoft ADO .NET' started by Guest, Oct 13, 2004.

  1. Guest

    Guest Guest

    I have a situation where a SqlCommand incorrectly throws an
    InvalidOperationException when two threads are using the same SqlCommand
    instance at different times. As you can see below the application
    synchronizes on the shared SqlCommand instances why thread synchronization is
    not the issue.

    The high level order of events when the exception occurs look like this:

    1. Thread1 creates SqlConnection and transaction, sets the Transaction and
    Connection property of SqlCommand1, creates a Reader using SqlCommand1,
    iterates over the reader and closes the reader
    2. Thread1 sets the Transaction and Connection property of SqlCommand2 and
    creates a reader using SqlCommand2
    3. At this point in time, Thread2 has reached the point where it has created
    another SqlConnection and transaction and tries to set the Transaction
    property of SqlCommand1. This is the point at which the exception is thrown.

    The reason seems to be that the Transaction currently associated with cmd1
    is being used with another SqlCommand (cmd2) for which a reader has been
    opened (on the first thread).

    This seems like a bug at worst or an undocumented feature at best. To get
    around the problem, simply add the following two lines after the first
    'reader.Close();'.
    cmd.Transaction = null;
    cmd.Connection = null;

    It seems as if the set operation of the SqlCommand.Transaction property does
    the following:
    'Give me the the current Transaction's Connection's Reader and check if it
    is busy. If it is, throw the InvalidOperationException'.

    The problem is that the 'Current Transaction's Connection's Reader' was
    created from another command (cmd2 in this case) why this logic seems flawed.

    It seems this either is a bug or if not, the documentation should be updated
    to say to explicitly set the Transaction and Connection properties of the
    command to null after the Reader is closed...


    To run the application you can use any SqlServer database (specify the
    ConnectionString property in app.config) and change the SQL in the
    BugReproducer constructor to any valid sql statement. The application goes to
    sleep for three seconds after creating the second Reader as to force the
    exception situation to occur.


    using System;
    using System.Threading;
    using System.Data;
    using System.Data.SqlClient;

    namespace SqlCommandBug
    {
    class Class1
    {
    [STAThread]
    static void Main(string[] args)
    {
    new BugReproducer().Reproduce();
    }

    public class BugReproducer {
    public static SqlCommand cmd = new SqlCommand();
    public static SqlCommand cmd2 = new SqlCommand();

    public BugReproducer() {
    cmd.CommandText = "Select * from Parameter";
    cmd2.CommandText = "Select * from Application_Curve";
    }

    public void Reproduce() {
    for (int i = 0; i < 2; i++) {
    new Thread(new ThreadStart(this.DoIt)).Start();
    }
    }

    public void DoIt() {
    SqlConnection conn = new
    SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
    conn.Open();
    SqlTransaction t1 = conn.BeginTransaction();

    lock (cmd) {
    cmd.Transaction = t1;
    cmd.Connection = t1.Connection;
    IDataReader reader = cmd.ExecuteReader();
    while (reader.Read());
    reader.Close();
    cmd.Transaction = null;
    cmd.Connection = null;

    }
    lock (cmd2) {
    cmd2.Transaction = t1;
    cmd2.Connection = t1.Connection;
    IDataReader reader2 = cmd2.ExecuteReader();
    Thread.Sleep(3000);
    while (reader2.Read());
    reader2.Close();
    }
    t1.Commit();
    }
    }
    }
    }
     
    Guest, Oct 13, 2004
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    Doh.

    To reproduce, run this program (I had left the lines in that gets around the
    bug in the original posting):

    using System;
    using System.Threading;
    using System.Data;
    using System.Data.SqlClient;

    namespace SqlCommandBug
    {
    class Class1
    {
    [STAThread]
    static void Main(string[] args)
    {
    new BugReproducer().Reproduce();
    }

    public class BugReproducer {
    public static SqlCommand cmd = new SqlCommand();
    public static SqlCommand cmd2 = new SqlCommand();

    public BugReproducer() {
    cmd.CommandText = "Select * from Parameter";
    cmd2.CommandText = "Select * from Application_Curve";
    }

    public void Reproduce() {
    for (int i = 0; i < 2; i++) {
    new Thread(new ThreadStart(this.DoIt)).Start();
    }
    }

    public void DoIt() {
    SqlConnection conn = new
    SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
    conn.Open();
    SqlTransaction t1 = conn.BeginTransaction();

    lock (cmd) {
    cmd.Transaction = t1;
    cmd.Connection = t1.Connection;
    IDataReader reader = cmd.ExecuteReader();
    while (reader.Read());
    reader.Close();

    }
    lock (cmd2) {
    cmd2.Transaction = t1;
    cmd2.Connection = t1.Connection;
    IDataReader reader2 = cmd2.ExecuteReader();
    Thread.Sleep(3000);
    while (reader2.Read());
    reader2.Close();
    }
    t1.Commit();
    }
    }
    }
    }

    "Base64" wrote:

    > I have a situation where a SqlCommand incorrectly throws an
    > InvalidOperationException when two threads are using the same SqlCommand
    > instance at different times. As you can see below the application
    > synchronizes on the shared SqlCommand instances why thread synchronization is
    > not the issue.
    >
    > The high level order of events when the exception occurs look like this:
    >
    > 1. Thread1 creates SqlConnection and transaction, sets the Transaction and
    > Connection property of SqlCommand1, creates a Reader using SqlCommand1,
    > iterates over the reader and closes the reader
    > 2. Thread1 sets the Transaction and Connection property of SqlCommand2 and
    > creates a reader using SqlCommand2
    > 3. At this point in time, Thread2 has reached the point where it has created
    > another SqlConnection and transaction and tries to set the Transaction
    > property of SqlCommand1. This is the point at which the exception is thrown.
    >
    > The reason seems to be that the Transaction currently associated with cmd1
    > is being used with another SqlCommand (cmd2) for which a reader has been
    > opened (on the first thread).
    >
    > This seems like a bug at worst or an undocumented feature at best. To get
    > around the problem, simply add the following two lines after the first
    > 'reader.Close();'.
    > cmd.Transaction = null;
    > cmd.Connection = null;
    >
    > It seems as if the set operation of the SqlCommand.Transaction property does
    > the following:
    > 'Give me the the current Transaction's Connection's Reader and check if it
    > is busy. If it is, throw the InvalidOperationException'.
    >
    > The problem is that the 'Current Transaction's Connection's Reader' was
    > created from another command (cmd2 in this case) why this logic seems flawed.
    >
    > It seems this either is a bug or if not, the documentation should be updated
    > to say to explicitly set the Transaction and Connection properties of the
    > command to null after the Reader is closed...
    >
    >
    > To run the application you can use any SqlServer database (specify the
    > ConnectionString property in app.config) and change the SQL in the
    > BugReproducer constructor to any valid sql statement. The application goes to
    > sleep for three seconds after creating the second Reader as to force the
    > exception situation to occur.
    >
    >
    > using System;
    > using System.Threading;
    > using System.Data;
    > using System.Data.SqlClient;
    >
    > namespace SqlCommandBug
    > {
    > class Class1
    > {
    > [STAThread]
    > static void Main(string[] args)
    > {
    > new BugReproducer().Reproduce();
    > }
    >
    > public class BugReproducer {
    > public static SqlCommand cmd = new SqlCommand();
    > public static SqlCommand cmd2 = new SqlCommand();
    >
    > public BugReproducer() {
    > cmd.CommandText = "Select * from Parameter";
    > cmd2.CommandText = "Select * from Application_Curve";
    > }
    >
    > public void Reproduce() {
    > for (int i = 0; i < 2; i++) {
    > new Thread(new ThreadStart(this.DoIt)).Start();
    > }
    > }
    >
    > public void DoIt() {
    > SqlConnection conn = new
    > SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
    > conn.Open();
    > SqlTransaction t1 = conn.BeginTransaction();
    >
    > lock (cmd) {
    > cmd.Transaction = t1;
    > cmd.Connection = t1.Connection;
    > IDataReader reader = cmd.ExecuteReader();
    > while (reader.Read());
    > reader.Close();
    > cmd.Transaction = null;
    > cmd.Connection = null;
    >
    > }
    > lock (cmd2) {
    > cmd2.Transaction = t1;
    > cmd2.Connection = t1.Connection;
    > IDataReader reader2 = cmd2.ExecuteReader();
    > Thread.Sleep(3000);
    > while (reader2.Read());
    > reader2.Close();
    > }
    > t1.Commit();
    > }
    > }
    > }
    > }
    >
     
    Guest, Oct 13, 2004
    #2
    1. Advertisements

  3. What purpose does cmd2 have? Why not just change the command text and use
    cmd for both. By doing this you'll simplify your design, save yourself an
    object and not have to change the association of the connection which I
    believe is at the core of your problem.

    --
    W.G. Ryan, MVP

    www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
    "Base64" <> wrote in message
    news:...
    > Doh.
    >
    > To reproduce, run this program (I had left the lines in that gets around

    the
    > bug in the original posting):
    >
    > using System;
    > using System.Threading;
    > using System.Data;
    > using System.Data.SqlClient;
    >
    > namespace SqlCommandBug
    > {
    > class Class1
    > {
    > [STAThread]
    > static void Main(string[] args)
    > {
    > new BugReproducer().Reproduce();
    > }
    >
    > public class BugReproducer {
    > public static SqlCommand cmd = new SqlCommand();
    > public static SqlCommand cmd2 = new SqlCommand();
    >
    > public BugReproducer() {
    > cmd.CommandText = "Select * from Parameter";
    > cmd2.CommandText = "Select * from Application_Curve";
    > }
    >
    > public void Reproduce() {
    > for (int i = 0; i < 2; i++) {
    > new Thread(new ThreadStart(this.DoIt)).Start();
    > }
    > }
    >
    > public void DoIt() {
    > SqlConnection conn = new
    >

    SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Connec
    tionString"]);
    > conn.Open();
    > SqlTransaction t1 = conn.BeginTransaction();
    >
    > lock (cmd) {
    > cmd.Transaction = t1;
    > cmd.Connection = t1.Connection;
    > IDataReader reader = cmd.ExecuteReader();
    > while (reader.Read());
    > reader.Close();
    >
    > }
    > lock (cmd2) {
    > cmd2.Transaction = t1;
    > cmd2.Connection = t1.Connection;
    > IDataReader reader2 = cmd2.ExecuteReader();
    > Thread.Sleep(3000);
    > while (reader2.Read());
    > reader2.Close();
    > }
    > t1.Commit();
    > }
    > }
    > }
    > }
    >
    > "Base64" wrote:
    >
    > > I have a situation where a SqlCommand incorrectly throws an
    > > InvalidOperationException when two threads are using the same SqlCommand
    > > instance at different times. As you can see below the application
    > > synchronizes on the shared SqlCommand instances why thread

    synchronization is
    > > not the issue.
    > >
    > > The high level order of events when the exception occurs look like this:
    > >
    > > 1. Thread1 creates SqlConnection and transaction, sets the Transaction

    and
    > > Connection property of SqlCommand1, creates a Reader using SqlCommand1,
    > > iterates over the reader and closes the reader
    > > 2. Thread1 sets the Transaction and Connection property of SqlCommand2

    and
    > > creates a reader using SqlCommand2
    > > 3. At this point in time, Thread2 has reached the point where it has

    created
    > > another SqlConnection and transaction and tries to set the Transaction
    > > property of SqlCommand1. This is the point at which the exception is

    thrown.
    > >
    > > The reason seems to be that the Transaction currently associated with

    cmd1
    > > is being used with another SqlCommand (cmd2) for which a reader has been
    > > opened (on the first thread).
    > >
    > > This seems like a bug at worst or an undocumented feature at best. To

    get
    > > around the problem, simply add the following two lines after the first
    > > 'reader.Close();'.
    > > cmd.Transaction = null;
    > > cmd.Connection = null;
    > >
    > > It seems as if the set operation of the SqlCommand.Transaction property

    does
    > > the following:
    > > 'Give me the the current Transaction's Connection's Reader and check if

    it
    > > is busy. If it is, throw the InvalidOperationException'.
    > >
    > > The problem is that the 'Current Transaction's Connection's Reader' was
    > > created from another command (cmd2 in this case) why this logic seems

    flawed.
    > >
    > > It seems this either is a bug or if not, the documentation should be

    updated
    > > to say to explicitly set the Transaction and Connection properties of

    the
    > > command to null after the Reader is closed...
    > >
    > >
    > > To run the application you can use any SqlServer database (specify the
    > > ConnectionString property in app.config) and change the SQL in the
    > > BugReproducer constructor to any valid sql statement. The application

    goes to
    > > sleep for three seconds after creating the second Reader as to force the
    > > exception situation to occur.
    > >
    > >
    > > using System;
    > > using System.Threading;
    > > using System.Data;
    > > using System.Data.SqlClient;
    > >
    > > namespace SqlCommandBug
    > > {
    > > class Class1
    > > {
    > > [STAThread]
    > > static void Main(string[] args)
    > > {
    > > new BugReproducer().Reproduce();
    > > }
    > >
    > > public class BugReproducer {
    > > public static SqlCommand cmd = new SqlCommand();
    > > public static SqlCommand cmd2 = new SqlCommand();
    > >
    > > public BugReproducer() {
    > > cmd.CommandText = "Select * from Parameter";
    > > cmd2.CommandText = "Select * from Application_Curve";
    > > }
    > >
    > > public void Reproduce() {
    > > for (int i = 0; i < 2; i++) {
    > > new Thread(new ThreadStart(this.DoIt)).Start();
    > > }
    > > }
    > >
    > > public void DoIt() {
    > > SqlConnection conn = new
    > >

    SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Connec
    tionString"]);
    > > conn.Open();
    > > SqlTransaction t1 = conn.BeginTransaction();
    > >
    > > lock (cmd) {
    > > cmd.Transaction = t1;
    > > cmd.Connection = t1.Connection;
    > > IDataReader reader = cmd.ExecuteReader();
    > > while (reader.Read());
    > > reader.Close();
    > > cmd.Transaction = null;
    > > cmd.Connection = null;
    > >
    > > }
    > > lock (cmd2) {
    > > cmd2.Transaction = t1;
    > > cmd2.Connection = t1.Connection;
    > > IDataReader reader2 = cmd2.ExecuteReader();
    > > Thread.Sleep(3000);
    > > while (reader2.Read());
    > > reader2.Close();
    > > }
    > > t1.Commit();
    > > }
    > > }
    > > }
    > > }
    > >
     
    W.G. Ryan eMVP, Oct 13, 2004
    #3
  4. Guest

    David Browne Guest

    "Base64" <> wrote in message
    news:...
    > Doh.
    > >> I have a situation where a SqlCommand incorrectly throws an

    >> InvalidOperationException when two threads are using the same SqlCommand
    >> instance at different times. As you can see below the application
    >> synchronizes on the shared SqlCommand instances why thread
    >> synchronization is
    >> not the issue.
    >>


    The doc for SqlCommand says

    You can reset the CommandText property and reuse the SqlCommand object.
    However, you must close the SqlDataReader before you can execute a new or
    previous command.

    Adding the fact that a SqlDataReader is associated not with a SqlCommand byt
    a SqlConnection, this means: if a command's current connection has an open
    SqlDataReader, you cannot reuse the SqlCommand. Which means you must attach
    the command to the new connection before you enlist it in a different
    transaction.

    Just switch the order of your code and set cmd.connection before
    cmd.transaction.

    But I don't really see why you would want to serialize your code just to
    share SqlCommands. A SqlCommand is a lightweight, disconnected object. You
    will waste much more time in synchronization waits than if you just created
    new SqlCommands each time.

    David
     
    David Browne, Oct 13, 2004
    #4
  5. Guest

    David Browne Guest

    "David Browne" <davidbaxterbrowne no potted > wrote in
    message news:%...
    >
    > "Base64" <> wrote in message
    > news:...
    >> Doh.
    >> >> I have a situation where a SqlCommand incorrectly throws an
    >>> InvalidOperationException when two threads are using the same SqlCommand
    >>> instance at different times. As you can see below the application
    >>> synchronizes on the shared SqlCommand instances why thread
    >>> synchronization is
    >>> not the issue.
    >>>

    >


    Oops,

    That doesn't work either (it just happend to a couple of times).

    I guess you cannot reuse a SqlCommand while it's SqlConnection is fetching.

    David
     
    David Browne, Oct 14, 2004
    #5
  6. Base64,
    This looks like a bug to me, will have to take a look at it. The fact that
    it has a simple work arround and that it no longer repros in whidbey beta1
    probably means that it is going to take me a while to get to it though. If
    this is urgent you want to talk to PSS. I really appreciate the fact that
    you are including full repro code.

    I am not the biggest fan of using ado.net objects across threads, I assume
    that you want to prepare a complex command with parameters and then use
    these commands across threads? I would have thought that locking the command
    would be more expensive than this, have you not found it so?
    --
    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/




    "David Browne" <davidbaxterbrowne no potted > wrote in
    message news:...
    >
    > "David Browne" <davidbaxterbrowne no potted > wrote in
    > message news:%...
    > >
    > > "Base64" <> wrote in message
    > > news:...
    > >> Doh.
    > >> >> I have a situation where a SqlCommand incorrectly throws an
    > >>> InvalidOperationException when two threads are using the same

    SqlCommand
    > >>> instance at different times. As you can see below the application
    > >>> synchronizes on the shared SqlCommand instances why thread
    > >>> synchronization is
    > >>> not the issue.
    > >>>

    > >

    >
    > Oops,
    >
    > That doesn't work either (it just happend to a couple of times).
    >
    > I guess you cannot reuse a SqlCommand while it's SqlConnection is

    fetching.
    >
    > David
    >
    >
    >
     
    Angel Saenz-Badillos[MS], Oct 14, 2004
    #6
  7. Guest

    Guest Guest

    Re: BUG? SqlCommand incorrectly reports 'The SqlCommand is current

    Hi Angel.

    Thanks for the fast reply.

    The issue is not urgent since we have the workaround. I mostly wanted to
    verify that it is in fact a bug and perhaps save someone some headaches if
    they encounter the same situation.

    I did a quick benchmark consisting of 20000 typical (for our app) command
    executions. First round I used single instances, locking on them as in the
    code I posted. In the second I created the commands for each execution. The
    result was a dead heat, the 'locking' scenario won but with a margin of less
    than a 10th of a percent.

    I also did a test doing the same number of command executions, with and
    without the locks, on 10 threads doing 2000 invocations each. I would have
    expected the 'lock' version to fare poorly compared to the one which creates
    the command each time but they ended up being a dead heat as well. Reason
    being that our typical sql commands execute very fast creating almost no
    contention for the commands when locking on them.

    I added a Sleep(50) for each of the commands (within the lock in the 'lock'
    version) to simulate them taking longer to execute and in doing so the
    contention for the shared command instances became very apparent. The 'lock'
    version took three times longer (300%) to execute compared to the version
    creating the instances each time.

    So it seems the conclusion would be that sharing the command instances
    imposes a penalty compared to creating the SqlCommand instances each time if
    the application is highly multi threaded with execution of the sql being
    expensive. In the other scenarios there does not seem to be a significant
    difference.
    However sharing the instances does not provide a significant benefit in any
    of the scenarios why this approach probably should be avoided unless there
    are very good reasons to use it.

    Best Regards
    Fredrik Sjodin




    "Angel Saenz-Badillos[MS]" wrote:

    > Base64,
    > This looks like a bug to me, will have to take a look at it. The fact that
    > it has a simple work arround and that it no longer repros in whidbey beta1
    > probably means that it is going to take me a while to get to it though. If
    > this is urgent you want to talk to PSS. I really appreciate the fact that
    > you are including full repro code.
    >
    > I am not the biggest fan of using ado.net objects across threads, I assume
    > that you want to prepare a complex command with parameters and then use
    > these commands across threads? I would have thought that locking the command
    > would be more expensive than this, have you not found it so?
    > --
    > 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/
    >
    >
    >
    >
    > "David Browne" <davidbaxterbrowne no potted > wrote in
    > message news:...
    > >
    > > "David Browne" <davidbaxterbrowne no potted > wrote in
    > > message news:%...
    > > >
    > > > "Base64" <> wrote in message
    > > > news:...
    > > >> Doh.
    > > >> >> I have a situation where a SqlCommand incorrectly throws an
    > > >>> InvalidOperationException when two threads are using the same

    > SqlCommand
    > > >>> instance at different times. As you can see below the application
    > > >>> synchronizes on the shared SqlCommand instances why thread
    > > >>> synchronization is
    > > >>> not the issue.
    > > >>>
    > > >

    > >
    > > Oops,
    > >
    > > That doesn't work either (it just happend to a couple of times).
    > >
    > > I guess you cannot reuse a SqlCommand while it's SqlConnection is

    > fetching.
    > >
    > > David
    > >
    > >
    > >

    >
    >
    >
     
    Guest, Oct 14, 2004
    #7
  8. Guest

    Guest Guest

    Re: BUG? SqlCommand incorrectly reports 'The SqlCommand is current

    Hi Angel.

    Thanks for the fast reply.

    The issue is not urgent since we have the workaround. I mostly wanted to
    verify that it is in fact a bug and perhaps save someone some headaches if
    they encounter the same situation.

    I did a quick benchmark consisting of 20000 typical (for our app) command
    executions. First round I used single instances, locking on them as in the
    code I posted. In the second I created the commands for each execution. The
    result was a dead heat, the 'locking' scenario won but with a margin of less
    than a 10th of a percent.

    I also did a test doing the same number of command executions, with and
    without the locks, on 10 threads doing 2000 invocations each. I would have
    expected the 'lock' version to fare poorly compared to the one which creates
    the command each time but they ended up being a dead heat as well. Reason
    being that our typical sql commands execute very fast creating almost no
    contention for the commands when locking on them.

    I added a Sleep(50) for each of the commands (within the lock in the 'lock'
    version) to simulate them taking longer to execute and in doing so the
    contention for the shared command instances became very apparent. The 'lock'
    version took three times longer (300%) to execute compared to the version
    creating the instances each time.

    So it seems the conclusion would be that sharing the command instances
    imposes a penalty compared to creating the SqlCommand instances each time if
    the application is highly multi threaded with execution of the sql being
    expensive. In the other scenarios there does not seem to be a significant
    difference.
    However sharing the instances does not provide a significant benefit in any
    of the scenarios why this approach probably should be avoided unless there
    are very good reasons to use it.

    Best Regards
    Fredrik Sjodin




    "Angel Saenz-Badillos[MS]" wrote:

    > Base64,
    > This looks like a bug to me, will have to take a look at it. The fact that
    > it has a simple work arround and that it no longer repros in whidbey beta1
    > probably means that it is going to take me a while to get to it though. If
    > this is urgent you want to talk to PSS. I really appreciate the fact that
    > you are including full repro code.
    >
    > I am not the biggest fan of using ado.net objects across threads, I assume
    > that you want to prepare a complex command with parameters and then use
    > these commands across threads? I would have thought that locking the command
    > would be more expensive than this, have you not found it so?
    > --
    > 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/
    >
    >
    >
    >
    > "David Browne" <davidbaxterbrowne no potted > wrote in
    > message news:...
    > >
    > > "David Browne" <davidbaxterbrowne no potted > wrote in
    > > message news:%...
    > > >
    > > > "Base64" <> wrote in message
    > > > news:...
    > > >> Doh.
    > > >> >> I have a situation where a SqlCommand incorrectly throws an
    > > >>> InvalidOperationException when two threads are using the same

    > SqlCommand
    > > >>> instance at different times. As you can see below the application
    > > >>> synchronizes on the shared SqlCommand instances why thread
    > > >>> synchronization is
    > > >>> not the issue.
    > > >>>
    > > >

    > >
    > > Oops,
    > >
    > > That doesn't work either (it just happend to a couple of times).
    > >
    > > I guess you cannot reuse a SqlCommand while it's SqlConnection is

    > fetching.
    > >
    > > David
    > >
    > >
    > >

    >
    >
    >
     
    Guest, Oct 14, 2004
    #8
  9. Re: BUG? SqlCommand incorrectly reports 'The SqlCommand is current

    >sharing the instances does not provide a significant benefit in any of the
    scenarios
    This has been my experience as well, you end up with harder to read code
    that has the potential for threading related bugs without tangible benefits.
    This is specially true when you are using more threads or a more powerful
    multi proc machine.

    That said I have to admit that I don't know enough to be able to make a call
    on whether you could squeeze more performance using cached commands, and
    that I am biased against locks.
    --
    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/




    "Base64" <> wrote in message
    news:...
    > Hi Angel.
    >
    > Thanks for the fast reply.
    >
    > The issue is not urgent since we have the workaround. I mostly wanted to
    > verify that it is in fact a bug and perhaps save someone some headaches if
    > they encounter the same situation.
    >
    > I did a quick benchmark consisting of 20000 typical (for our app) command
    > executions. First round I used single instances, locking on them as in the
    > code I posted. In the second I created the commands for each execution.

    The
    > result was a dead heat, the 'locking' scenario won but with a margin of

    less
    > than a 10th of a percent.
    >
    > I also did a test doing the same number of command executions, with and
    > without the locks, on 10 threads doing 2000 invocations each. I would have
    > expected the 'lock' version to fare poorly compared to the one which

    creates
    > the command each time but they ended up being a dead heat as well. Reason
    > being that our typical sql commands execute very fast creating almost no
    > contention for the commands when locking on them.
    >
    > I added a Sleep(50) for each of the commands (within the lock in the

    'lock'
    > version) to simulate them taking longer to execute and in doing so the
    > contention for the shared command instances became very apparent. The

    'lock'
    > version took three times longer (300%) to execute compared to the version
    > creating the instances each time.
    >
    > So it seems the conclusion would be that sharing the command instances
    > imposes a penalty compared to creating the SqlCommand instances each time

    if
    > the application is highly multi threaded with execution of the sql being
    > expensive. In the other scenarios there does not seem to be a significant
    > difference.
    > However sharing the instances does not provide a significant benefit in

    any
    > of the scenarios why this approach probably should be avoided unless there
    > are very good reasons to use it.
    >
    > Best Regards
    > Fredrik Sjodin
    >
    >
    >
    >
    > "Angel Saenz-Badillos[MS]" wrote:
    >
    > > Base64,
    > > This looks like a bug to me, will have to take a look at it. The fact

    that
    > > it has a simple work arround and that it no longer repros in whidbey

    beta1
    > > probably means that it is going to take me a while to get to it though.

    If
    > > this is urgent you want to talk to PSS. I really appreciate the fact

    that
    > > you are including full repro code.
    > >
    > > I am not the biggest fan of using ado.net objects across threads, I

    assume
    > > that you want to prepare a complex command with parameters and then use
    > > these commands across threads? I would have thought that locking the

    command
    > > would be more expensive than this, have you not found it so?
    > > --
    > > 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/
    > >
    > >
    > >
    > >
    > > "David Browne" <davidbaxterbrowne no potted > wrote in
    > > message news:...
    > > >
    > > > "David Browne" <davidbaxterbrowne no potted > wrote in
    > > > message news:%...
    > > > >
    > > > > "Base64" <> wrote in message
    > > > > news:...
    > > > >> Doh.
    > > > >> >> I have a situation where a SqlCommand incorrectly throws an
    > > > >>> InvalidOperationException when two threads are using the same

    > > SqlCommand
    > > > >>> instance at different times. As you can see below the application
    > > > >>> synchronizes on the shared SqlCommand instances why thread
    > > > >>> synchronization is
    > > > >>> not the issue.
    > > > >>>
    > > > >
    > > >
    > > > Oops,
    > > >
    > > > That doesn't work either (it just happend to a couple of times).
    > > >
    > > > I guess you cannot reuse a SqlCommand while it's SqlConnection is

    > > fetching.
    > > >
    > > > David
    > > >
    > > >
    > > >

    > >
    > >
    > >
     
    Angel Saenz-Badillos[MS], Oct 14, 2004
    #9
    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. Steve B. [Labo.Net]

    Threading and SqlCommand.ExecuteNonQuery

    Steve B. [Labo.Net], Jul 16, 2003, in forum: Microsoft ADO .NET
    Replies:
    2
    Views:
    822
    AlexS
    Jul 18, 2003
  2. phil

    Bug in SqlCommand?

    phil, Sep 4, 2003, in forum: Microsoft ADO .NET
    Replies:
    2
    Views:
    2,202
    Pablo Castro [MS]
    Sep 4, 2003
  3. Giox
    Replies:
    3
    Views:
    2,007
  4. moondaddy

    Bug in SqlClient.SqlCommand.Parameters collection

    moondaddy, Mar 21, 2005, in forum: Microsoft ADO .NET
    Replies:
    2
    Views:
    300
    moondaddy
    Mar 21, 2005
  5. Guest
    Replies:
    9
    Views:
    873
    Guest
    Jan 25, 2007
Loading...

Share This Page