I am working on Oracle 10.2.0.4 on AIX .
In awr reports we found 'row lock contention' as top wait events. As my knowledge There are two types of locks..
1. Deadlock - where oracle will automatically rollback the locking query and generate deadlock trace file.
2. A user session update some rows and not commit/rollback , so other sessions which need to lock same rows , were witing. So dba need to manully kill first
session which was holding lock.
We want to know , in which of above scenario in awr report we can see 'row lock contention' wait.
any idea ..
Both, realistically. If you have a deadlock, that must, by definition, start will a case where one session holds a lock that another session requests.
If you're getting a deadlock, you'll see ORA-00600 errors in your alert log and deadlock trace files in your dump directory.
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,