Database Lockup

Question

Database Lockup

jimkern

Mr or Mrs. 500

Points: 591
More actions
December 7, 2010 at 3:29 am

#146398

I have what I consider to be a very busy SQL server database, with an average of 150 transactions per second. We use mirroring for redundancy. We have quite a few different "agent" applications all hitting the database, primarily using a few tables, including our primary subscriber table for our service.
What we are seeing is that sometimes we get errors indicating that a process "was chosen as the victim of a deadlock." This is GREAT and we have no problem dealing with this error by re-running the transaction.
What we are also seeing is that sometimes we get transactions that completely deadlock and are NOT automatically killed by SQL, essentially locking the database, or at least, the tables we use most often, until we take steps to manually intervene.
Primarily, it is one process we are seeing that is the culprit, which used to have a cursor that has since been removed. With the cursor in place, this was happening about 4-5 times per week. Since removing the cursor about 5 weeks ago, it has only happened once in total, so it's significantly better without the cursor.
Q: Why is it that sometimes SQL detects the deadlock and other times it does not, allowing the database to completely lock up?
Jim

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Are you certain it's a deadlock not just a long-lasting blocking condition?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

Are you sure that it caused deadlock? Could it be that it causes massive blocking but not deadlock? Can you let us know how you came into conclusion that it did cause deadlock?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

jimkern Mr or Mrs. 500 Points: 591 More actions · Answer 3

The only thing that I can say is that I've seen it last for as much as 2 hours in the past - we typically don't tend to let it wait that long, given that our system is "down" while it is locked up. To me, it definitely "appears" deadlocked.

Jim

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

A deadlock is a very specific condition. It's when two (or more) processes are in a state where each is waiting for resources the other has locked. It's a state that is permanent unless outside action is taken

I suspect what you have is long-lasting blocking. Lots of processes waiting for locks, but not in a state that's unresolvable, just one that takes longer than acceptable to resolve. The sQL deadlock detector looks for deadlocks and only deadlocks, not long lasting blocking.

I've seen blocking on a large DB with bad code that took the DB virtually down, it happens. Blocking chains 15 processes long, taking hours to resolve by itself (typically only resolving once users went home). SQL won't kill processes unless it detects an actual deadlock - a lock/wait loop that will never resolve itself.

To resolve blocking, you need to identify what's at the beginning of the blocking chain and look at optimising it to take less locks or run faster or both. Start with the sys.dm_exec_requests DMV, the column blocking_session_id will help you navigate the block chain

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jimkern Mr or Mrs. 500 Points: 591 More actions · Answer 5

Wanted to follow up here...looks like you were correct in saying that it was not a deadlock (no surprise there. 🙂 )

We believe that a stored procedure is somehow leaving a transaction open (no commit or rollback) and causing the condition. We have duplicated the problem in a trivial database by beginning a transaction, inserting to a table and leaving it open, then attempting to select from the same table.

Now we have to figure out where the problem is in the offending stored procedure and fix it.

Thanks for the help.

Jim