D
Daniel
are System.Data.SqlClient.SqlConnection thread safe? can many threads share
a System.Data.SqlClient.SqlConnection instance without any synchronization?
a System.Data.SqlClient.SqlConnection instance without any synchronization?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
are System.Data.SqlClient.SqlConnection thread safe? can many threads share a System.Data.SqlClient.
D
Dmytro Lapshyn [MVP]
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.
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.
L
Laura T.
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
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
J
Joerg Jooss
Thus wrote Laura T.,
No. MSDN clearly states that it isn't, and the availability of MARS has little
to do with the question of thread-safety.
Cheers,
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,
L
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.
Connection can be common for many data readers.
It still does not make connection class fully thread safe.
Laura
Laura
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
J
Joerg Jooss
Thus wrote Laura T.,
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,
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,
B
Brian Gideon
Laura,
The ability to use multiple active result sets does not make a
SqlConnection object thread-safe.
Brian
The ability to use multiple active result sets does not make a
SqlConnection object thread-safe.
Brian