1

Topic: the bound tables in a multiple user mode

The Task:

: wrote:

to Create archive tables of the same structure that the table of tasks ("task") and the table, binding the task and employees ("task-employee"). To write the function moving records from the main tables ("task" and "task-employee") to appropriate archive tables and working in a multiple user mode. As parameter function receives date which specifies that the tasks completed before this date should be transferred to archive. Function returns 0 if 1 task has been transferred to archive tables at least; function returns 1 if it has not been transferred any task and returns 2 if at performance there was an error.

The Decision:

Not archive tables of the same structure as well as archive:
create table tasks_arch (task_id number not null;
task_name varchar2 (200) not null;
start_date date;
stop_date date;
description varchar2 (200);
manager_id number);
create table workflow_arch (task_id number not null;
employee_id number not null);
declare
p_start_date date: = to_date (' 10/11/2019 ', ' dd.mm.yyyy ');
lr_tasks tasks%rowtype;
begin
for rec_all_tasks in (select rowid, task_id
from tasks t
where t.start_date <(p_start_date) +1
)
loop
begin
SavePoint sp_before_tasks;
select *
into lr_tasks
from tasks t
where t.rowid = rec_all_tasks.rowid
for update nowait;
begin
insert into tasks_arch (task_id;
task_name;
start_date;
stop_date;
description;
manager_id)
select task_id;
task_name;
start_date;
stop_date;
description;
manager_id
from tasks t
where t.task_id = lr_tasks.task_id;
insert into workflow_arch (task_id, employee_id)
select task_id, employee_id
from workflow w
where w.task_id = lr_tasks.task_id;
delete from workflow w
where w.task_id = lr_tasks.task_id;
delete from tasks t
where t.rowid = rec_all_tasks.rowid;
exception
when no_data_found then
rollback to sp_before_tasks;
continue;
end;
exception
when others then
rollback to sp_before_tasks;
end;
end loop;
commit;
end;

2

Re: the bound tables in a multiple user mode

1. Parallel session fixes record in workflow between delete from workflow and delete from tasks
2. row-by-row = slow-by-slow (c)

3

Re: the bound tables in a multiple user mode

tester99;
And a question in what?
We do not know, who and as you will check... Can to it "and so descends" ()?

4

Re: the bound tables in a multiple user mode

andrey_anonymous;
I so understand that a multiple user mode only through this procedure. I.e. the second session does not receive lock on the table tasks and to delete does not reach.
Question in, whether is correct so to implement?

5

Re: the bound tables in a multiple user mode

tester99 wrote:

the Question in, whether is correct so to implement?

And so, basically, descends, but it is necessary to attend TIL more abruptly, than normal read
committed. Though, probably, it is meant that it should be emulated  bulk
collect.

6

Re: the bound tables in a multiple user mode

tester99 wrote:

I so understand that a multiple user mode only through this procedure.

It hardly.
Procedure  cannot be the unique process using the data.
Correctly to organize - under the textbook.
Study head data concurency and consistency .
Read application development guide.
Consider various variants of competing loading - from simple select to dml and if suffices , ddl.
Begin with lock of jobs with which it is supposed to work in procedure.
Is a little "" methods, research them (lock of all table, lock of a demanded line by two methods,  lock). Select the most suitable variant to the task.
Consider variants of presence/absence FK, index presence/absence on FK, various variants "on delete".
Study postponed (deferrable... deferred) check of integrity constraints also apply to the task.
Study a variant of data migration by means of dml returning value.
Learn to process exceptional situations correctly.
As a result receive 5-10 candidate solutions of the task and quite good initial  in the field of development generally and Oracle-working out in particular.