A question about mixing the (relatively new) “fetch first” syntax with “select for update” appeared a few days ago on the Oracle Developer Forum. The requirement was for a query something like:
select * from t1 order by n1 fetch first 10 rows only for update ;
The problem with this query is that it results in Oracle raising error ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. The error doesn’t seem to be particularly relevant, of course, until you remember that “fetch first” creates an inline view using the analytic row_number() under the covers.
One suggested solution was to use PL/SQL to open a cursor with a pure select then use a loop to lock each row in turn. This would need a little defensive programming, of course, since each individual “select for update” would be running at a different SCN from the driving loop, and there would be some risk of concurrency problems (locking, or competing data change) occuring.
There is a pure – thought contorted – SQL solution though where we take the driving SQL and put it into a subquery that generates the rowids of the rows we want to lock, as follows:
select /*+ qb_name(main) */ * from t1 where t1.rowid in ( select /*+ qb_name(inline) unnest no_merge */ t1a.rowid from t1 t1a order by t1a.n1 fetch first 10 rows only ) for update ;
The execution plan for this query is critical – so once you can get it working it would be a good idea to create a baseline (or SQL Patch) and attach it to the query. It is most important that the execution plan should be the equivalent of the following:
select /*+ qb_name(main) */ * from t1 where t1.rowid in ( select /*+ qb_name(inline) unnest no_merge */ t1a.rowid from t1 t1a order by t1a.n1 fetch first 10 rows only ) for update Plan hash value: 1286935441 --------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 190 | | | | | 1 | FOR UPDATE | | 1 | | 10 |00:00:00.01 | 190 | | | | | 2 | BUFFER SORT | | 2 | | 20 |00:00:00.01 | 178 | 2048 | 2048 | 2048 (0)| | 3 | NESTED LOOPS | | 1 | 10 | 10 |00:00:00.01 | 178 | | | | |* 4 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 177 | | | | |* 5 | WINDOW SORT PUSHED RANK | | 1 | 10000 | 10 |00:00:00.01 | 177 | 2048 | 2048 | 2048 (0)| | 6 | TABLE ACCESS FULL | T1 | 1 | 10000 | 10000 |00:00:00.01 | 177 | | | | | 7 | TABLE ACCESS BY USER ROWID| T1 | 10 | 1 | 10 |00:00:00.01 | 1 | | | | --------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=10) 5 - filter(ROW_NUMBER() OVER ( ORDER BY "T1A"."N1")<=10)
Critically you need the VIEW operation to be the driving query of a nested loop join that does the “table access by user rowid” joinback. In my case the query has used a full tablescan to identify the small number of rowids needed – in a production system that would be the part of the statement that should first be optimised.
It’s an unfortunate feature of this query structure (made messier by the internal rewrite for the analytic function) that it’s not easy to generate a correct set of hints to force the plan until after you’ve already managed to get the plan. Here’s the outline information that shows the messiness of the hints I would have needed:
Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"INLINE") OUTLINE_LEAF(@"SEL$A3F38ADC") UNNEST(@"SEL$1") OUTLINE(@"INLINE") OUTLINE(@"MAIN") OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$A3F38ADC" "from$_subquery$_003"@"SEL$1") ROWID(@"SEL$A3F38ADC" "T1"@"MAIN") LEADING(@"SEL$A3F38ADC" "from$_subquery$_003"@"SEL$1" "T1"@"MAIN") USE_NL(@"SEL$A3F38ADC" "T1"@"MAIN") FULL(@"INLINE" "T1A"@"INLINE") END_OUTLINE_DATA */
You’ll notice that my /*+ unnest */ hint is now modified – for inclusion at the start of the query – to /*+ unnest(@sel1) */ rather than the /*+ unnest(@inline) */ that you might have expected. That’s the side effect of the optimizer doing the “fetch first” rewrite before applying “missing” query block names. If I wanted to write a full hint set into the query itself (leaving the qb_name() hints in place but removing the unnest and merge I had originally) I would need the following:
/*+ unnest(@sel$1) leading(@sel$a3f38adc from$_subquery$_003@sel$1 t1@main) use_nl( @sel$a3f38adc t1@main) rowid( @sel$a3f38adc t1@main) */
I did make a bit of a fuss about the execution plan. I think it’s probably very important that everyone who runs this query gets exactly the same plan and the plan should be this nested loop. Although there’s a BUFFER SORT at operation 2 that is probably ensuring that every would get the same data in the same order regardless of the execution plan before locking any of it, I would be a little worried that different plans might somehow be allowed to lock the data in a different order, thus allowing for deadlocks.
Hello Jonathan,
A very interesting topic, that comes back after a long time.
While reading it, I suddenly remembered of the following related older but equally interesting post:
https://hoopercharles.wordpress.com/2011/11/21/select-for-update-in-what-order-are-the-rows-locked/
Thanks a lot & Best Regards,
Iudith Mentzel
Comment by Iudith Mentzel — June 3, 2020 @ 3:28 pm BST Jun 3,2020 |
Iudith,
Thanks for the comment and the link – good memory.
I won’t be able to put a finger on it, but I do remember a discussion a few years ago (maybe about the time Charles wrote that blog note) about a bug (or possibly a “notable change inbehaviour”) in Oracle where the order in which rows were locked changed on an upgrade if there were an ORDER BY clause so that the order of locking matched the ORDER BY clause rather than the order of data access.
(I don’t remember the details but I think this was fairly important when two non-trivial “literal” statements would fetch overlapping data but one would used a nested loop join and the other would use a hash join so the order of data access was different and the statements would deadlock if the lock was in access order, but one statement would simply wait for the other if it was in “order by” order.)
Regards
Jonathan Lewis
Comment by Jonathan Lewis — June 3, 2020 @ 7:01 pm BST Jun 3,2020 |
Hi Jonathan,
I would rather take a look at SKIP LOCKED + BULK COLLECT LIMIT N. It locks the rows while fetching them, so that it does not provide the exact behaviour of “CURSOR … FOR UPDATE” which locks the whole resultset when the cursor is opened.
I put an abridged example of the proposed technique below:
Regards,
Mikhail.
Comment by Mikhail Velikikh — June 3, 2020 @ 6:06 pm BST Jun 3,2020 |
Mikhail,
Thanks for the comment.
That’s a fascinating difference between “select for update” and “select for update skip locked” when used as cursors in that way. Is it documented anywhere (sorry, being lazy) or is it something you discovered experimentally.
It’s possible that it’s also demonstrating an underlying change in “skip locked”. I can’t be certain when it changed because it’s been a long time (10 years) since I looked at it, but in this posting the comment discussion says that rownum should applied before the test for the lock — and I’m wondering if the “limit N” used to be the same. I’ll have to look at it again when I have a little time.
Comment by Jonathan Lewis — June 3, 2020 @ 6:53 pm BST Jun 3,2020 |
Jonathan,
[quote]Is it documented anywhere (sorry, being lazy) or is it something you discovered experimentally.[/quote]
I must have read about it somewhere, probably on this blog post: Rob van Wijk: http://rwijk.blogspot.com/2009/02/for-update-skip-locked.html
I see that it is documented that opening a cursor locks the rows of the result set:
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/static-sql.html#GUID-87B4D958-520C-4483-BC63-2D1FE4C4CC01
I am afraid I cannot find when it is said in so many words that ‘for update skip locked locks the rows as they are fetched’.
Yet it can be confirmed by a test, and I would love to see an experiment proving the opposite.
I just did a small test dumping the redo entries for two open cursor commands, so as to substantiate this “SKIP LOCKED” behaviour with some facts:
https://github.com/mvelikikh/oracle/blob/master/blog/202006_for_update_vs_skip_locked_redo_dump/for_update_skip_locked_redo_dump.sql
1. FOR UPDATE:
its trace file: https://github.com/mvelikikh/oracle/blob/master/blog/202006_for_update_vs_skip_locked_redo_dump/orcl_ora_17319_FOR_UPDATE.trc
2. FOR UPDATE SKIP LOCKED:
its trace file: https://github.com/mvelikikh/oracle/blob/master/blog/202006_for_update_vs_skip_locked_redo_dump/orcl_ora_17319_FOR_UPDATE_SKIP_LOCKED.trc
We can see that the first command generated 10 Redo Op Codes 11.4 – a “lock row piece” Op Code, where 10 is the number of rows in the table, whereas the second command generated one redo record with the usual begin transaction Redo Op codes. Therefore, opening the FOR UPDATE SKIP LOCKED cursor did not lock any rows as opposed to its FOR UPDATE counterpart.
Regards,
Mikhail.
Comment by Mikhail Velikikh — June 4, 2020 @ 12:31 pm BST Jun 4,2020 |
Hello Jonathan & Mikhail,
You can easily demonstrate the behavior for FOR UPDATE SKIP LOCKED using an autonomous transaction,
like in your first example:
create table t1
as
select level n1
from dual
connect by level <= 10
/
declare
cursor csr is
select *
from t1
order by n1
for update skip locked;
type csr_tbl_type is table of csr%rowtype;
tbl csr_tbl_type;
procedure check_locked (p_n1 in t1.n1%type )
is
pragma autonomous_transaction;
cursor c
is
select 'dummy' from t1
where n1 = p_n1
for update nowait ;
e_resource_busy exception;
pragma exception_init(e_resource_busy,-54);
begin
open c;
close c;
dbms_output.put_line('Record ' || to_char(p_n1) || ' is not locked.');
rollback;
exception
when e_resource_busy then
dbms_output.put_line('Record ' || to_char(p_n1) || ' is locked.');
end check_locked;
begin
open csr;
dbms_output.put_line('Locked after open');
for n in 1 .. 10
loop
check_locked(n);
end loop;
fetch csr bulk collect into tbl limit 3;
dbms_output.put_line('Locked after fetch limit 3');
for n in 1 .. 10
loop
check_locked(n);
end loop;
close csr;
end;
/
Locked after open
Record 1 is not locked.
Record 2 is not locked.
Record 3 is not locked.
Record 4 is not locked.
Record 5 is not locked.
Record 6 is not locked.
Record 7 is not locked.
Record 8 is not locked.
Record 9 is not locked.
Record 10 is not locked.
Locked after fetch limit 3
Record 1 is locked.
Record 2 is locked.
Record 3 is locked.
Record 4 is not locked.
Record 5 is not locked.
Record 6 is not locked.
Record 7 is not locked.
Record 8 is not locked.
Record 9 is not locked.
Record 10 is not locked.
So , from here we see that the records are locked as they are fetched.
I think that there is an essential difference between ROWNUM < n and FETCH … LIMIT n,
because the ROWNUM condition is applied while executing the query, on the server side, while the FETCH … LIMIT n is controlled
by the client side, after all the selected rows were located.
In a similar way, we can demonstrate not only that the rows are locked ONLY when they are fetched,
but, also that if a row that was selected by the FOR UPDATE SKIP LOCKED cursor is either:
a. deleted by another session after the cursor was opened, but before that row was fetched and locked
or
b. changed by another session after the cursor was opened, but before that row was fetched and locked
in such a way that is relevant for its selection through the cursor (for example, changing a column that appears in the cursor's
SELECT list or WHERE clause or ORDER BY)
then that row will be skipped when fetching from the FOR UPDATE SKIP LOCKED cursor.
Thanks a lot & Best Regards,
Iudith Mentzel
Comment by Iudith Mentzel — June 4, 2020 @ 3:01 pm BST Jun 4,2020 |