VB / ASP.net - SQL Server Deadlock

[sirjayrod]

I have been making changes to an app that has already been released. When I released the new version, SQL server suffered a DeadLock (EventID: 2262). I have tried to replicate this error in the testing environment, but so far have been unsuccessful. On top of this, my app seems to stall (Hang / freeze) after about 5 minutes of use. After further review of my app, I see that when the app freezes, it is freezing on a connection and th only error I get is a timout error. I have changed my connection time out to 500000 to see if it really was a timeout error, but it still did not recover. I am asking if anyone has an idea on hoe to troubleshoot a bug that does not produce a valid error. Also, if anyone knows how to troublshoot a Deadlock error I would like to hear about that too.



Thank you all.

[jhermiz]

I have been making changes to an app that has already been released. When I released the new version, SQL server suffered a DeadLock (EventID: 2262). I have tried to replicate this error in the testing environment, but so far have been unsuccessful. On top of this, my app seems to stall (Hang / freeze) after about 5 minutes of use. After further review of my app, I see that when the app freezes, it is freezing on a connection and th only error I get is a timout error. I have changed my connection time out to 500000 to see if it really was a timeout error, but it still did not recover. I am asking if anyone has an idea on hoe to troubleshoot a bug that does not produce a valid error. Also, if anyone knows how to troublshoot a Deadlock error I would like to hear about that too.



Thank you all.

You tried to reboot the sql server ?

[sirjayrod]

You tried to reboot the sql server ?

Yes. We had to reboot because it was frozen. 5 minutes later, it was deadlocked again. And if the DB doesn't deadlock, then the application freezes on connection.open.

Any ideas?

[jhermiz]

Refresh me, what do you want me to answer? Do you want an idea on how to prevent / minimize deadlocks. Do you still have problems with the server?

What OS, what server environment, ???

[jhermiz]

Yes. We had to reboot because it was frozen. 5 minutes later, it was deadlocked again. And if the DB doesn't deadlock, then the application freezes on connection.open.

Any ideas?
You will need to provide details on your application? Cursors might be the cause, do you use cursors in your application?

http://www.sql-server-performance.com/deadlocks.asp

Check that you should be able to find the answer to your problems.

[sirjayrod]

Refresh me, what do you want me to answer? Do you want an idea on how to prevent / minimize deadlocks. Do you still have problems with the server?

What OS, what server environment, ???

Win 2003 / SQL Server 2003

I guess the bigger issue is the connection.open. after runnint the app, updating, deleting, inserting, and just generally moving around, it will lock up on a connection.open and the only error I get it a timeout error. I ahve increased my timeout to 500000, but it still happens and I can't figure out why. There is not errors being thrown on the .NET side, and no errors on the SQL Server side either. The deadlock issue seems to only come up when multiple people and high traffic begin to use the app.

One cursor is being used and I Close and deallocate it when I am done with it. I have even gone as far as to not use it thinking that may be the cause.

I would like to know a way, if there is one, to get this app to throw an error as to why the connection is hanging, then move on to figuring out the issue with the deadlock.

[jhermiz]

Win 2003 / SQL Server 2003

I guess the bigger issue is the connection.open. after runnint the app, updating, deleting, inserting, and just generally moving around, it will lock up on a connection.open and the only error I get it a timeout error. I ahve increased my timeout to 500000, but it still happens and I can't figure out why. There is not errors being thrown on the .NET side, and no errors on the SQL Server side either. The deadlock issue seems to only come up when multiple people and high traffic begin to use the app.

One cursor is being used and I Close and deallocate it when I am done with it. I have even gone as far as to not use it thinking that may be the cause.

I would like to know a way, if there is one, to get this app to throw an error as to why the connection is hanging, then move on to figuring out the issue with the deadlock.

Trouble shooting deadlocks is not a simple task, it could fail on any object without returning an error code. You have to replicate it to see how this has happened, sometimes that is not easy, but you will have to do some looking around as to where and when it happens. It might be a specific procedure or function that is calling this.

Jon

[sirjayrod]

Trouble shooting deadlocks is not a simple task, it could fail on any object without returning an error code. You have to replicate it to see how this has happened, sometimes that is not easy, but you will have to do some looking around as to where and when it happens. It might be a specific procedure or function that is calling this.

Jon

Thanks Jon...

Have any good way to get .NET to return an error on a connection.open freeze? Any idea what may be causing that to happen?

[Magiaus]

Have you checked to see if you are caught in a loop or a recursive loop. My App kept locking up once and I had a recrusive call to a method in it's self. I had gone in to quickly fix a glitch and put the call in the wrong spot. I don't remember if it hit SQL Server but it locked the web server.

[sirjayrod]

Have you checked to see if you are caught in a loop or a recursive loop. My App kept locking up once and I had a recrusive call to a method in it's self. I had gone in to quickly fix a glitch and put the call in the wrong spot. I don't remember if it hit SQL Server but it locked the web server.


Magiaus,

That is one of the first things I checked and the answer is no...not as far as I can tell. The changes I made were mostly Views, sprocs and displaying it to the screen. Nothing to complicated. The thing is that this happens intermitnly and the server doesn't lock, its the connection.open on the app that freezes.

Thanks for your advice, but unfortunatly, that is a raoad I have already been down and it has produced nothin' :(

thanks again.