Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...

.NET Forum / .NET Framework / New Users / March 2006

Tip: Looking for answers? Try searching our database.

are System.Data.SqlClient.SqlConnection thread safe? can many threads share a System.Data.SqlClient.SqlConnection instance without any synchronization?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Daniel - 22 Mar 2006 06:58 GMT
are System.Data.SqlClient.SqlConnection thread safe? can many threads share
a System.Data.SqlClient.SqlConnection instance without any synchronization?
Dmytro Lapshyn [MVP] - 22 Mar 2006 09:04 GMT
Hi Daniel,

I remember a colleague was telling me it was not thread-safe and his code
was experiencing performance hits.

Actually, since there's the connection pool being maintained behind the
scenes, there's no need to share a SqlConnection instance.

> are System.Data.SqlClient.SqlConnection thread safe? can many threads
> share
> a System.Data.SqlClient.SqlConnection instance without any
> synchronization?
Laura T. - 22 Mar 2006 15:43 GMT
I think it is thread safe.. sort of, as now it supports MARS:

From http://blogs.msdn.com/angelsb/archive/2004/07/15/184479.aspx:
"
Multiple Active Result Sets per connection (MARS):

I don't really believe this feature will be the winner of the PTBGM, but it
is definitely a contender. When connecting with SqlClient to Sql Server 2005
(or with Oledb and the next release of the network libraries) we will now
allow multiple active result sets per connection. In ado.net this mostly
means that you will now be able to open multiple datareaders per connection
as long as you use a separate command for each. The main scenario for this
feature would be being able to modify the database as you process a
datareader, it also enables running queries inside of the same transaction
context and _can_ have a performance benefit in very specific scenarios
where the cost of more connections (about 40k memory per connection) becomes
a concern. There are a number of ways in which "things can go wrong" T
however. Forgetting to close a datareader will now stop you from committing
a transaction, modifying the state of the connection can give you unexpected
results, reading and writing inside of a transaction context may isolate the
reader from the changes and writer writer conflicts are non deterministic.
Overall fairly minor and contrived concerns, my personal biggest problem
with this feature however is that it is hard to squeeze real performance
gains out of it, and easy to write hard to maintain code to try it. There
are performance implications to using MARS across the board. On the client
we run into an issue where creating a new batch is not free, we kind of work
around this issue by pooling mars commands but still expensive if you don't
used the pooled functionality. On the network layer there is a cost
associated with multiplexing the tds buffer, opening too many batches can be
more expensive than opening another connection. On the server all MARS
batches run in the same scope, worst case scenario your queries will end up
running in the order received.

I really like the current way to use ado.net, open a new connection in each
thread and rely on pooling to get performance and ease of development. It is
tempting with the advent of mars to switch this model to one where we open
one connection and rely on using a different command in each thread, I would
not recommend it. Misusing (IMO) MARS to try to get additional performance
has the potential of making your code look like a bowl of spaghetti for very
unclear performance benefits.

My opinion: Don't try to use MARS and threading to improve performance.

"

The last paragraph seems to say that, yes, it is thread safe, but please do
not use it.
And I do agree. Connection pooling is way more efficient.

Laura
> Hi Daniel,
>
[quoted text clipped - 8 lines]
>> a System.Data.SqlClient.SqlConnection instance without any
>> synchronization?
Joerg Jooss - 22 Mar 2006 19:42 GMT
Thus wrote Laura T.,

> I think it is thread safe.. sort of, as now it supports MARS:

No. MSDN clearly states that it isn't, and the availability of MARS has little
to do with the question of thread-safety.

Cheers,
Signature

Joerg Jooss
news-reply@joergjooss.d

Laura T. - 23 Mar 2006 11:31 GMT
Yes, MSDN clearly states that instance methods are not thread safe.
But since the object can be used by many threads contemporarly, MARS, it
should has some safeguards.
Connection can be common for many data readers.
It still does not make connection class fully thread safe.

Laura

Laura
> Thus wrote Laura T.,
>
[quoted text clipped - 3 lines]
> little to do with the question of thread-safety.
> Cheers,
Joerg Jooss - 23 Mar 2006 22:04 GMT
Thus wrote Laura T.,

> Yes, MSDN clearly states that instance methods are not thread safe.
> But since the object can be used by many threads contemporarly, MARS,
> it
> should has some safeguards.

Why? MARS allows you to open multiple DataReaders over a single connection.
That's it. The assumption that this connection therefore can be shared among
multiple threads is dangerous one.

Cheers,
Signature

Joerg Jooss
news-reply@joergjooss.de

Brian Gideon - 24 Mar 2006 02:01 GMT
Laura,

The ability to use multiple active result sets does not make a
SqlConnection object thread-safe.

Brian

> Yes, MSDN clearly states that instance methods are not thread safe.
> But since the object can be used by many threads contemporarly, MARS, it
[quoted text clipped - 3 lines]
>
> Laura

Rate this thread:







Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.