Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have a situation where a db is "slowing down". DB operations that normally take a second or two are taking ten times as long. There appear to be a large number of locks in the DB when this is happening. Could a large number of deadlocks be creating a DB wide slowness?

share|improve this question

3 Answers 3

up vote 3 down vote accepted

Deadlocks are a particular sort of situation where transaction A has a lock and then tries to lock something that is currently locked by transaction B and has to wait. Then transaction B tries to lock the item currently locked by transaction A. The database 'notices' that the situation can't resolve naturally so errors one of the transactions and forces it to rollback.

As others have stated, this is unusual and I'd expect you would have had reports of errors raised to the application regarding deadlocks.

Instead, I'm guessing that 'something' changed. It could be locks but equally it could be that you have much more stuff locked because transactions are taking longer (and therefore the locks are held for longer).

If you are on 10g/11g with AWR/ASH (Diagnostics/Tuning Pack), OEM should be able to help diagnose the situation.

If not, look at V$SESSION and see if you have lots of sessions with BLOCKING_SESSION set. That indicates they are waiting for locks. You only need to worry about locks if sessions are waiting on them, so use this rather than the number of locks. If that doesn't indicate a problem go down to v$session_event and see what wait_class entries are responsible for the bulk of the time.

share|improve this answer
    
+1, especially for the advice about seeing what waits are eating the time. –  DCookie Sep 17 '09 at 23:15

It could be deadlocks, but databases can usually tell when there are deadlocks, and will kill connections with a deadlock exception in that case.

I've seen this happen on my oracle database, and it can happen when resources are running thin, and the database is running out of memory and has to swap to disk.

This is especially true if a number of jobs are all waiting in queue for the same resource to free up.

share|improve this answer
    
"This is especially true if a number of jobs are all waiting in queue for the same resource to free up." -- is there any way to see what's queing up and what resource it's waiting on? –  jeffspost Sep 18 '09 at 13:45

Deadlock are recorded on the trace files so you can easily tell if this is the issue. First analyze for the usual suspects. Query explain plan, indexes, statistics and such.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.