|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 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.
To prevent this, we could instruct Boss A to obtain the employee rows with a lock:
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.