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?
Add a comment
|
3 Answers
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.
-
+1, especially for the advice about seeing what waits are eating the time.– DCookieSep 17, 2009 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.
-
"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? Sep 18, 2009 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.
