Lesson 2	Overview of Oracle locking
Objective	Describe Oracle's locking scheme

Overview of Oracle Locking

Whenever an Oracle data row is accessed with the intent to update the row, you may want to ensure that none of the row data changes during the transaction. This is especially important if the data values in the row are used to make the decision about the change. For example, you may want to update the pay_raise column of an employee based upon his or her prior performance. If some other user were to change the pay_raise while you were looking at the customer, you might update the customer based upon outdated information. The following series of images below illustrates this scenario.

Need for Database Locks

Boss A requests the "Smith" employee row.

The Boss sees that Smith's performance rating is a "B" and prepares to give Smith a 5% pay raise..<br>
Boss B accesses the "Smith" employee row. — 2) The Boss sees that Smith's performance rating is a "B" and prepares to give Smith a 5% pay raise..
Boss B accesses the "Smith" employee row.

3) Boss B changes Smith's performance_rating to "10" (making Mr. or Ms. Smith deserving of a 10% raise.)

4) Boss A, is unaware that Boss B has changed the performance_rating and gives Smith a 5% raise.

To prevent this, we could instruct Boss A to obtain the employee rows with a lock:


Select
   Employee_stuff
From
   Employee
Where
   Employee_name = ‘SMITH’
FOR UPDATE OF EMPLOYEE;

In this case, Oracle will hold a lock on this row inside the shared pool. Only after Boss A has released the lock

with a COMMIT,
ROLLBACK, or
normal end-of-task

will the lock be released.
Now that you understand the basic purpose behind Oracle locks, let's take a look at the types of Oracle locks.

Core Concepts of Oracle Locks

Oracle's locking mechanism ensures data consistency and prevents destructive interactions in concurrent environments. The following core concepts have remained consistent across Oracle versions, from 8i to 23ai:

DML Locks (Data Locks): Protect table and row data during Data Manipulation Language (DML) operations, such as INSERT, UPDATE, and DELETE.
Table Locks (TM): Automatically acquired for DML operations to protect table metadata, ensuring structural integrity during modifications.
Row Locks (TX): Acquired on specific rows to prevent concurrent modifications, allowing fine-grained control over data changes.
DDL Locks: Protect database objects during Data Definition Language (DDL) operations, such as ALTER TABLE, to prevent conflicting structural changes.
System Locks: Manage internal database resources, such as latches and mutexes, to ensure efficient operation of the database engine.
Multiversion Concurrency Control (MVCC): Ensures that readers do not block writers and writers do not block readers (except in cases like SELECT ... FOR UPDATE), using undo segments to provide consistent data snapshots.

Detecting Lock Conflicts

Detecting locks in Oracle 23 ai using SQL makes your job easy. Here is an SQL example to detect locks.


SELECT
  s.sid, s.serial#, l.type AS lock_type,
  l.mode_held, l.mode_requested,
  o.object_name, o.object_type, o.owner
FROM
  v$session s
JOIN
  v$locked_object lo ON s.sid = lo.session_id
JOIN
  dba_objects o ON lo.object_id = o.object_id
JOIN
  v$lock l ON l.sid = s.sid
WHERE
  s.username = 'SCOTT';