Performance Engineering Interview Questions #13

Following are the Performance Engineering Interview Questions Set #13.

Q. 61 How can lock contention be identified in an AWR report?

Ans: High wait events like “enq: TX- row lock contention” or “enq: TM- contention” indicate lock waits. The “Segments by Row Lock Waits” section shows which segments are involved.

Q. 62 What does the wait event ‘enq: TX- row lock contention’ signify?

Ans: It indicates that a session is waiting for a row-level lock held by another transaction, often due to uncommitted changes or long-running transactions holding locks.

Q. 63 How can you determine which sessions or transactions are causing lock contention?

Ans: Use the ‘Active Session History’ (ASH) data in the AWR report, focusing on sessions with high wait times for lock-related events. Also, check the ‘Blocking Sessions’ section if available.

Q. 64 What strategies can be employed to reduce lock contention identified in an AWR report?

Ans: Optimize application logic to commit transactions promptly, avoid unnecessary locks (e.g., SELECT FOR UPDATE when not needed), and consider using row-level locking appropriately.

Q. 65 How does indexing influence lock contention?

Ans: Proper indexing can reduce the number of rows locked during DML operations by narrowing down the affected rows, thus minimizing contention.