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.

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.

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.

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.