Understanding SELECT FOR UPDATE In Oracle Database

for_update

The SELECT FOR UPDATE statement is a special variation of the normal SELECT statement, which proactively issues row locks on each row of data retrieved by the query. Use SELECT FOR UPDATE only when you need to reserve data you are querying to ensure that no one changes the data while you are processing it.

The SELECT statement with the FOR UPDATE clause (SELECT FOR UPDATE statement) selects the rows of the result set and locks them. SELECT FOR UPDATE lets you base an update on the existing values in the rows, because it ensures that no other user can change those values before you update them. You can also use SELECT FOR UPDATE to lock rows that you do not want to update.

By default, the SELECT FOR UPDATE statement waits until the requested row lock is acquired. To change this behavior, use the NOWAIT, WAIT, or SKIP LOCKED clause of the SELECT FOR UPDATE statement.

When SELECT FOR UPDATE is associated with an explicit cursor, the cursor is called a FOR UPDATE cursor. Only a FOR UPDATE cursor can appear in the CURRENT OF clause of an UPDATE or DELETE statement. (The CURRENT OF clause, a PL/SQL extension to the WHERE clause of the SQL statements UPDATE and DELETE, restricts the statement to the current row of the cursor.)

SQL> DECLARE
  2     CURSOR emp_cur
  3     IS
  4       SELECT * FROM emp FOR UPDATE;
  5
  6  emp_rec emp_cur%ROWTYPE;
  7  vename emp.ename%TYPE;
  8  BEGIN
  9     OPEN emp_cur;
 10     LOOP
 11       FETCH emp_cur INTO emp_rec;
 12   EXIT WHEN emp_cur%NOTFOUND;
 13
 14   IF emp_cur%FOUND THEN
 15   vename := CONCAT(emp_rec.ename,'-X');
 16
 17   UPDATE emp SET ename = vename
 18   WHERE empno = emp_rec.empno;
 19
 20   DBMS_OUTPUT.PUT_LINE('CURSOR ROWCOUNT ::: '||emp_cur%ROWCOUNT||' - '||emp_rec.ename||' - '||vename);
 21   END IF;
 22     END LOOP;
 23     CLOSE emp_cur;
 24  END;
 25  /

--OUTPUT :::
CURSOR ROWCOUNT ::: 1 - KING - KING-X
CURSOR ROWCOUNT ::: 2 - BLAKE - BLAKE-X
CURSOR ROWCOUNT ::: 3 - CLARK - CLARK-X
CURSOR ROWCOUNT ::: 4 - JONES - JONES-X
CURSOR ROWCOUNT ::: 5 - SCOTT - SCOTT-X
CURSOR ROWCOUNT ::: 6 - FORD - FORD-X
CURSOR ROWCOUNT ::: 7 - SMITH - SMITH-X
CURSOR ROWCOUNT ::: 8 - ALLEN - ALLEN-X
CURSOR ROWCOUNT ::: 9 - WARD - WARD-X
CURSOR ROWCOUNT ::: 10 - MARTIN - MARTIN-X
CURSOR ROWCOUNT ::: 11 - TURNER - TURNER-X
CURSOR ROWCOUNT ::: 12 - ADAMS - ADAMS-X
CURSOR ROWCOUNT ::: 13 - JAMES - JAMES-X
CURSOR ROWCOUNT ::: 14 - MILLER - MILLER-X

SQL> DECLARE
  2     CURSOR emp_cur
  3     IS
  4       SELECT * FROM emp FOR UPDATE OF ename;
  5
  6  emp_rec emp_cur%ROWTYPE;
  7  vename emp.ename%TYPE;
  8  BEGIN
  9     OPEN emp_cur;
 10     LOOP
 11       FETCH emp_cur INTO emp_rec;
 12   EXIT WHEN emp_cur%NOTFOUND;
 13
 14   IF emp_cur%FOUND THEN
 15   vename := CONCAT(emp_rec.ename,'-X');
 16
 17   UPDATE emp SET ename = vename
 18   WHERE empno = emp_rec.empno;
 19
 20   DBMS_OUTPUT.PUT_LINE('CURSOR ROWCOUNT ::: '||emp_cur%ROWCOUNT||' - '||emp_rec.ename||' - '||vename);
 21   END IF;
 22     END LOOP;
 23     CLOSE emp_cur;
 24  END;
 25  /

--OUTPUT :::
CURSOR ROWCOUNT ::: 1 - KING - KING-X
CURSOR ROWCOUNT ::: 2 - BLAKE - BLAKE-X
CURSOR ROWCOUNT ::: 3 - CLARK - CLARK-X
CURSOR ROWCOUNT ::: 4 - JONES - JONES-X
CURSOR ROWCOUNT ::: 5 - SCOTT - SCOTT-X
CURSOR ROWCOUNT ::: 6 - FORD - FORD-X
CURSOR ROWCOUNT ::: 7 - SMITH - SMITH-X
CURSOR ROWCOUNT ::: 8 - ALLEN - ALLEN-X
CURSOR ROWCOUNT ::: 9 - WARD - WARD-X
CURSOR ROWCOUNT ::: 10 - MARTIN - MARTIN-X
CURSOR ROWCOUNT ::: 11 - TURNER - TURNER-X
CURSOR ROWCOUNT ::: 12 - ADAMS - ADAMS-X
CURSOR ROWCOUNT ::: 13 - JAMES - JAMES-X
CURSOR ROWCOUNT ::: 14 - MILLER - MILLER-X

SQL> DECLARE
  2     CURSOR emp_cur
  3     IS
  4       SELECT * FROM emp FOR UPDATE NOWAIT;
  5
  6  emp_rec emp_cur%ROWTYPE;
  7  vename emp.ename%TYPE;
  8  BEGIN
  9     OPEN emp_cur;
 10     LOOP
 11       FETCH emp_cur INTO emp_rec;
 12   EXIT WHEN emp_cur%NOTFOUND;
 13
 14   IF emp_cur%FOUND THEN
 15   vename := CONCAT(emp_rec.ename,'-X');
 16
 17   UPDATE emp SET ename = vename
 18   WHERE empno = emp_rec.empno;
 19
 20   DBMS_OUTPUT.PUT_LINE('CURSOR ROWCOUNT ::: '||emp_cur%ROWCOUNT||' - '||emp_rec.ename||' - '||vename);
 21   END IF;
 22     END LOOP;
 23     CLOSE emp_cur;
 24  END;
 25  /

--OUTPUT :::
CURSOR ROWCOUNT ::: 1 - KING - KING-X
CURSOR ROWCOUNT ::: 2 - BLAKE - BLAKE-X
CURSOR ROWCOUNT ::: 3 - CLARK - CLARK-X
CURSOR ROWCOUNT ::: 4 - JONES - JONES-X
CURSOR ROWCOUNT ::: 5 - SCOTT - SCOTT-X
CURSOR ROWCOUNT ::: 6 - FORD - FORD-X
CURSOR ROWCOUNT ::: 7 - SMITH - SMITH-X
CURSOR ROWCOUNT ::: 8 - ALLEN - ALLEN-X
CURSOR ROWCOUNT ::: 9 - WARD - WARD-X
CURSOR ROWCOUNT ::: 10 - MARTIN - MARTIN-X
CURSOR ROWCOUNT ::: 11 - TURNER - TURNER-X
CURSOR ROWCOUNT ::: 12 - ADAMS - ADAMS-X
CURSOR ROWCOUNT ::: 13 - JAMES - JAMES-X
CURSOR ROWCOUNT ::: 14 - MILLER - MILLER-X

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *