nerdexam
Oracle

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.

Managing Data and Concurrency

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)
  • A
    89% (42)
  • C
    9% (4)
  • D
    2% (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.

AAvoid coding unnecessary longrunning transactions.Correct

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.

BAllow the database to handle locks in default locking mode.Correct

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.

CAlways explicitly code the locks as per the requirement of the application.

Explicitly coding lock statements (such as SELECT FOR UPDATE) beyond what the application truly requires introduces additional contention and increases the risk of deadlocks.

DAllow escalation of row locks to block locks if too many row locks cause problem.

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

#locking best practices#row locks#long-running transactions#concurrency control

Community Discussion

No community discussion yet for this question.

Full 1Z0-052 Practice