This content has been marked as final. Show 2 replies
Ok, first, I think your definitions are a bit off.
There is the concept of a lock, or an enqueue. The terms 'lock' and 'enqueue' are synonymous, in Oracle.
A lock (or enqueue) protects a 'resource'. A TX (transaction) enqueue protects rows which have been locked. Only one transaction is permitted to modify a specific row in a specific table, at a time, for obvious reasons.
A row-level lock occurs when a session attempts to modify a row that another session has already locked. When that occurs, the session attempting the lock will wait on a TX enqueue.
These types of locks occur all the time in Oracle, and are not necessarily a bad thing. They are a sign that Oracle is protecting the integrity of your data, and that's a good thing.
However, when waits on row-level locks begin to dominate the response time of your application, then you have a problem. Generally, this is going to come down to your application design. How can you avoid concurrent sessions colliding on their updates to specific rows? This is something that only you, with your knowledge of your application, can answer.
Finally, you mentioned the term 'deadlock' before. A deadlock occurs when two or more sessions are simultaneously holding a lock that the other is waiting on, while waiting on a lock the other is holding.
A simple example would be as follows:
Consider table_a, with row1 and row2.
Session 1 takes a lock on row1, no problem.
Session 2 takes a lock on row2, no problem.
Now, session 1 attempts to take a lock on row2, but session 2 has lock, so it waits.
Now, session 2 attempts to take a lock on row1, but session 1 has lock, so it waits.
This is a deadlock. It would wait forever. But, the Oracle kernel has a deadlock detection mechanism. So, within 3 seconds, Oracle will detect a deadlock, and one of the sessions (usually the one that has been waiting the longest) will catch ORA-00060 deadlock detected, and statement level rollback will occur.
Hope that clarifies your questions and/or doubts,