How do I safely use ADO.NET IDbConnection and IDbCommand to execute multiple database commands concurrently?

Question

The Goal

Use an ADO.NET IDbConnection and IDbCommand to execute multiple commands at the same time, against the same database, given that the ADO.NET implementation is specified at runtime.

Investigation

The MSDN Documentation for IDbConnection does not specify any threading limitations. The SqlConnection page has the standard disclaimer saying "Any instance members are not guaranteed to be thread safe." The IDbCommand and SqlCommand documentation is equally un-informative.

Assuming that no individual instance member is thread-safe, I can still create multiple commands from a connection (on the same thread) and then execute them concurrently on different threads.

Presumably this would still not achieve the desired effect, because (I assume) only one command can execute at a time on the single underlying connection to the database. So the concurrent IDbCommand executions would get serialized at the connection.

Conclusion

So this means we have to create a separate IDbConnection, which is ok if you know you're using SqlConnection because that supports pooling. If your ADO.NET implementation is determined at runtime, these assumptions cannot be made.

Does this mean I need to implement my own connection pooling in order to support performant multi-threaded access to the database?

Answer 1

If you create a connection on one thread, you shouldn't use it on a different thread. The same goes for commands.

However, you can create a connection on each of your threads and use those objects safely on their own thread.

Pooling is for when you create lots of short-lived connection objects. It means the underlying ( expensive ) database connections are re-used.

Nick

Answer 2

You will need to manage thread access to your instance members, but most ADO implementations manage their own connection pool. They generally expect that multiple queries will be run simultaneously.

I would feel free to open and close as many connections as is necessary, and handle an exceptions that could be thrown if pooling were not available.

Here's an article on ADO connection pooling