Let's say you want to reserve the 10 first rows of the EMPLOYEES table of your HR schema.
In Oracle's SQL*Plus, this would look like this:
SQL> select employee_id from employees where rownum <= 10 for update skip locked;This would return a report like this:
The idea is to get the first unlocked records, and then to lock them. Let's try the following PLSQL block:
set serveroutput on
declare
counter integer := 0;
cursor emp_curs is
select employee_id from employees for update skip locked;
emp_row emp_curs%ROWTYPE;
emp_id employees.employee_id%TYPE;
begin
open emp_curs;
loop
fetch emp_curs into emp_row;
exit when emp_curs%NOTFOUND;
counter := counter + 1;
select employee_id into emp_id from employees where employee_id = emp_row.employee_id for update;
dbms_output.put_line('-> Locked: ' || to_char(emp_id));
if counter >= 10 then
exit;
end if;
end loop;
end;
/
We just display the rows we want to lock.
We run it:
Now, before releasing the lock, let's run it from another process or thread:
Etc, etc.
declare
counter integer := 0;
cursor emp_curs is
select e.*, rowid from employees e for update skip locked;
emp_row emp_curs%ROWTYPE;
emp_id employees.employee_id%TYPE;
begin
open emp_curs;
loop
fetch emp_curs into emp_row;
exit when emp_curs%NOTFOUND;
counter := counter + 1;
select employee_id into emp_id from employees where rowid = emp_row.rowid for update;
dbms_output.put_line('-> Locked: ' || to_char(emp_id));
if counter >= 10 then
exit;
end if;
end loop;
end;
/
It is even possible to use dynamic SQL to do this job (this is on another schema, as you can tell):
set serveroutput on size 3000
set ver off
set timing on
accept nbrows2lock prompt 'Max Nb of Rows to Lock > '
-- +---------------------------------------------------
declare
maxReturnedRows integer := &nbrows2lock;
table_name varchar2(128) := 'QUEUE_2100';
vch_tsolabel varchar2(64) := 'MANAGE_USER';
counter integer := 0;
sql_stmt_One varchar2(4096) := '';
vch_hint varchar2(128) := '/*+ Rule */';
tsoidvar varchar2(60);
orderidvar varchar2(60);
msisdnvar varchar2(60);
imsivar varchar2(60);
priorityvar number(4,0);
instmsdt date;
row_id rowid;
type QueueCursorType is ref cursor;
queue_curs_var QueueCursorType;
type recordType is record
(
row_id rowid,
tsoidvar varchar2(60),
orderidvar varchar2(60),
msisdnvar varchar2(60),
imsivar varchar2(60),
priorityvar number(4,0),
instmsdt date
);
recordHolder recordType;
type tableOfRecord is table of recordType index by binary_integer;
tableHolder tableOfRecord;
i integer := 1; -- Index for tableOfRecord
begin
sql_stmt_One := 'SELECT ' || vch_hint || ' rowid, ' ||
' t.tsoid, ' ||
' nvl(t.orderid, '''') orderid, ' ||
' nvl(t.msisdn, '''') msisdn, ' ||
' nvl(t.imsi, '''') imsi, ' ||
' nvl(t.priority, 1) priority, ' ||
' t.ins_tms ' ||
'FROM ' || table_name || ' t ' ||
'WHERE t.bp_flag = 0 and ' ||
't.tsolabel = :1 ' ||
'for update skip locked';
open queue_curs_var for sql_stmt_One using vch_tsolabel;
loop
-- --------------------------------------------------------------
-- The trick is to have an 'exit when' before and after the fetch
-- --------------------------------------------------------------
exit when counter = maxReturnedRows;
fetch queue_curs_var
into row_id,
tsoidvar,
orderidvar,
msisdnvar,
imsivar,
priorityvar,
instmsdt;
exit when queue_curs_var%NOTFOUND;
counter := counter + 1;
dbms_output.put_line('Locking row #' || to_char(counter));
--
-- for tests. Concurrent locks...
--
if false then -- set to true to wait
dbms_lock.sleep(1); -- Needs grant execute from SYS
end if;
--
recordHolder.row_id := row_id;
recordHolder.tsoidvar := tsoidvar;
recordHolder.orderidvar := orderidvar;
recordHolder.msisdnvar := msisdnvar;
recordHolder.imsivar := imsivar;
recordHolder.priorityvar := priorityvar;
recordHolder.instmsdt := instmsdt;
tableHolder(i) := recordHolder;
i := i + 1;
end loop;
close queue_curs_var;
dbms_output.put_line('Done with the array, ' || to_char(counter) || ' entry(ies)');
if counter > 0 then
-- Loop on the selected values to process them
for i in tableHolder.FIRST..tableHolder.LAST loop
dbms_output.put_line ('Processing [' || tableHolder(i).tsoidvar || ']');
-- Processing takes place here
execute immediate
'update ' || table_name || ' t ' ||
'set t.bp_flag = 1 ' ||
'where rowid = :1'
using tableHolder(i).row_id;
end loop;
else
dbms_output.put_line('No record to manage');
end if;
exception
when others then
dbms_output.put_line('ERR:' || sqlerrm);
end;
/
show errors
We are actually using here the same kind of technique as above, having the SQL generated dynamically does not make much difference. We are using the ROWID approach. Notice the following details:
- The exit when statement, before and after the fetch of the first cursor
- The way we populate a table of records, so we can handle it after putting the lock
- The dbms_lock.sleep, for tests, that allows to simulate what happens if two or more such blocks are executed at the same time. We actually see that it makes no problem, no row can be locked more than once. It is interesting to understand that, for the first cursor (that has a for update clause), the lock is actually set when the record is fetched. This explains the exit when mechanism mentionned above.
