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.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
