Let's prepare a demo to illustrate the usage of the FOR UPDATE clause in Oracle. This demo will show how to lock rows using FOR UPDATE, update the locked rows, and then commit the transaction. We will also demonstrate the usage of NOWAIT.
Step-by-Step Demo
1. Setup the Environment
First, let's create a sample table and insert some data.
-- Create the table
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER,
department_id NUMBER
);
-- Insert sample data
INSERT INTO employees (employee_id, first_name, last_name, salary, department_id) VALUES (100, 'John', 'Doe', 50000, 10);
INSERT INTO employees (employee_id, first_name, last_name, salary, department_id) VALUES (101, 'Jane', 'Smith', 55000, 10);
INSERT INTO employees (employee_id, first_name, last_name, salary, department_id) VALUES (102, 'Jim', 'Brown', 60000, 20);
COMMIT;
2. Using FOR UPDATE
Next, we will demonstrate how to lock a row for update.
-- Session 1: Lock the row with employee_id = 100
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE employee_id = 100
FOR UPDATE;
Now, the row with employee_id = 100 is locked by Session 1.
3. Attempt to Update Locked Row from Another Session
In a separate session (Session 2), attempt to update the locked row.
-- Session 2: Try to update the locked row
UPDATE employees
SET salary = salary + 2000
WHERE employee_id = 100;
-- This will hang until Session 1 commits or rolls back the transaction
4. Commit the Transaction in Session 1
Back in Session 1, commit the transaction to release the lock.
-- Session 1: Commit the transaction
COMMIT;
5. Verify the Update in Session 2
After the commit in Session 1, the update in Session 2 will proceed. Check the updated value.
-- Session 2: Check the updated value
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE employee_id = 100;
6. Using FOR UPDATE NOWAIT
Let's see how FOR UPDATE NOWAIT behaves when the row is already locked.
-- Session 1: Lock the row again
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE employee_id = 100
FOR UPDATE;
-- Session 2: Try to lock the same row using NOWAIT
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE employee_id = 100
FOR UPDATE NOWAIT;
-- This will immediately return an error indicating that the row is already locked
7. Using FOR UPDATE WAIT
Now, let's demonstrate how FOR UPDATE WAIT works.
-- Session 1: Lock the row again
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE employee_id = 100
FOR UPDATE;
-- Session 2: Try to lock the same row using WAIT
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE employee_id = 100
FOR UPDATE WAIT 10;
-- This will wait for up to 10 seconds for the row to become available
-- If Session 1 releases the lock within 10 seconds, Session 2 will proceed
-- Otherwise, it will return an error after 10 seconds
Conclusion
This demo illustrates how to use the FOR UPDATE clause to lock rows in Oracle, ensuring data consistency during transactions. It also shows how to handle scenarios where rows are already locked using the NOWAIT and WAIT options. This feature is particularly useful in applications where concurrent transactions need to modify the same set of data, ensuring that updates are done in a controlled and consistent manner.