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

Overview of Oracle Locking

Why lock?

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 our 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

1) 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.

Lock Example
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.