PC Review


Reply
Thread Tools Rate Thread

BUG? SqlCommand incorrectly reports 'The SqlCommand is currently b

 
 
=?Utf-8?B?QmFzZTY0?=
Guest
Posts: n/a
 
      13th Oct 2004
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();
}
}
}
}

 
Reply With Quote
 
 
 
 
=?Utf-8?B?QmFzZTY0?=
Guest
Posts: n/a
 
      13th Oct 2004
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();
> }
> }
> }
> }
>

 
Reply With Quote
 
 
 
 
W.G. Ryan eMVP
Guest
Posts: n/a
 
      13th Oct 2004
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:(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();
> > }
> > }
> > }
> > }
> >



 
Reply With Quote
 
David Browne
Guest
Posts: n/a
 
      13th Oct 2004

"Base64" <(E-Mail Removed)> wrote in message
news:(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


 
Reply With Quote
 
David Browne
Guest
Posts: n/a
 
      14th Oct 2004

"David Browne" <davidbaxterbrowne no potted (E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
>
> "Base64" <(E-Mail Removed)> wrote in message
> news:(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



 
Reply With Quote
 
Angel Saenz-Badillos[MS]
Guest
Posts: n/a
 
      14th Oct 2004
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:(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
>
>
>



 
Reply With Quote
 
=?Utf-8?B?QmFzZTY0?=
Guest
Posts: n/a
 
      14th Oct 2004
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:(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
> >
> >
> >

>
>
>

 
Reply With Quote
 
=?Utf-8?B?QmFzZTY0?=
Guest
Posts: n/a
 
      14th Oct 2004
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:(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
> >
> >
> >

>
>
>

 
Reply With Quote
 
Angel Saenz-Badillos[MS]
Guest
Posts: n/a
 
      14th Oct 2004
>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:(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:(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
> > >
> > >
> > >

> >
> >
> >



 
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
Call SqlCommand.ExecuteReader inside another SqlCommand.ExecuteRea =?Utf-8?B?RGF2aWQgVGhpZWxlbg==?= Microsoft ADO .NET 9 25th Jan 2007 05:57 PM
Help .....System.InvalidOperationException: The SqlCommand is currently busy Open, Fetching. Giox Microsoft ADO .NET 3 23rd Dec 2004 09:26 AM
Windows Security Center incorrectly reports firewall status =?Utf-8?B?VHJhdmlz?= Windows XP Security 5 30th Nov 2004 11:13 PM
CHKDSK /R incorrectly reports a bad cluster in large, compressed files on NTFS volume Ben Voris \(remove 'N' 'O' and 'SPAM' to reply\) Windows XP General 0 15th Jul 2004 06:15 AM
Windows XP incorrectly reports files in use Terry Windows XP Security 0 20th Nov 2003 11:29 PM


Features
 

Advertising
 

Newsgroups
 


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