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();
}
}
}
}

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();
> }
> }
> }
> }
>

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" <(E-Mail Removed)> wrote in message
news:18BCAD0C-8354-4BC1-9F0A-(E-Mail Removed)...
> 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();
> > }
> > }
> > }
> > }
> >

"Base64" <(E-Mail Removed)> wrote in message
news:18BCAD0C-8354-4BC1-9F0A-(E-Mail Removed)...
> 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" <davidbaxterbrowne no potted (E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
>
> "Base64" <(E-Mail Removed)> wrote in message
> news:18BCAD0C-8354-4BC1-9F0A-(E-Mail Removed)...
>> 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

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 (E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> "David Browne" <davidbaxterbrowne no potted (E-Mail Removed)> wrote in
> message news:%(E-Mail Removed)...
> >
> > "Base64" <(E-Mail Removed)> wrote in message
> > news:18BCAD0C-8354-4BC1-9F0A-(E-Mail Removed)...
> >> 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
>
>
>

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 (E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
> >
> > "David Browne" <davidbaxterbrowne no potted (E-Mail Removed)> wrote in
> > message news:%(E-Mail Removed)...
> > >
> > > "Base64" <(E-Mail Removed)> wrote in message
> > > news:18BCAD0C-8354-4BC1-9F0A-(E-Mail Removed)...
> > >> 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
> >
> >
> >
>
>
>

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 (E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
> >
> > "David Browne" <davidbaxterbrowne no potted (E-Mail Removed)> wrote in
> > message news:%(E-Mail Removed)...
> > >
> > > "Base64" <(E-Mail Removed)> wrote in message
> > > news:18BCAD0C-8354-4BC1-9F0A-(E-Mail Removed)...
> > >> 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
> >
> >
> >
>
>
>

>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" <(E-Mail Removed)> wrote in message
news:E1B77009-74DF-42AC-8A98-(E-Mail Removed)...
> 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 (E-Mail Removed)> wrote in
> > message news:(E-Mail Removed)...
> > >
> > > "David Browne" <davidbaxterbrowne no potted (E-Mail Removed)> wrote in
> > > message news:%(E-Mail Removed)...
> > > >
> > > > "Base64" <(E-Mail Removed)> wrote in message
> > > > news:18BCAD0C-8354-4BC1-9F0A-(E-Mail Removed)...
> > > >> 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
> > >
> > >
> > >
> >
> >
> >