Oracle Locks   «Prev  Next»
Lesson 2 Overview of Oracle locking
ObjectiveDescribe 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 Slide Show below illustrates this scenario.

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
  1. with a COMMIT,
  2. ROLLBACK, or
  3. 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.