PL/SQL Transactions   «Prev  Next»

Lesson 4Locking levels and modes
ObjectiveIdentify when each type of locking is used.

Locking Levels Modes (PL/SQL)

When data is modified in an Oracle database, the Oracle system takes precautions to ensure that no other user makes changes to a row that is being modified by the current transaction. The mechanism used is called a lock. Locks allow you to control the availability of rows or tables to other users during the time in which your program is working with the rows or tables.

Locking levels and modes

Locks can be imposed on different levels and with varying severity, or modes. The following table describes each combination available to you in PL/SQL.
Level --> Mode Row Table
Exclusive Implicit or Explicit Explicit
Share Implicit or Explicit Explicit

PL/SQL Lock Level

The lock level determines the scope of the lock. Locks can be imposed on either the table or row level. A table level lock can only be created explicitly using the LOCK TABLE command. A row level lock is created implicitly when you modify a row in a table. Row level locks can also be created explicitly.
The lock mode determines the severity of the lock. Locks can be imposed on either the share or exclusive mode. Share mode gives you a consistent view of the row or table, even if someone else changes it after you lock it. Exclusive mode prevents others from modifying the row or table that you have locked.

Implicit Locks

An exclusive row-level lock is implicitly created on a row when you either update or delete it. The lock prevents all other users from updating or deleting the same row. The lock remains in effect until released either by a COMMIT command, a ROLLBACK command, or an action that implicitly causes a COMMIT or a ROLLBACK to occur. All DDL commands implicitly COMMIT your transaction. Exiting SQL*Plus implicitly either commits or rolls back, depending on system settings.

The vast majority of the time, allowing Oracle to handle locking with its implicit locking mechanism is sufficient.

Explicit Locks

When you need special locking, you can explicitly impose locking with either of these methods:
  1. CURSOR with FOR UPDATE clause. When creating a cursor that queries data that is updated later in the same procedure, add the FOR UPDATE clause. This locks all the rows that are queried, preventing updates by other users as long as your procedure is running.
  2. LOCK TABLE command. This allows you to impose certain types of locking on an entire table.

The next lesson describes how to create a cursor that performs explicit locks on rows.

Locking Levels Modes - Exercise

Click the Exercise link below to match terms and definitions about transactions and locks.
Locking Levels Modes - Exercise