Pooled Connections are not a new concept of SQL Client. It reuses pool of opened connections for any new T-SQL requests. It provides a logically fresh new connection to application as you physically open a new connection. The fact behind it is that when you close a pooled connection, the connection is merely returned back to the pool without physically closing the tunnel(SQL Server doesn’t know the connection is closed by the client), and, when you open a new connection, SQL Client will open a new connection if there is no available connections in the pool. Otherwise SQL Client will get the connection from the pool. In this case, SQL Server still does not know that the connection is assigned for new purposes. Whenever you run the first command through a connection which assigned from the pool, SQL Client will “inject” execution of sp_reset_connection before the execution of your SQL statement or procedure to reset resources and mimik a brand new connection. A lot of discussions regarding sp_reset_connection in the internet. I will excerpt them at the end of this blog post. Apart from that, you must know few other things about it.
Pooled Connection
Deadlock in Pooled Connections
When we talk about deadlock, most of time we thought it’s caused by incorrect resource accessing. That’s correct. Then you dive into your application code and hopefully find a root cause in T-SQL. In some situation, the dead lock happens in a random manner. The place even you think that it’s impossible to get a deadlock gets deadlock. In this case, you need to check whether you are using pooled connection properly.