Question: I understand that Oracle has
locks and that it is possible to get an Oracle deadlock, where one
task aborts. I also understand that there are transient
deadlocks, where a task waits for a resource to become free.
What are the different types of Oracle deadlocks, and how do I find
the source objects for an Oracle deadlock?
Answer: Oracle has a row level locking
mechanism that generates a lock on a resource when a transaction
starts. Any transaction beginning after that point that
requires the locked resource will have to wait for the completion of
the transaction that generated the lock. When the transaction
commits or rollbacks, it will release the exclusive lock. Once
the lock is released, the next transaction requests a lock so that
it may proceed.
There are several kinds of deadlock waits in Oracle:
- The
deadly embrace Oracle deadlock
occurs when a second transaction asks for resources that another
transaction is currently holding, and this other transaction is
waiting for a block held by the second transactions.
- In a
buffer busy wait deadlock,
a transaction deadlocks while waiting for a buffer to become
available. This occurs when an Oracle session needs to access a
block in the buffer cache but cannot because the data block in
the buffer is locked. This can happen if the block is being
read into the buffer by another session, or if another session
has the buffer block locked in a mode that is incompatible with
the waiting sessions request. For more information on identifying the
cause of buffer busy waits, you can read more in
this
helpful article.
- SQL has a lock for update clause that locks a set of rows
exclusively for the session that creates the lock. This
type of lock is especially dangerous when a transaction aborts
and a zombie process continues to hold row locks. There
are better locking strategies
available.
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright ? 1996 - 2012
All rights reserved.
Oracle ?
is the registered trademark of Oracle Corporation.
|
|
