I am quite not understanding the lock functionality with SELECT FOR UPDATE.
Here is what I've tried.
CREATE TABLE ACCOUNT_TAB (
ACC_ID int NOT NULL PRIMARY KEY,
BALANCE int NOT NULL
INSERT INTO ACCOUNT_TAB
SELECT * FROM ACCOUNT_TAB FOR UPDATE;
SELECT * FROM ACCOUNT_TAB;
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>