What happened to SqlConnection.BeginOpen?

[Chris Mullins]

We're building some very high performance database applications, and are
trying to use all of the new Async ADO.Net 2.0 features.

The problem we're having is that we can't find a
SqlConnection.BeginOpen/EndOpen method. We can execute everything using the
BeginExecuteCommand, but without the BeginOpen on the connection we end up
with lots of deadlocked threads, all waiting for SqlConnection.Open.

We looked briefly into MARS for doing this, as that might be an option, but
we really prefer the more standard Async stuff as it maps much easier into
working with other database provides such as Oracle. To do this with MARS,
we would still need this BeginEnd methodology.

What's frustrating is that early Whidbey builds, and many old ADO.Net 2.0
articles, refer to these methods so they existed at one point. These methods
were obviously removed during the .NET 2.0 RTM process, which seems really
strange.

Any ideas how to work around this and actually do highly scalable I/O using
the Async infrastructure in ADO.NET 2.0?

 

[William \(Bill\) Vaughn]

Nope, if it ever existed, it was dropped. There is no async open in ADO.NET
(at least not yet).
Open should return very quickly once the connection is initially opened and
closed. If the pool is full, you'll block (and eventually timeout) while
waiting for a connection. You haven't created your own pooling mechanism
have you?

I don't endorse MARS. It causes more problems that it fixes--at least in my
experience.

Never depend on early builds, beta or CTP docs or feature sets. They can
(and always are) pared down prior to RTM.

I have a long section in my new book on Async ops that might help... it'll
be out in early November--that's only 5 weeks away.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[Chris Mullins]

Hi Bill,

Nope, if it ever existed, it was dropped. There is no async open in
ADO.NET (at least not yet).

Open should return very quickly once the connection is initially opened
and closed.

The issue we're having is that we're a high-throughput server handling
thousands of requests per second that come in over Windows Sockets. All of
our operations are running on IOCP callback threads.

The "block once we hit the limit" means we can never queue up transactions
beyond the size of the ADO.NET connection pool - which just isn't an option.
We handle LOTS of transactions per second right now...

We currently do ADO.NET synchronously, as no databases except SQL Server
support Async I/O with ADO.Net. This means we play quite a few games in
terms of queuing up work requests and managing the number of parallel
operations. We want this managment code to disappear, as it causes
un-necessary context switches and means we have more threads running than we
really should.

We hit a brick wall though due to the lack of an async way to open a
database connection - this means that once we need to hit the DB we're stuck
blocking for an indefinite amount of time once the connection pool is
exhausted.

As a high performance Socket Server using IOCP, this means we very quickly
get lots of threads (about 1000) blocked waiting for a connection to open.
This sucks, and we can't figure out a way around it. In terms of moving to
async, we need to be all-or-nothing. It doesn't do any good to be async in
95% of the places if that last 5% causes our threads to stall...

If the pool is full, you'll block (and eventually timeout) while waiting
for a connection.

Yup. This is why I really want the "BeginOpenConnection" method or a
workaround. I need a way to put my thread back into the IOCP pool while we
wait for a DB Connection to become available. When the connection becomes
available, a callback (preferable on an IOCP thread) would then happen and
we could continue processing the reqeust.

You haven't created your own pooling mechanism have you?

In order to work around this, I'm thinking about it.

We've already got a rich connection management infrastructure for managing
non ADO.Net server connections (mostly connections to Active Directory,
LDAP, and NTLM servers), and I was thinking of extending that for use with
ADO.Net.

I could create an IOCP Port, and bind the connections to that so that as
they were checked into and out of the pool, I could perform the callbacks I
was describing earlier.


--
Chris Mullins, MCSD.NET, MCPD:Enterprise
http://www.coversant.net/blogs/cmullins

I don't endorse MARS. It causes more problems that it fixes--at least in
my experience.

Never depend on early builds, beta or CTP docs or feature sets. They can
(and always are) pared down prior to RTM.

I have a long section in my new book on Async ops that might help... it'll
be out in early November--that's only 5 weeks away.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[William \(Bill\) Vaughn]

While there is no built-in support for an async open, you can fake it with a
BackgroundWorker thread. However, I doubt if that will solve your problem.
Once the pool is full, it means that there are no more connections to
use--of course you can bump the size of the pool. If you system can handle
the load, you can make the pool virtually any size.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

Hi Bill,

Nope, if it ever existed, it was dropped. There is no async open in
ADO.NET (at least not yet).

Open should return very quickly once the connection is initially opened
and closed.

The issue we're having is that we're a high-throughput server handling
thousands of requests per second that come in over Windows Sockets. All of
our operations are running on IOCP callback threads.

The "block once we hit the limit" means we can never queue up transactions
beyond the size of the ADO.NET connection pool - which just isn't an
option. We handle LOTS of transactions per second right now...

We currently do ADO.NET synchronously, as no databases except SQL Server
support Async I/O with ADO.Net. This means we play quite a few games in
terms of queuing up work requests and managing the number of parallel
operations. We want this managment code to disappear, as it causes
un-necessary context switches and means we have more threads running than
we really should.

We hit a brick wall though due to the lack of an async way to open a
database connection - this means that once we need to hit the DB we're
stuck blocking for an indefinite amount of time once the connection pool
is exhausted.

As a high performance Socket Server using IOCP, this means we very quickly
get lots of threads (about 1000) blocked waiting for a connection to open.
This sucks, and we can't figure out a way around it. In terms of moving to
async, we need to be all-or-nothing. It doesn't do any good to be async in
95% of the places if that last 5% causes our threads to stall...

If the pool is full, you'll block (and eventually timeout) while waiting
for a connection.

Yup. This is why I really want the "BeginOpenConnection" method or a
workaround. I need a way to put my thread back into the IOCP pool while we
wait for a DB Connection to become available. When the connection becomes
available, a callback (preferable on an IOCP thread) would then happen and
we could continue processing the reqeust.

You haven't created your own pooling mechanism have you?

In order to work around this, I'm thinking about it.

We've already got a rich connection management infrastructure for managing
non ADO.Net server connections (mostly connections to Active Directory,
LDAP, and NTLM servers), and I was thinking of extending that for use with
ADO.Net.

I could create an IOCP Port, and bind the connections to that so that as
they were checked into and out of the pool, I could perform the callbacks
I was describing earlier.

 

[Chris Mullins]

While there is no built-in support for an async open, you can fake it with
a BackgroundWorker thread.

Queuing things to a background worker thread won't do any good. This is (by
default) limited to 25 threads per processor and if we manage to block all
the threads in this threadpool we end up having a hung application.

I've been down this road before:
Don't use the System.Threadpool blog:
http://www.coversant.net/dotnetnuke/Default.aspx?tabid=88&EntryID=8

of course you can bump the size of the pool. If you system can handle the
load, you can make the pool virtually any size.

The problem is that there's no number I can enter here as the size and be
confident that we won't have threads blocking. 1000? probably too small. I
can easily see us in high load conditions wanting to queue up 1000 db
requests. 10k would probably be safe, but that's an awful lot of resources
to suck up for Sql Connections....

I appriciate you taking the time to respond, but it doesn't sound like
there's a good answer out there.

Do you know any ADO.Net / SqlClient team members I can ask about this?

 

[William \(Bill\) Vaughn]

Ok, what you're describing here is more like a MSMQ problem. I might
approach the problem from this angle. This gives you a way to submit N
requests and have guaranteed delivery and execution at the pace set by the
capacity of the server. Yes, the team monitors this alias from time-to-time.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[Chris Mullins]

I agree, at it's root this is fundamentally a queueing problem.

In general, the Async patterns used throughout the .Net APIs are excellent
as solving this queueing problem - especially when they're paired with I/O
Completion Ports. IOCP was specifically developed by Microsoft to solve the
I/O Queue issue in a very efficient way, and the results that are obtained
when using IOCP are excellent. Sockets follow this pattern, File I/O follows
this pattern, and all of the SqlClient stuff (except Connection.Open)
follows this pattern.

At this point I'm pretty sure I'm going to write my own Sql Connection Pool,
and put together an async Queue on top of it. The BeginOpen will check out a
connection from the queue, and everything will proceed normally from there.

I would love to hear from a Microsoft SQLClient developer at regarding why
they took this out when .Net 2.0 went RTM. I'm hoping there's not a subtle
hidden gotcha that I'm missing that will end up cause me to waste a few days
of development time.

 

[William \(Bill\) Vaughn]

I asked this question (why Async open was removed) but the answer
was...complex. Yes, they found some hidden issues that made it impossible to
implement. I would try to get mail to Pablo Castro at MS. He's the go-to guy
in the ADO.NET team that understands all of the issues here. He sometimes
monitors this alias and I think he watches his blog.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[Chris Mullins]

Thanks Bill.

I can't find Pablo's email address, but I sent email to some of the more
prolific Microsfot ADO.Net team bloggers. Hopefully I'll get a good answer.