Oracle Select for Update Behaviour

Oracle select for update behaviour

The behaviour you've encountered for FOR UPDATE SKIP LOCKED has been described in this blog note. My understanding is that the FOR UPDATE clause is evaluated AFTER the WHERE clause. The SKIP LOCKED is like an additional filter that guarantees that among the rows that would have been returned, none are locked.

Your statement is logically equivalent to: find the first row from card_numbers and return it if it is not locked. Obviously this is not what you want.

Here is a little test case that reproduces the behaviour you describe:

SQL> CREATE TABLE t (ID PRIMARY KEY)
  2  AS SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 1000;

Table created

SESSION1> select id from t where rownum <= 1 for update skip locked;

        ID
----------
         1

SESSION2> select id from t where rownum <= 1 for update skip locked;

        ID
----------

No row is returned from the second select. You can use a cursor to work around this issue:

SQL> CREATE FUNCTION get_and_lock RETURN NUMBER IS
  2     CURSOR c IS SELECT ID FROM t FOR UPDATE SKIP LOCKED;
  3     l_id NUMBER;
  4  BEGIN
  5     OPEN c;
  6     FETCH c INTO l_id;
  7     CLOSE c;
  8     RETURN l_id;
  9  END;
 10  /

Function created

SESSION1> variable x number;
SESSION1> exec :x := get_and_lock;

PL/SQL procedure successfully completed
x
---------
1

SESSION2> variable x number;
SESSION2> exec :x := get_and_lock;

PL/SQL procedure successfully completed
x
---------
2

Since I've explicitely fetched the cursor, only one row will be returned (and only one row will be locked).

Oracle SELECT FOR UPDATE - Demonstration?

Your original experiment failed to demonstrate the locking because in Oracle writes don't block reads. The FOR UPDATE clause allows us to avoid situations in which two sessions attempt to write to the same record; any number of sessions can read a record.

"Okay but still, is there some way to demonstrate the lock in a single script file?"

Yes. Here is a script with a local procedure which uses the autonomous_transaction pragma to simulate a multi-user environment:

declare
    procedure p1 (p_id in number) is
        pragma autonomous_transaction;
        cursor c23  is
            select * from t23
            where id = p_id
            for update nowait;
        r23 c23%rowtype;
    begin
        dbms_output.put_line('nested transaction');
        open c23;
        fetch c23 into r23;
        update t23 
        set col2 = col2 * 2;
        close c23;
        commit;
    exception
        when others then
            dbms_output.put_line(sqlerrm);
    end;

begin
    update t23
    set col1 = 2
    where id = 1;

    p1 (1);

    commit;
end;
/

The first UPDATE statement issues a lock, which causes the procedural call to fail because it can't get a lock (due to use of NOWAIT clause):

  ...
  30  end;
  31  /
nested transaction
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

PL/SQL procedure successfully completed.

SQL> 

Does SELECT FOR UPDATE prevent other connections inserting when the row is not present?

In Oracle, the SELECT ... FOR UPDATE has no effect on a non-existent row (the statement simply raises a No Data Found exception). The INSERT statement will prevent a duplicates of unique/primary key values. Any other transactions attempting to insert the same key values will block until the first transaction commits (at which time the blocked transaction will get a duplicate key error) or rolls back (at which time the blocked transaction continues).

Oracle database SELECT... FOR UPDATE with autocommit on

As said in documentation:

the default is for a SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved. In almost all cases, however, a statement is completed, and therefore committed, right after it is executed

Another documentation:

Enabling auto-commit may be more convenient, but gives you less control. For example, you have no option to roll back changes. In addition, some SQLJ or JDBC features are incompatible with auto-commit mode. For example, you must disable the auto-commit flag for update batching or SELECT FOR UPDATE syntax to work properly.

So for this query type you should use manual commit

Oracle FOR UPDATE (OF) Cursor behaviour

According to the Oracle 11G PL/SQL documentation here:

When SELECT FOR UPDATE queries multiple tables, it locks only rows
whose columns appear in the FOR UPDATE clause.

So it might appear that in your example, no rows are locked and current of might not work.

However, when I try this:

declare
  cursor c is
    select ename, dname
      from emp join dept on dept.deptno = emp.deptno
      for update;
begin
  for r in c loop
     null;
  end loop;
end;

I find that the rows of EMP and DEPT are locked (an update to either from another session hangs).

If I change the code to try to update one of the tables, it works fine for EMP:

declare
  cursor c is
    select ename, dname
      from emp join dept on dept.deptno = emp.deptno
      for update;
begin
  for r in c loop
     update emp
       set ename = upper(ename)
      where current of c;
  end loop;
end;

But if I try to update DEPT instead I get the exception:

ORA-01410: invalid ROWID

This doesn't surprise me, because I have a foreign key from EMP to DEPT, and EMP will be "key-preserved" by the cursor's query, but DEPT will not be (i.e the same DEPT row can appear more than once in the results).

This suggests to me that the documentation is wrong, or at least misleading. However, I cannot see how your code could just not update the row, without raising an error as mine did.

---

Related Topics