In this post, I am going to explain how FOR UPDATE and FOR UPDATE SKIP LOCKED clauses work. I would like to demonstrate it with a basic example. Let’s first take a look at what these clauses mean.
- FOR UPDATE: It locks all the selected rows as soon as the cursor is opened. If any of the selected rows is locked by another session. The current session will wait until the locking transaction will COMMIT or ROLLBACK.
- FOR UPDATE NOWAIT: This is similar to FOR UPDATE. However, if another session is holding a lock on any of the selected rows the current transaction will receive immediately the following error. “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”
- FOR UPDATE WAIT n: This is also similar to FOR UPDATE. In case of the row locks are held by another transaction, the current session will wait only “n” seconds. if the transaction is not released at the end of the specified time, then the following error will be issued. “ORA-30006: resource busy; acquire with WAIT timeout expired”
- FOR UPDATE SKIP LOCKED: This clause behaves differently from others. Basically, the selected rows are locked when they are fetched only, not when the cursor is opened like others. However, the following rows will be skipped.
- Rows that are currently locked by another transaction.
- Rows that were updated after the opening time of the cursor.
SET UP
Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
SQL> CREATE TABLE T1
2 AS
3 SELECT LEVEL ID, DBMS_RANDOM.STRING('A', 10) TXT FROM DUAL CONNECT BY LEVEL <= 10;
Table created.
SQL> SET LINESIZE 100
SQL> COL ID FORMAT 99999
SQL> COL TXT FORMAT A20
SQL>
SQL> SELECT * FROM T1;
ID TXT
------ --------------------
1 GFUriupYNt
2 mylZMIShKc
3 kGWGpeCOPL
4 WJIbvRrcRI
5 jDgnVKSZgy
6 szHZBwkXFx
7 FMiWViQdBd
8 QClrPGQogW
9 dvOWkYGszB
10 PxozeFlxQX
10 rows selected.
Be aware of the start and finish times for both scripts.
SQL> --Session 1066
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 V_REC T1%ROWTYPE;
3 V_SID NUMBER := SYS_CONTEXT('USERENV', 'SID');
4 CURSOR C1 IS
5 SELECT * FROM T1
6 FOR UPDATE SKIP LOCKED;
7 BEGIN
8
9 DBMS_OUTPUT.PUT_LINE('Session '
10 ||TO_CHAR(V_SID)
11 ||' begins. '
12 ||TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
13 );
14
15 OPEN C1;
16
17 DBMS_LOCK.SLEEP(5); --At this point, the other script is executed from another session.
18
19 LOOP
20
21 FETCH C1 INTO V_REC;
22
23 EXIT WHEN C1%NOTFOUND;
24
25 UPDATE T1
26 SET TXT = 'Updated by '||TO_CHAR(V_SID)
27 WHERE CURRENT OF C1;
28
29 END LOOP;
30
31 CLOSE C1;
32
33 COMMIT;
34
35 DBMS_OUTPUT.PUT_LINE('Session '
36 ||TO_CHAR(V_SID)
37 ||' finishes. '
38 ||TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
39 );
40
41 END;
42 /
Session 1066 begins. 31/01/2023 18:50:02
Session 1066 finishes. 31/01/2023 18:50:07
PL/SQL procedure successfully completed.
SQL> SET LINESIZE 100
SQL> COL ID FORMAT 99999
SQL> COL TXT FORMAT A20
SQL>
SQL> SELECT * FROM T1;
ID TXT
------ --------------------
1 GFUriupYNt
2 mylZMIShKc
3 kGWGpeCOPL
4 WJIbvRrcRI
5 jDgnVKSZgy
6 Updated by 88
7 Updated by 1066
8 Updated by 1066
9 Updated by 1066
10 Updated by 1066
10 rows selected.
SQL> --Session 88
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 V_SID NUMBER := SYS_CONTEXT('USERENV', 'SID');
3
4 CURSOR C1 IS
5 SELECT * FROM T1
6 WHERE ID <=5 FOR UPDATE;
7 BEGIN
8
9 DBMS_OUTPUT.PUT_LINE('Session '
10 ||TO_CHAR(V_SID)
11 ||' begins. '
12 ||TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
13 );
14
15 UPDATE T1 SET TXT = 'Updated by '||TO_CHAR(V_SID) WHERE ID = 6;
16
17 UPDATE T1 SET TXT = TXT WHERE ID = 7; --This row is updated with the same data.
18
19 COMMIT;
20
21 OPEN C1;
22
23 DBMS_OUTPUT.PUT_LINE('Session '
24 ||TO_CHAR(V_SID)
25 ||' finishes. '
26 ||TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
27 );
28 END;
29 /
Session 88 begins. 31/01/2023 18:50:03
Session 88 finishes. 31/01/2023 18:50:03
PL/SQL procedure successfully completed.
The second script was run manually after opening the cursor in the first script which belong to session 1066. If you look at the second script which is executed by session 88, ID number 7 is updated with its own data on purpose. In other words, even though it is updated, the data isn’t changed. Interestingly, it is not considered as updated. For this reason, the cursor in session 1066 could hold the lock on ID number 7. However, it couldn’t acquire the row lock for ID 6. Because, the data of ID 6 has been changed after the opening time of the first cursor and then the other IDs between 1 and 5 has been locked by the second session.
I hope this post helps. Don’t hesitate to comment if you have questions or need further clarification.