Reproduced ASP. Error–adding the specified count to the semaphore would cause it to exceed it maximum count

http://jwcooney.com/2012/08/13/ asp-net-error-adding-the-specified-count-to-the-semaphore-would-cause-it-to-exceed-its-maximum-count/

Justin COONEY/2012/08/13

If you were working with ASP. NET in Visual Studio, and then you could mystified when you see an error code: Adding the SP Ecified count to the semaphore would cause it to exceed its maximum count.

This error had me wondering what exactly are going on ...

Identifying the problem

At first if I saw this error, I thought it might is due to the syntax of the A was running. However after starting up query Profiler I is able to run the query directly in SSMS without any issues. The problem clearly is somewherein the application and isn't with the SQL query.

After the investigating this, it appears the IT related to ASP. NET ' s ADO Connection pool manager. When you put together a connection string in your Web. config file, you probably won ' t is thinking about connection pooling , but by default this would be enabled. The connection pool manager would attempt to find a free connection, but if none is available it'll throw the error we s Aw.

What's a Semaphore in SQL Server?

The first question I asked myself was what a semaphore are since I had not heard this term used before. Well, it seems the definition of semaphore was just a locking mechanism used by SQL Server behind the scenes to Preven T more than one task from accessing a data structure, that's currently in use. In particular SQL Server protects user log caches using semaphores.

What's the cause of this problem?

So, was pretty interesting information. Basically with the error being returned, it appears that some sort of a lock is being retained and not released when my V b.NET application is communicating with SQL Server. It is definitely not a problem with long running queries or forgetting to clean up the connection object after execution was complete.

I ' m still not able to pin down exactly what is causing this error to happen. People online speculate that similar problems appear to being caused by network issues. However, when I encountered the problem there were no noticeable network problems though, so I doubt that the network was Causing the problem in my case.

Bottom line is, the semaphore locking issue appears to being related to ASP. NET ' s connection pooling. Resources were being locked in the pool and then were not being released, so I would see the semaphore error when my appli Cation was trying to access the application pool and no free resources were available.

Why is Connection pools used by Default?

So since ADO connection pools seem to being a possible point of failure, the question remains:why is connection pools E Nabled by default? The answer is performance.

There is a high performance hits involved with establishing a connection with a database, so ADO tries to increase perf Ormance by does destroying connections after a call have happened to a database. Rather, ADO puts the released connection into a pool which it holds for the next time which a request to the database I S made. In the ends up returning database results much faster than if connection pooling are disabled.

Of course we have also seen the down-side to connection pooling, which is the semaphore error where ADO tries to acces s a connection in the pool, and finds that it can ' t.

In the case of my application I decided so the possible performance improvement gained by using connection pooling was O Utweighed by the possibility of getting ugly connection errors such as the semaphore error, so next I'll explain how to ' Fix ' The semaphore error by disabling ADO connection pooling.

Fixing the problem

The simplest to fix the ADO semaphore error is to disable connection pooling in the connection string of your web. Config file.

Here's an example of what a default connection string might look like. Although it doesn ' t specify a connection pooling option, this is enabled by default:

<add name= "testconnection" connectionstring= "Data source=mydbserver;initial catalog=mydatabase; Persist Security info=true; User Id=testuserid; Password=testpassword; "Providername=" System.Data.SqlClient "/>

Now to disable pooling and get rid of the error message we were seeing, we simply append the directive pooling=false to th e End of our connection parameters as follows:

<add name= "testconnection" connectionstring= "Data source=mydbserver;initial catalog=mydatabase; Persist Security info=true; User Id=testuserid; Password=testpassword;  pooling=false;"providername=" System.Data.SqlClient "/>

Pretty, right?

Sometimes finding the reason for a problem are more difficult than fixing the problem. Now which I know what was causing the error I'll find it easier to diagnose similar problems in future!

Reproduced ASP. Error–adding the specified count to the semaphore would cause it to exceed it maximum count