Oracle Locks   «Prev  Next»

Tuning Oracle locks

Oracle locks are used by the Oracle database to ensure that database rows are not inadvertently written-over by concurrent tasks.

Levels of Locking

Oracle locking is used at two levels:
  1. First, Oracle will automatically lock the target of all UPDATE statements for the duration of the task to ensure that no data inconsistency occurs.
  2. Second, Oracle programmers can explicitly lock row and tables with Oracle's LOCK TABLE and SELECT FOR UPDATE clauses.

In this module we will explore the internal constructs of Oracle locking and see how appropriate locking can improve Oracle performance. By the end of this module, you'll be able to:
  1. Describe Oracle's locking scheme
  2. Describe the purpose of shared and exclusive Oracle locks
  3. Describe Oracle lock modes
  4. Identify an Oracle deadlock
  5. Prevent a database deadlock
  6. List the dictionary lock views
  7. Run the lock utility scripts
  8. Use the dbms_lock package

Overview of Oracle Locking

An Oracle database contains two types of locks:
  1. shared and
  2. exclusive.
The most common type of locks are shared locks that are issued using SQL SELECT statements, and exclusive locks that are issued with DELETE and UPDATE statements.
In shared locking, whenever a unit of data is retrieved from the database, an entry is placed in the database storage pool.
Exclusive locks are issued for the duration of all 1) UPDATE or 2) DELETE statements to ensure that all modifications are single-threaded within the tables of the database. Oracle must obtain exclusive control of the segment header block whenever a row is inserted or deleted.

Size of Lock

The size of a lock is managed internally by Oracle and the lock will be held by the database until a COMMIT, END, or ABORT message releases the lock. Oracle's locking schemes use a coexistence method [1]. Many clients may have shared locks against the same database, but shared locks cannot coexist with exclusive locks. Whenever an update event occurs, the database attempts to post an exclusive lock against the target row. To understand the difference between shared and exclusive locks, assume that a task wants to lock a row for update. The task will need to wait until all shared locks are released for this row before it can issue the exclusive lock. The exclusive lock will wait if any other tasks hold a shared lock against the target row. We will begin by looking at Oracle's overall locking scheme.

[1]coexistence method: A technique for ensuring coexistence between two methods for access to the Oracle database.

Ad Oracle Database 12c Performance Tuning