1Z0-052 · Question #195
Which two statements describe good practices for an application developer to reduce locking conflicts in Oracle database? (Choose two.)
The correct answer is A. Avoid coding unnecessary longrunning transactions. B. Allow the database to handle locks in default locking mode. Reducing lock contention in Oracle is best achieved by keeping transactions short and trusting Oracle's optimized default row-level locking rather than adding manual lock management.
Question
Which two statements describe good practices for an application developer to reduce locking conflicts in Oracle database? (Choose two.)
Options
- AAvoid coding unnecessary longrunning transactions.
- BAllow the database to handle locks in default locking mode.
- CAlways explicitly code the locks as per the requirement of the application.
- DAllow escalation of row locks to block locks if too many row locks cause problem.
How the community answered
(47 responses)- A89% (42)
- C9% (4)
- D2% (1)
Why each option
Reducing lock contention in Oracle is best achieved by keeping transactions short and trusting Oracle's optimized default row-level locking rather than adding manual lock management.
Long-running transactions hold row-level locks for extended periods, forcing other sessions to wait; keeping transactions as short as possible minimizes the lock hold time and reduces contention.
Oracle's default locking model uses fine-grained row-level locks combined with multi-version concurrency control (MVCC) so readers never block writers; this default behavior is already highly optimized and overriding it with explicit locks typically introduces unnecessary contention.
Explicitly coding lock statements (such as SELECT FOR UPDATE) beyond what the application truly requires introduces additional contention and increases the risk of deadlocks.
Oracle does not support lock escalation from row level to block or table level - that concept belongs to SQL Server; Oracle maintains row-level granularity regardless of the number of row locks held.
Concept tested: Oracle row-level locking and MVCC concurrency best practices
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-concurrency-and-consistency.html
Topics
Community Discussion
No community discussion yet for this question.