It first gets the number of skipped rows, and lists all the skipped rows as well.
We also give a workaround for delete skip locked.
Then we explore one unexpected Oracle behaviour of analytic function with skip locked in finding the skipped rows.
Note: Tested in Oracle 19.24.
1. Test Setup
We create one test table and one procedure to detect skipped rows.
drop table test_upd_tab;
create table test_upd_tab as select level x, -level y from dual connect by level <= 10;
create index test_upd_tab_idx_1 on test_upd_tab(x);
exec dbms_stats.gather_table_stats('K', 'TEST_UPD_TAB', cascade=>true);
--drop type test_upd_tab_set force;
--drop type test_upd_tab_row force;
create or replace type test_upd_tab_row as object (x number, y number, vsum number, rcnt number, rnum number);
/
create or replace type test_upd_tab_set is table of test_upd_tab_row;
/
create or replace procedure test_update_skip_locked(p_start number, p_end number) as
l_row_set_upd test_upd_tab_set;
l_updatable_rows number;
l_sql_text varchar2(4000);
l_xid varchar2(50);
l_start_scn number;
l_start_date date;
l_row_set_start test_upd_tab_set;
begin
select /*+ index(t test_upd_tab_idx_1) GATHER_PLAN_STATISTICS MONITOR test_ks_2 */ test_upd_tab_row(x, y, 0, 0, 0)
bulk collect into l_row_set_upd
from test_upd_tab t where x between p_start and p_end for update skip locked;
dbms_output.put_line(rpad('=', 100, '='));
dbms_output.put_line('Selected to Update Rows#: '||sql%rowcount);
dbms_output.put_line('Returned to Update Rows#: '||l_row_set_upd.count());
dbms_output.put_line(rpad('=', 100, '='));
for c in (
with sq as (select * from v$sql_plan_monitor
where sql_id in (select sql_id from v$sql_monitor
where lower(sql_text) like 'select%test_ks_2%' and sid = sys_context('userenv', 'sid')))
select sql_id, sid, sql_exec_start, plan_line_id, plan_operation, output_rows from sq
where sql_exec_id = (select max(sql_exec_id) from sq) and plan_line_id in (1, 2) order by plan_line_id)
loop
case
when c.plan_line_id = 1 then
l_updatable_rows := c.output_rows;
select sql_text into l_sql_text from v$sql where sql_id = c.sql_id and rownum = 1;
dbms_output.put_line('Session: '||c.sid||', at: '||c.sql_exec_start||', run SQL_ID: '||c.sql_id);
dbms_output.put_line('sql_text: '||l_sql_text);
dbms_output.put_line(rpad('=', 100, '='));
when c.plan_line_id = 2 then
dbms_output.put_line('Found Rows#: '||c.output_rows);
dbms_output.put_line('Updatable Rows#: '||l_updatable_rows);
dbms_output.put_line('Skipped Rows#: '||(c.output_rows - l_updatable_rows));
end case;
end loop;
select xid, start_scn, start_date into l_xid, l_start_scn, l_start_date
from v$transaction t
where (xidusn||'.'||xidslot||'.'||xidsqn) = dbms_transaction.local_transaction_id;
dbms_output.put_line(rpad('=', 100, '='));
dbms_output.put_line('XID = '||l_xid||', Start_SCN = '||l_start_scn||', Start_Date = '||l_start_date);
select /*+ GATHER_PLAN_STATISTICS MONITOR test_ks_3 */ test_upd_tab_row(x, y, 0, 0, 0)
bulk collect into l_row_set_start
from test_upd_tab as of scn l_start_scn where x between p_start and p_end;
dbms_output.put_line(rpad('=', 100, '='));
dbms_output.put_line('Selected at Start Rows#: '||SQL%ROWCOUNT);
dbms_output.put_line('Returned at Start Rows#: '||l_row_set_start.count());
dbms_output.put_line(rpad('=', 42, '=')||' Skip Locked Rows '||rpad('=', 42, '='));
dbms_output.put_line(rpad('X', 8, ' ')||rpad('Y', 8, ' '));
dbms_output.put_line(rpad('-', 8, '-')||rpad('-', 8, '-'));
for c in (select * from table(l_row_set_start) minus select * from table(l_row_set_upd) order by 1)
loop
dbms_output.put_line(rpad(c.x, 8, ' ')||rpad(c.y, 8, ' '));
end loop;
end;
/
2. Test Run
We open two Sqlplus sessions, and make following test.
2.1 Session_1
We first lock 3 rows with x between 5 and 7 in Session_1:
------------ Session_1@T1, lock 3 rows ------------
SQL > select * from test_upd_tab t where x between 5 and 7 for update skip locked;
X Y
---------- ----------
5 -5
6 -6
7 -7
2.2 Session_2
In Session_2, we are trying to lock 8 rows with x between 2 and 9.
The output shows that there are 8 rows found, but only 5 are updatable since 3 (=8-5) rows are locked by Session_1.
Then we list all 3 skipped rows.
------------ Session_2@T2, Print the number of skipped lock rows, and skip locked rows ------------
alter session set nls_date_format ='YYYY-MM-DD HH24:MI:SS';
SQL > exec test_update_skip_locked(2, 9);
====================================================================================================
Selected to Update Rows#: 5
Returned to Update Rows#: 5
====================================================================================================
Session: 553, at: 2024-10-22 05:44:44, run SQL_ID: 14bm0y4afwr9u
sql_text: SELECT /*+ index(t test_upd_tab_idx_1) GATHER_PLAN_STATISTICS MONITOR test_ks_2 */ TEST_UPD_TAB_ROW(X, Y, 0, 0, 0)
FROM TEST_UPD_TAB T WHERE X BETWEEN :B2 AND :B1 FOR UPDATE SKIP LOCKED
====================================================================================================
Found Rows#: 8
Updatable Rows#: 5
Skipped Rows#: 3
====================================================================================================
XID = 5800010042A60100, Start_SCN = 13106847731835, Start_Date = 2024-10-22 05:44:44
====================================================================================================
Selected at Start Rows#: 8
Returned at Start Rows#: 8
========================================== Skip Locked Rows ==========================================
X Y
----------------
5 -5
6 -6
7 -7
select sys.dbms_sqltune.report_sql_monitor('14bm0y4afwr9u', report_level=>'all' , type=>'TEXT') from dual;
SQL Text
------------------------------
SELECT /*+ index(t test_upd_tab_idx_1) GATHER_PLAN_STATISTICS MONITOR test_ks_2 */ TEST_UPD_TAB_ROW(X, Y, 0, 0, 0)
FROM TEST_UPD_TAB T WHERE X BETWEEN :B2 AND :B1 FOR UPDATE SKIP LOCKED
SQL ID : 14bm0y4afwr9u
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B2 | 1 | NUMBER | 2 |
| :B1 | 2 | NUMBER | 9 |
========================================================================================================================
Global Stats
======================================
| Elapsed | Cpu | Fetch | Buffer |
| Time(s) | Time(s) | Calls | Gets |
======================================
| 0.00 | 0.00 | 1 | 28 |
======================================
SQL Plan Monitoring Details (Plan Hash Value=1755639989)
===========================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
===========================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 5 | | |
| 1 | FOR UPDATE | | | | 1 | +0 | 1 | 5 | | |
| 2 | FILTER | | | | 1 | +0 | 1 | 8 | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | TEST_UPD_TAB | 10 | 1 | 1 | +0 | 1 | 8 | | |
| 4 | INDEX RANGE SCAN | TEST_UPD_TAB_IDX_1 | 10 | 1 | 1 | +0 | 1 | 8 | | |
===========================================================================================================================================================
3. Delete skip locked
Oracle does not support "delete skip locked" (error: ORA-00933: SQL command not properly ended). We can have a workaround if we run following code in Session_2:
------------ Session_2@T2, delete skip locked rows ------------
declare
p_start number := 2;
p_end number := 9;
l_row_set_upd test_upd_tab_set;
l_row_set_del test_upd_tab_set;
begin
select /*+ index(t test_upd_tab_idx_1) GATHER_PLAN_STATISTICS MONITOR test_ks_2 */ test_upd_tab_row(x, y, 0, 0, 0)
bulk collect into l_row_set_upd
from test_upd_tab t where x between p_start and p_end for update skip locked;
delete from test_upd_tab
where x between p_start and p_end
and x in (select x from table(l_row_set_upd))
returning test_upd_tab_row(x, y, 0, 0, 0) bulk collect into l_row_set_del;
dbms_output.put_line(rpad('=', 42, '=')||' Delete skip locked rows '||rpad('=', 42, '='));
dbms_output.put_line(rpad('X', 8, ' ')||rpad('Y', 8, ' '));
dbms_output.put_line(rpad('-', 8, '-')||rpad('-', 8, '-'));
for c in (select * from table(l_row_set_del) order by 1)
loop
dbms_output.put_line(rpad(c.x, 8, ' ')||rpad(c.y, 8, ' '));
end loop;
dbms_output.put_line('');
dbms_output.put_line(l_row_set_del.count()||' rows deleted.');
end;
/
========================================== Delete skip locked rows ==========================================
X Y
----------------
2 -2
3 -3
4 -4
8 -8
9 -9
4. Using Oracle behaviour of analytic function with skip locked
When facing question: "Is there a way to get the data of the rows that were skipped ?",
Solomon Yakobson (Feb 22 2018) in Oracle Forums: For Update Skip Locked (https://forums.oracle.com/ords/apexds/post/for-update-skip-locked-6618)
revealed one related Oracle behaviour of analytic function with skip locked.
4.1. Test of analytic function with skip locked
Here the test and all output, which shows locked row data will be included in analytic function related calculations of VAL_SUM, ROW_NUM.
(at first rollback all transactions in Session_1 and Session_2)
------------- Session_1@T1, Test Setup and one row update -------------
drop table test_skip_tab;
create table test_skip_tab as select level id, 100 val from dual connect by level <= 3;
select * from test_skip_tab;
update test_skip_tab set val = val where id = 2;
select sum(val), count(*) from test_skip_tab;
SUM(VAL) COUNT(*)
---------- ----------
300 3
select t.*, sum (val) over () val_sum, count (*) over () row_cnt, row_number() over (order by id) row_num from test_skip_tab t;
ID VAL VAL_SUM ROW_CNT ROW_NUM
---------- ---------- ---------- ---------- ----------
1 100 300 3 1
2 100 300 3 2
3 100 300 3 3
------------- Session_2@T2, look VAL_SUM, ROW_NUM -------------
select t.*, sum (val) over () val_sum, count (*) over () row_cnt, row_number() over (order by id) row_num from test_skip_tab t for update skip locked;
ID VAL VAL_SUM ROW_CNT ROW_NUM
---------- ---------- ---------- ---------- ----------
1 100 300 3 1
3 100 300 3 3
------------- Session_1@T3, look VAL_SUM, ROW_NUM -------------
select t.*, sum (val) over () val_sum, count (*) over () row_cnt, row_number() over (order by id) row_num from test_skip_tab t for update skip locked;
ID VAL VAL_SUM ROW_CNT ROW_NUM
---------- ---------- ---------- ---------- ----------
2 100 300 3 2
4.2. Use unexpected behaviour to in find the skipped rows.
Similar to Section 1 Test Setup, we first code one Plsql procedure:
create or replace procedure test_update_skip_locked_2(p_start number, p_end number) as
l_row_set_upd test_upd_tab_set;
l_updatable_rows number;
l_sql_text varchar2(4000);
l_xid varchar2(50);
l_start_scn number;
l_start_date date;
l_row_set_start test_upd_tab_set;
l_cnt number;
begin
select /*+ index(t test_upd_tab_idx_1) GATHER_PLAN_STATISTICS MONITOR test_ks_2b */
test_upd_tab_row(x, y, sum (1) over (), count (1) over (), row_number() over (order by x))
bulk collect into l_row_set_upd
from test_upd_tab t where x between p_start and p_end for update skip locked;
dbms_output.put_line(rpad('=', 100, '='));
dbms_output.put_line('Selected to Update Rows#: '||sql%rowcount);
dbms_output.put_line('Returned to Update Rows#: '||l_row_set_upd.count());
dbms_output.put_line(rpad('=', 100, '='));
dbms_output.put_line('Found Rows#: '||l_row_set_upd(1).rcnt);
dbms_output.put_line('Updatable Rows#: '||l_row_set_upd.count);
dbms_output.put_line('Skipped Rows#: '||(l_row_set_upd(1).rcnt - l_row_set_upd.count));
dbms_output.put_line(rpad('=', 100, '='));
for c in (select level + (p_start-1) rnum from dual connect by level <= l_row_set_upd(1).rcnt
minus
select t.rnum + (p_start-1) rnum from table(l_row_set_upd) t
)
loop
dbms_output.put_line('Skipped Row Number: '||c.rnum);
end loop;
select xid, start_scn, start_date into l_xid, l_start_scn, l_start_date
from v$transaction t
where (xidusn||'.'||xidslot||'.'||xidsqn) = dbms_transaction.local_transaction_id;
dbms_output.put_line(rpad('=', 100, '='));
dbms_output.put_line('XID = '||l_xid||', Start_SCN = '||l_start_scn||', Start_Date = '||l_start_date);
select /*+ GATHER_PLAN_STATISTICS MONITOR test_ks_3b */
test_upd_tab_row(x, y, sum (1) over (), count (1) over (), row_number() over (order by x))
bulk collect into l_row_set_start
from test_upd_tab as of scn l_start_scn where x between p_start and p_end;
dbms_output.put_line(rpad('=', 100, '='));
dbms_output.put_line('Selected at Start Rows#: '||SQL%ROWCOUNT);
dbms_output.put_line('Returned at Start Rows#: '||l_row_set_start.count());
dbms_output.put_line(rpad('=', 42, '=')||' Skip Locked Rows '||rpad('=', 42, '='));
dbms_output.put_line(rpad('X', 8, ' ')||rpad('Y', 8, ' '));
dbms_output.put_line(rpad('-', 8, '-')||rpad('-', 8, '-'));
for c in (select * from table(l_row_set_start) minus select * from table(l_row_set_upd) order by 1)
loop
dbms_output.put_line(rpad(c.x, 8, ' ')||rpad(c.y, 8, ' '));
end loop;
end;
/
4.3 Session_1 Test
We first lock 3 rows with x between 5 and 7 in Session_1:
(at first rollback all transactions in Session_1 and Session_2)
------------ Session_1@T1, lock 3 rows ------------
SQL > select * from test_upd_tab t where x between 5 and 7 for update skip locked;
X Y
---------- ----------
5 -5
6 -6
7 -7
4.4 Session_2 Test
In Session_2, we are trying to lock 8 rows with x between 2 and 9.
The output shows that there are 8 rows found, but only 5 are updatable since 3 (=8-5) rows are locked by Session_1.
Then we list all 3 skipped rows.
------------ Session_2@T2, Print the number of skipped lock rows, and skip locked rows ------------
alter session set nls_date_format ='YYYY-MM-DD HH24:MI:SS';
SQL > exec test_update_skip_locked_2(2, 9);
====================================================================================================
Selected to Update Rows#: 5
Returned to Update Rows#: 5
====================================================================================================
Found Rows#: 8
Updatable Rows#: 5
Skipped Rows#: 3
====================================================================================================
Skipped Row Number: 5
Skipped Row Number: 6
Skipped Row Number: 7
====================================================================================================
XID = 5F00140055F40100, Start_SCN = 13106847744696, Start_Date = 2024-10-22 10:16:57
====================================================================================================
Selected at Start Rows#: 8
Returned at Start Rows#: 8
========================================== Skip Locked Rows ==========================================
X Y
----------------
5 -5
6 -6
7 -7
5. Related Work
Rob van Wijk (February 7, 2009) in Blog: FOR UPDATE SKIP LOCKED (http://rwijk.blogspot.com/2009/02/for-update-skip-locked.html) showed:
(1). opening a cursor with the "for update skip locked" clause doesn't lock a single row. Fetching a row does lock the row.
(2). a "for update skip locked" doesn't only skip the locked rows, it also skips the rows that were modified between the opening and the fetching, thus avoiding the classic "lost update".
