[Home] [Help]
PACKAGE BODY: APPS.AD_CONC_SESSION_LOCKS
Source
1 package body ad_conc_session_locks as
2 /* $Header: adcslckb.pls 115.3 2004/02/05 15:16:18 pzanwar ship $ */
3
4 --
5 -- Procedure
6 -- get_admin_lock
7 --
8 -- Purpose
9 -- Lock the DB admin objects used in ad_concurrent_sessions
10 -- Usage
11 --
12 --
13 --
14 procedure get_admin_lock(status out nocopy number)
15 is
16 l_lockhandle varchar2(128);
17 l_status number := 100;
18 l_exit_loop boolean := FALSE;
19 begin
20 dbms_lock.allocate_unique('ORA_APPS_AD_LOCKMGR', l_lockhandle);
21 --
22 l_exit_loop := FALSE;
23 --
24 loop
25 exit when l_exit_loop;
26 --
27 l_status := dbms_lock.request(l_lockhandle);
28 --
29 if l_status in (0, 4) then
30 -- 0 => success
31 -- 1 => already held. deem as success.
32 --
33 l_exit_loop := TRUE;
34 --
35 elsif l_status <> 1 then
36 --
37 -- 1 => Timeout, in which case we want to keep trying (ie. stay in the
38 -- loop). Any value other than 1 is a fatal error.
39 --
40 raise_application_error(-20000, 'Fatal error in get_admin_lock() - '||
41 to_char(l_status));
42 end if;
43 --
44 end loop;
45 --
46 status := l_status;
47 --
48 return;
49 --
50 end get_admin_lock;
51
52
53 procedure get_admin_lock_with_timeout(status out nocopy number,
54 timeout_period in integer)
55 is
56 l_lockhandle varchar2(128);
57 l_status number := 100;
58 l_exit_loop boolean := FALSE;
59 begin
60 dbms_lock.allocate_unique('ORA_APPS_AD_LOCKMGR', l_lockhandle);
61 --
62 l_exit_loop := FALSE;
63 --
64 loop
65 exit when l_exit_loop;
66 --
67 l_status := dbms_lock.request(l_lockhandle, 6, timeout_period, FALSE);
68 --
69 if l_status in (0, 1, 4) then
70 -- 0 => success
71 -- 1 => already held. deem as success.
72 --
73 l_exit_loop := TRUE;
74 --
75 elsif l_status <> 1 then
76 --
77 -- 1 => Timeout, in which case we want to keep trying (ie. stay in the
78 -- loop). Any value other than 1 is a fatal error.
79
80 raise_application_error(-20000, 'Fatal error in get_admin_lock() - '||
81 to_char(l_status) || timeout_period);
82 end if;
83 --
84 end loop;
85 --
86 status := l_status;
87 --
88 return;
89 --
90 end get_admin_lock_with_timeout;
91
92
93 --
94 -- Procedure
95 -- release_admin_lock
96 --
97 -- Purpose
98 -- Release the DB admin objects used in ad_concurrent_sessions
99 -- Usage
100 --
101 --
102 --
103 procedure release_admin_lock(status out nocopy number)
104 is
105 l_lockhandle varchar2(128);
106 l_status number := 100;
107 l_exit_loop boolean := FALSE;
108 begin
109 dbms_lock.allocate_unique('ORA_APPS_AD_LOCKMGR',l_lockhandle);
110 --
111 l_exit_loop := FALSE;
112 --
113 loop
114 --
115 exit when l_exit_loop;
116 --
117 l_status:=dbms_lock.release(l_lockhandle);
118 --
119 if l_status in (0, 4) then
120 -- 0 => success
121 -- 1 => already held. deem as success.
122 --
123 l_exit_loop := TRUE;
124 else
125 raise_application_error(-20000,
126 'Fatal error in release_admin_lock() - '|| to_char(l_status));
127 end if;
128 --
129 end loop;
130 --
131 --
132 status:=l_status;
133 --
134 --
135 return;
136 end release_admin_lock;
137 --
138 --
139 procedure reset_session_info(sess_id in number, process_id in number ,
140 appltop_path in varchar2,
141 node_name in varchar2,
142 inactive_flag out nocopy varchar2,
143 p_invokdir in varchar2)
144 is
145 dummy_number number;
146 begin
147 --
148 /*Get the Admin lock */
149 get_admin_lock(dummy_number);
150 --
151 --
152 /* Update the process_id and set the STATUS to 'ACTIVE' */
153 update ad_sessions set
154 OS_PROCESS_ID=process_id,
155 STATUS='ACTIVE',
156 LAST_UPDATE_DATE=sysdate,
157 APPL_TOP_PATH=appltop_path,
158 INVOKDIR=p_invokdir,
159 RUN_ON_NODE=node_name
160 where
161 session_id=sess_id;
162 --
163 --
164 update AD_WORKING_RES_LOCKS set
165 SESSION_STATUS='ACTIVE'
166 where
167 SESSION_ID=sess_id;
168 --
169 --
170 /* Get the ANY_CHANGE_WHILE_INACTIVE_FLAG for this session from ad_sessions */
171 select nvl(ANY_CHANGE_WHILE_INACTIVE_FLAG,'N')
172 into inactive_flag
173 from ad_sessions
174 where session_id=sess_id;
175 --
176 --
177 --
178 commit;
179 /* release the Admin lock */
180 release_admin_lock(dummy_number);
181 --
182 end reset_session_info;
183 --
184 --
185 procedure set_session_info(sess_id in number ,process_id in number,
186 utility_nm in varchar2,appltop_id in number,
187 priority_value in number,osuser_name in varchar2,
188 invokedir in varchar2, appltop_path in varchar2,
189 node_name in varchar2, p_topdir in varchar2)
190 is
191 lock_status number;
192 session_status varchar2(30);
193 session_exists number:=0;
194
195 exst_session_id number;
196 exst_utility_name varchar2(8);
197 exst_appl_top_id number;
198 exst_topdir varchar2(256);
199 exst_status varchar2(30);
200 exst_priority number;
201 exst_os_user_name varchar2(30);
202 exst_appltop_path varchar2(256);
203 exst_node_name varchar2(31);
204 begin
205 --
206 -- Get db admin lock
207 --
208 if utility_nm = 'adctrl' then
209 get_admin_lock_with_timeout(lock_status, 3);
210 else
211 get_admin_lock(lock_status);
212 end if;
213
214 --
215 --
216 SELECT count(*) INTO session_exists from ad_sessions
217 where session_id=sess_id;
218 --
219 if session_exists > 1 then
220 -- should not happen but in case the primary key index is dropped
221 raise_application_error(-20000,
222 'Fatal error Duplicate session_id in AD_SESSIONS: set_session_info()');
223 end if;
224
225
226 if session_exists = 1 then
227 -- if a row exists with the same session_id then
228 -- check if it is the same utility, APPL_TOP and start_date
229 -- as the one we are trying to insert
230 -- if they same then need not insert the record again. This situation
231 -- can occur when a row was created in ad_sessions table and failed before
232 -- updating the session_info_file.
233 -- In the restart case we need to just update the STATUS column and
234 -- process_id column for that row in the ad_sessions
235 select
236 SESSION_ID,UTILITY_NAME,APPL_TOP_ID,
237 TOPDIR,STATUS,PRIORITY,
238 OS_USER_NAME
239 into
240 exst_session_id,exst_utility_name,exst_appl_top_id,
241 exst_topdir,exst_status,exst_priority,
242 exst_os_user_name
243 from ad_sessions
244 where
245 session_id=sess_id;
246 --
247 if exst_session_id = sess_id and
248 exst_utility_name = utility_nm and
249 exst_appl_top_id = appltop_id and
250 exst_topdir = p_topdir and
251 exst_priority = priority_value and
252 exst_os_user_name = osuser_name then
253 -- if exists then just update the sttus to ACTIVE
254 update AD_SESSIONS set status='ACTIVE'
255 where session_id=sess_id;
256 else
257 raise_application_error(-20000,
258 'Fatal error Duplicate session_id in AD_SESSIONS: set_session_info()');
259 end if;
260 else
261 -- if here this means that a new row has to be inserted
262 INSERT INTO AD_SESSIONS
263 (
264 SESSION_ID,UTILITY_NAME,APPL_TOP_ID,
265 INVOKDIR,STATUS,PRIORITY,
266 OS_PROCESS_ID,OS_USER_NAME,
267 START_DATE,CREATION_DATE,LAST_UPDATE_DATE,
268 APPL_TOP_PATH, RUN_ON_NODE, TOPDIR
269 )
270 VALUES
271 (
272 sess_id,
273 utility_nm,
274 appltop_id,
275 invokedir,
276 'ACTIVE',
277 priority_value,
278 process_id,
279 osuser_name,
280 SYSDATE,
281 SYSDATE,SYSDATE,
282 appltop_path, node_name, p_topdir
283 );
284 end if;
285 --
286 -- Release db admin lock
287 --
288 release_admin_lock(lock_status);
289 --
290 --
291 -- commit;
292 commit;
293 --
294 --
295 return;
296 --
297 --
298 end set_session_info;
299 --
300 --
301 procedure deactivate_session_row(sess_id in number,
302 get_lock_with_timeout in number,
303 row_count out nocopy number)
304 is
305 dummy_number number;
306 begin
307 --
308 /*Get the Admin lock */
309 if get_lock_with_timeout = 0 then
310 get_admin_lock(dummy_number);
311 else
312 get_admin_lock_with_timeout(dummy_number, 3);
313 end if;
314 --
315 --
316 /* Update the STATUS to 'INACTIVE' */
317 --
318 update AD_SESSIONS set
319 STATUS='INACTIVE',
320 LAST_UPDATE_DATE=SYSDATE
321 where
322 SESSION_ID=sess_id;
323 --
324 update AD_WORKING_RES_LOCKS set
325 SESSION_STATUS='INACTIVE'
326 where
327 SESSION_ID=sess_id;
328 --
329 row_count := sql%rowcount;
330 /* commit */
331 --
332 commit;
333 --
334 /* release the Admin lock */
335 release_admin_lock(dummy_number);
336 --
337 --
338 exception
339 when no_data_found then
340 row_count := 0;
341 end deactivate_session_row;
342
343
344 -- Procedure
345 -- check_compatibility_with_fifo
346 -- Arguments
347 -- 1) session_id
348 -- 2) x_conflict_session_ids - Out parameter
349 -- List of conflict session ids.
350 -- 3) error_code - Out parameter
351 -- 0 --> Success
352 -- BASE_ERROR + 1 --> Invalid Argument
353 -- BASE_ERROR + 2 --> Unhandled exception
354 -- Purpose
355 -- This procedures does compatibility checks in DB. It checks set
356 -- of locks requested can be acquired (together) or not. It also
357 -- forces FIFO algorithm amoung same priority sessions
358 -- Notes
359
360 procedure check_compatibility_with_fifo (session_id in number,
361 x_conflict_session_ids out
362 nocopy conflict_sessions_t,
363 error_code out nocopy number)
364 is
365 BASE_ERROR number; /* This will be used, if we want to
366 * propogate error number to the main
367 * (for generating unique error number
368 * - in future)
369 */
370 x_session_id number;
371 begin
372
373 BASE_ERROR := 0;
374 x_session_id := session_id;
375 error_code := 0; /* No error */
376
377 /* Validate the arguments passed */
378 if (session_id <= 0) then
379 error_code := BASE_ERROR + 1; /* Invalid argument */
380 raise_application_error(-20000,
381 'Fatal error in check_compatibility_with_fifo() - '||
382 to_char(error_code));
383 end if;
384
385 select distinct wl.session_id
386 bulk collect into x_conflict_session_ids
387 from ad_working_res_locks wr, /* Requested */
388 ad_working_res_locks wl /* Locked */
389 where wr.session_id = x_session_id and
390 wl.session_id <> x_session_id and /* Look at other sessions */
391 wl.resource_code = wr.resource_code and
392 ( wl.appl_top_id = -5 or
393 wr.appl_top_id = -5 or
394 wl.appl_top_id = wr.appl_top_id ) and
395 ( wl.context = 'ALL' or
396 wr.context = 'ALL' or
397 wl.context = wr.context ) and
398 ( wl.language = 'ALL' or
399 wr.language = 'ALL' or
400 wl.language = wr.language ) and
401 ( wl.extra_context1 = 'ALL' or
402 wr.extra_context1 = 'ALL' or
403 wl.extra_context1 = wr.extra_context1 ) and
404 ( wl.extra_context2 = 'ALL' or
405 wr.extra_context2 = 'ALL' or
406 wl.extra_context2 = wr.extra_context2 ) and
407 ( wl.extra_context3 = 'ALL' or
408 wr.extra_context3 = 'ALL' or
409 wl.extra_context3 = wr.extra_context3 ) and
410 wl.lock_mode = decode(wr.lock_mode,
411 'S', 'X',
412 'X', wl.lock_mode,
413 wl.lock_mode) and
414 wl.session_status = 'ACTIVE' and
415 ( wl.date_acquired is not null or
416 wl.session_priority > wr.session_priority or
417 (
418 wl.session_priority = wr.session_priority and
419 wl.date_requested < wr.date_requested
420 )
421 );
422
423 exception
424 when others then
425 error_code := BASE_ERROR + 2; /* Error, unhandled exception */
426 raise_application_error(-20000,
427 'Fatal error in check_compatibility_with_fifo() - '||
428 to_char(error_code) ||
429 substr(sqlerrm, 1, 100));
430
431 end check_compatibility_with_fifo;
432
433 -- Procedure
434 -- check_compatibility
435 -- Arguments
436 -- 1) session_id
437 -- 2) x_conflict_session_ids - Out parameter
438 -- List of conflict session ids.
439 -- 3) error_code - Out parameter
440 -- 0 --> Success
441 -- BASE_ERROR + 1 --> Invalid Argument
442 -- BASE_ERROR + 2 --> Unhandled exception
443 -- Purpose
444 -- This procedures does compatibility checks in DB. It checks set
445 -- of locks requested can be acquired (together) or not.
446 -- Notes
447
448 procedure check_compatibility (session_id in number,
449 x_conflict_session_ids out
450 nocopy conflict_sessions_t,
451 error_code out nocopy number)
452 is
453 BASE_ERROR number; /* This will be used, if we want to
454 * propogate error number to the main
455 * (for generating unique error number
456 * - in future)
457 */
458 x_session_id number;
459 begin
460
461 BASE_ERROR := 0;
462 x_session_id := session_id;
463 error_code := 0; /* No error */
464
465 /* Validate the arguments passed */
466 if (session_id <= 0) then
467 error_code := BASE_ERROR + 1; /* Invalid argument */
468 raise_application_error(-20000,
469 'Fatal error in check_compatibility() - '||
470 to_char(error_code));
471 end if;
472
473 select distinct wl.session_id
474 bulk collect into x_conflict_session_ids
475 from ad_working_res_locks wr, /* Requested */
476 ad_working_res_locks wl /* Locked */
477 where wr.session_id = x_session_id and
481 wr.appl_top_id = -5 or
478 wl.session_id <> x_session_id and /* Look at other sessions */
479 wl.resource_code = wr.resource_code and
480 ( wl.appl_top_id = -5 or
482 wl.appl_top_id = wr.appl_top_id ) and
483 ( wl.context = 'ALL' or
484 wr.context = 'ALL' or
485 wl.context = wr.context ) and
486 ( wl.language = 'ALL' or
487 wr.language = 'ALL' or
488 wl.language = wr.language ) and
489 ( wl.extra_context1 = 'ALL' or
490 wr.extra_context1 = 'ALL' or
491 wl.extra_context1 = wr.extra_context1 ) and
492 ( wl.extra_context2 = 'ALL' or
493 wr.extra_context2 = 'ALL' or
494 wl.extra_context2 = wr.extra_context2 ) and
495 ( wl.extra_context3 = 'ALL' or
496 wr.extra_context3 = 'ALL' or
497 wl.extra_context3 = wr.extra_context3 ) and
498 wl.lock_mode = decode(wr.lock_mode,
499 'S', 'X',
500 'X', wl.lock_mode,
501 wl.lock_mode) and
502 wl.session_status = 'ACTIVE' and
503 ( wl.date_acquired is not null or
504 wl.session_priority > wr.session_priority
505 );
506
507 exception
508 when others then
509 error_code := BASE_ERROR + 2; /* Error, unhandled exception */
510 raise_application_error(-20000,
511 'Fatal error in check_compatibility() - '||
512 to_char(error_code) ||
513 substr(sqlerrm, 1, 100));
514
515 end check_compatibility;
516
517 -- Procedure
518 -- do_deadlock_detection
519 -- Arguments
520 -- 1) root_session_id
521 -- 2) error_code - Out parameter
522 -- 0 --> Success
523 -- BASE_ERROR + 1 --> Deadlock detected, abort (caller is
524 -- the least priority process in the list
525 -- of process that caused the deadlock)
526 -- BASE_ERROR + 2 --> Deadlock detected, continue (caller is
527 -- not the least priority process)
528 -- BASE_ERROR + 3 --> No conflicting sessions found for root
529 -- session id.
530 -- BASE_ERROR + 4 --> Invalid Argument
531 -- BASE_ERROR + 5 --> check_compatibility failed
532 -- BASE_ERROR + 6 --> Reading ad_sessions failed
533 -- BASE_ERROR + 7 --> Unhandled exception
534 -- BASE_ERROR + 8 --> Logic error,tree nodes are not proper
535 -- Purpose
536 -- Deadlocks are detected in this procedure. See the various return
537 -- values (above) for exact functionality.
538 -- Notes
539
540 procedure do_deadlock_detection(root_session_id in number,
541 error_code out nocopy number)
542 is
543 BASE_ERROR number; /* This will be used, if we want to
544 * propogate error number to the main
545 * (for generating unique error number
546 * - in future)
547 */
548 session_list conflict_sessions_t; /* To store tree node
549 * values */
550 session_tree conflict_sessions_t; /* To store tree */
551 conflict_session_ids conflict_sessions_t;
552 session_max number;
553 loop_count number;
554 temp_session_count number;
555 num number;
556 dup_flag number;
557 deadlock_node_index number;
558 parent_node number;
559 child_node number;
560 parent_priority number;
561 child_priority number;
562 least_priority_node number;
563 begin
564
565 BASE_ERROR := 0;
566 error_code := 0; /* No error */
567
568 if (root_session_id <= 0) then
569 error_code := BASE_ERROR + 4; /* Invalid Argument */
570 raise_application_error(-20000,
571 'Fatal error in do_deadlock_detection() - '||
572 to_char(error_code));
573 return;
574 end if;
575
576 /* Do compatibility check for root session id */
577 check_compatibility_with_fifo(root_session_id, session_list,
578 error_code);
579
580 /* session_list is used as tree node values list, the actual tree
581 * structure is stored in session_tree
582 *
583 * Example
584 * session_tree 0 0 0 1 1 2 4
585 * session_list 101 103 105 107 108 110 113
586 *
587 * This is the BFS tree. root_node is not in the tree. The root
588 * node is nothing but root_session_id. It is not stored in the
589 * above shown data structures.
590 *
591 * Using session_tree, we can construct the tree. The tree node
592 * values are stored in session_list.
593 *
594 * In other words, session_list has the list of session_ids and
595 * corresponding session_tree element has the index of the parent
596 * element. If the session_tree element has value 0, the parent
597 * node is root node.
598 */
599 if (session_list.count = 0) then
600 error_code := BASE_ERROR + 3; /* No conflicting sessions */
601 return;
605
602 end if;
603
604 session_max := session_list.count;
606 /* Initialize session tree */
607 num := 1;
608 while (num <= session_max) loop
609 session_tree(num) := 0; /* parent node is root_session_id */
610 num := num + 1;
611 end loop;
612
613 loop_count := 1;
614
615 /* Do compatibility check - for all the elements in the
616 * list of session ids (conflicting with root session id, either
617 * directly or indirectly)
618 */
619 deadlock_node_index := 0;
620 while (loop_count <= session_max) loop
621
622 check_compatibility_with_fifo(session_list(loop_count),
623 conflict_session_ids,
624 error_code);
625
626 /* One or more conflicting sessions found */
627 if (conflict_session_ids.count > 0) then
628
629 temp_session_count := session_list.count;
630 /* Check whether newly found conflicting sessions (not
631 * necessarily directly conflicting with root session id)
632 * are already in the list of conflicting sessions. If it is
633 * not so, add them to the list. Also, check for deadlock.
634 */
635 for var in conflict_session_ids.first..conflict_session_ids.last loop
636 /* Check for deadlock */
637 if (conflict_session_ids(var) = root_session_id) then
638 deadlock_node_index := loop_count; /* Deadlock detected */
639 session_max := 0; /* Terminate the outer loop */
640 exit;
641 else
642 /* Add this session id to the list of conflicting sessions.
643 * If it is not available (list of sessions for which
644 * do_compatibility check need to be run).
645 * (Search is not optimized because, the number of sessions
646 * are always very less)
647 */
648
649 /** A small optimization here. restricting loop to
650 * "temp_session_count", instead of
651 * "session_list.count"
652 */
653 dup_flag := 0;
654 num := 1;
655 while (num <= temp_session_count) loop
656 if (conflict_session_ids(var) = session_list(num)) then
657 dup_flag := 1;
658 end if;
659 num := num + 1;
660 end loop;
661
662 if (dup_flag = 0) then
663 session_max := session_max + 1;
664 session_list(session_max) := conflict_session_ids(var);
665 session_tree(session_max) := loop_count; /* Parent node */
666 end if;
667 end if;
668 end loop;
669 end if;
670 loop_count := loop_count + 1;
671 end loop;
672
673 /* Deadlock detected. Check for the lowest priority process in
674 * the deadlock loop. If the current process is the lowest priority
675 * return a special value (the deadlock detected and forced
676 * to abort the session)
677 */
678 if (deadlock_node_index <> 0) then
679 child_node := session_list(deadlock_node_index);
680 begin
681 select priority into child_priority from ad_sessions
682 where session_id = child_node; /* Child node */
683 exception
684 when others then
685 error_code := BASE_ERROR + 6; /* Error, reading ad_sessions */
686 raise_application_error(-20000,
687 'Fatal error in do_deadlock_detection() - '||
688 to_char(error_code) ||
689 substr(sqlerrm, 1, 100));
690 end;
691
692 /* Traverse back from the leaf node where deadlock is detected and
693 * find the lowest priority process(node)
694 */
695 least_priority_node := 0;
696 while (deadlock_node_index <> 0) loop
697 if (session_tree(deadlock_node_index) = 0) then
698 parent_node := root_session_id;
699 else
700 parent_node := session_list(session_tree(deadlock_node_index));
701 end if;
702
703 begin
704 select priority into parent_priority from ad_sessions
705 where session_id = parent_node;
706 exception
707 when others then
708 error_code := BASE_ERROR + 6; /* Error, reading ad_sessions */
712 substr(sqlerrm, 1, 100));
709 raise_application_error(-20000,
710 'Fatal error in do_deadlock_detection() - '||
711 to_char(error_code) ||
713 end;
714
715 if (child_priority < parent_priority) then
716 least_priority_node := child_node;
717 else
718 least_priority_node := parent_node;
719 end if;
720 child_node := parent_node;
721 child_priority := parent_priority;
722 if (session_tree(deadlock_node_index) = 0) then
723 if (child_node = root_session_id) then
724 exit;
725 else
726 error_code := BASE_ERROR + 8; /* Error, tree construction */
727 raise_application_error(-20000,
728 'Fatal error in do_deadlock_detection() - '||
729 to_char(error_code));
730 end if;
731 end if;
732 deadlock_node_index := session_tree(deadlock_node_index);
733 end loop;
734
735 if (least_priority_node = root_session_id) then
736 error_code := BASE_ERROR + 1; /* Deadlock, current session is the
737 * least priority session, abort */
738 else
739 error_code := BASE_ERROR + 2; /* Deadlock, current session is not
740 * the least priority session */
741 end if;
742 end if;
743 return;
744 exception
745 when others then
746 error_code := BASE_ERROR + 7; /* Unhandled exception */
747 raise_application_error(-20000,
748 'Fatal error in do_deadlock_detection() - '||
749 to_char(error_code) ||
750 substr(sqlerrm, 1, 100));
751
752 end do_deadlock_detection;
753
754
755 -- Procedure
756 -- acquire_promote_db_locks
757 -- Arguments
758 -- 1) session_id
759 -- 2) stage_code
760 -- 3) acquire_admin_flag - If this flag is set to 1,ADMIN lock is
761 -- acquired at the beginning.
762 -- Otherwise, it is assumed that caller
763 -- has already acquired the lock.
764 -- 4) release_admin_flag - If this flag is set to 1,ADMIN lock is
765 -- released at the end.
766 -- Otherwise, it is assumed that caller
767 -- will release the lock later. (this
768 -- procedure retains ADMIN lock, only if
769 -- the return status is success)
770 -- 5) error_code - Out parameter
771 -- 0 --> Success
772 -- BASE_ERROR + 1 --> This stage is already done.
773 -- BASE_ERROR + 2 --> Deadlock detected, abort (caller is
774 -- the least priority process in the list
775 -- of processes that caused the deadlock)
776 -- BASE_ERROR + 3 --> Invalid argument.
777 -- BASE_ERROR + 4 --> Lock rows are not prestaged.
778 -- BASE_ERROR + 5 --> Inconsistency state (some of the lock
779 -- rows have done_flag='Y' and others
780 -- have done_flag='N')
781 -- It shouldn't happen.
782 -- BASE_ERROR + 6 --> Exception,reading ad_planned_res_lock
783 -- BASE_ERROR + 7 --> Exception, reading ad_sessions.
784 -- BASE_ERROR + 8 --> Exception, inserting row in
785 -- ad_working_res_locks.
786 -- BASE_ERROR + 9 --> Exception updating ad_sessions.
787 -- BASE_ERROR + 10 --> Error, do_compatibility check failed
788 -- BASE_ERROR + 11 --> Stage_code = 'ACQUIRE_HELD' and
789 -- compatibility check -> incompatibility
790 -- BASE_ERROR + 12 --> Exception, updating ad_sessions,
791 -- ad_working_res_locks
792 -- BASE_ERROR + 13 --> Exception, reading ad_sessions
793 -- BASE_ERROR + 14 --> Adctrl says to quit
794 -- BASE_ERROR + 15 --> Exception, updating ad_sessions,
795 -- ad_working_res_locks
796 -- BASE_ERROR + 16 --> Error calling do_deadlock_detection.
797 -- BASE_ERROR + 17 --> Exception, updating ad_sessions.
798 -- BASE_ERROR + 18 --> Exception, updating ad_sessions,
799 -- ad_working_res_locks
800 -- BASE_ERROR + 19 --> Exception, updating ad_sessions,
801 -- ad_working_res_locks
802 -- BASE_ERROR + 20 --> Unhandled exception
803 -- BASE_ERROR + 21 --> Error, Current Mode = X and Requested
804 -- mode = S in Promotion
805 -- BASE_ERROR + 22 --> Error, Locks need to be promoted is
806 -- not at all held.
807 -- BASE_ERROR + 23 --> Error, reading ad_essions
808 -- BASE_ERROR + 24 --> Special return value, Acquire lock
809 -- loop exhausted
810 -- 6) warning_code - Out parameter
811 -- 0 --> No warning
812 -- 1 --> ADMIN db lock is held already by this
813 -- connection. But an attempt is made to
817 -- 7) error_message - Out parameter
814 -- acquire once again.
815 -- 2 --> ADMIN db lock is not at all held. But
816 -- an attempt is made to release it.
818 -- - SQL errors are returned to the caller
819 -- using this variable.
820 -- Purpose
821 -- This procedures acquires and promotes locks for a stage (of a
822 -- session_id). This API also handles a special stage "ACQUIRE_HELD"
823 -- differently. In this special stage, this API simply tries to
824 -- reacquire all already held locks.
825 -- Notes
826
827
828 procedure acquire_promote_db_locks (session_id in number,
829 stage_code in varchar2,
830 acquire_admin_flag in number,
831 release_admin_flag in number,
832 sleep_duration_in_ms in number,
833 try_again_flag in number,
834 error_code out nocopy number,
835 warning_code out nocopy number,
836 error_message out nocopy varchar2)
837 is
838 type done_flag_t is table of varchar2(1) index by binary_integer;
839
840 BASE_ERROR number; /* This will be used, if we want to
841 * propogate error number to the main
842 * (for generating unique error number
843 * - in future)
844 */
845 x_done_flag done_flag_t;
846 x_stage_code varchar2(30);
847 x_session_id number;
848 x_priority number;
849 x_status varchar2(30);
850 x_wait_loop_time number;
851 x_control_code varchar2(1);
852 x_mode_count number;
853 held_lock number;
854 sleep_duration number;
855 lock_status number;
856 conflict_session_ids conflict_sessions_t;
857 conflict_session_count number;
858 loop_max_count number;
859 x_lock_count number;
860 loop_count number;
861 begin
862
863 BASE_ERROR := 0;
864 error_code := 0; /* No error */
865 warning_code := 0; /* No warning */
866 held_lock := 0;
867 sleep_duration := sleep_duration_in_ms/1000;
868
869 x_session_id := session_id;
870 x_stage_code := stage_code;
871 loop_max_count := 10;
872
873 /* Validate the arguments passed */
874 if (session_id <= 0 or stage_code = null) then
875 error_code := BASE_ERROR + 3; /* Error, invalid argument */
876 return;
877 end if;
878
879 /* If stage code is ACQUIRE_HELD, it is an attempt to
880 * re-acquire all acquired locks
881 */
882
883 if (x_stage_code <> 'ACQUIRE_HELD') then
884 /* Check whether this stage locks have been acquired and
885 * released earlier or not. (done_flag -> restart purpose)
886 *
887 * Are all the prestaged rows for this stage marked as
888 * Done (done_flag = 'Y') ?
889 */
890 begin
891
892 select distinct nvl(done_flag,'N') bulk collect into x_done_flag
893 from ad_planned_res_locks
894 where session_id = x_session_id and
895 stage_code = x_stage_code;
896
897 if (x_done_flag.count = 0) then
898 error_code := BASE_ERROR + 4; /* Error, no lock rows
899 * are prestaged */
900 goto acquire_error;
901 end if;
902
903 if (x_done_flag.count <> 1) then
904 error_code := BASE_ERROR + 5; /* Error, inconsistancy state,
905 * it shouldn't happend */
906 goto acquire_error;
907
908 end if;
909
910 if (x_done_flag(1) = 'Y') then
911 error_code := BASE_ERROR + 1; /* Special return status, this
912 * stage is done already */
913 goto acquire_error;
914 end if;
915 exception
916 when others then
917 error_message := substr(sqlerrm, 1, 100);
918 error_code := BASE_ERROR + 6; /* Error, reading
919 * ad_planned_res_locks */
920 goto acquire_error;
921 end;
922 end if;
923
924 /* Read priority and status from ad_sessions table */
925 begin
926 select priority, status into x_priority, x_status
927 from ad_sessions where session_id = x_session_id;
928 exception
929 when others then
930 error_message := substr(sqlerrm, 1, 100);
931 error_code := BASE_ERROR + 7; /* Error, reading
932 * ad_sessions */
933 goto acquire_error;
934 end;
935
936 if (acquire_admin_flag = 1) then
937 /* Acquire ADMIN lock */
938 get_admin_lock(lock_status);
939 if (lock_status <> 0) then
940 warning_code := 1; /* Warning, this connection
941 * holds the lock already */
942 end if;
943 end if;
944
945 held_lock := 1; /* Lock is held */
946
950 if (x_stage_code <> 'ACQUIRE_HELD') then
947 if (try_again_flag = 0) then
948 /* Request locks -> insert lock rows in ad_working_res_locks */
949 begin
951 insert into ad_working_res_locks
952 (
953 session_id,
954 resource_code,
955 context,
956 appl_top_id,
957 language,
958 extra_context1,
959 extra_context2,
960 extra_context3,
961 in_process_flag,
962 lock_mode,
963 date_requested,
964 date_acquired,
965 session_status,
966 session_priority
967 )
968 select x_session_id, p.resource_code, p.context, p.appl_top_id,
969 p.language,
970 p.extra_context1,
971 p.extra_context2,
972 p.extra_context3,
973 'Y',
974 p.lock_mode,
975 sysdate,
976 null,
977 x_status,
978 x_priority
979 from ad_planned_res_locks p
980 where p.session_id = x_session_id and
981 p.stage_code = x_stage_code and
982 p.action_flag in ('A', 'P', 'B') and
983 not exists (
984 /* It may be a restart and rows may already exist.
985 * (If the failure occurred in this procedure in
986 * the prior run)
987 */
988 select 'Already inserted'
989 from ad_working_res_locks w
990 where w.session_id = x_session_id and
991 w.resource_code = p.resource_code and
992 w.context = p.context and
993 w.appl_top_id = p.appl_top_id and
994 w.language = p.language and
995 w.extra_context1 = p.extra_context1 and
996 w.extra_context2 = p.extra_context2 and
997 w.extra_context3 = p.extra_context3 and
998 w.in_process_flag = 'Y');
999 end if;
1000 exception
1001 when others then
1002 rollback;
1003 error_message := substr(sqlerrm, 1, 100);
1004 error_code := BASE_ERROR + 8; /* Error, inserting row in
1005 * ad_working_res_locks */
1006 goto acquire_error;
1007 end;
1008
1009 /* Reset wait loop time */
1010 begin
1011 update ad_sessions set wait_loop_time = 0
1012 where session_id = x_session_id;
1013 commit work;
1014 x_wait_loop_time := 0;
1015 exception
1016 when others then
1017 rollback;
1018 error_message := substr(sqlerrm, 1, 100);
1019 error_code := BASE_ERROR + 9; /* Error, updating
1020 * ad_sessions */
1021 goto acquire_error;
1022 end;
1023 else
1024 begin
1025 select wait_loop_time into x_wait_loop_time from ad_sessions
1026 where session_id = x_session_id;
1027 exception
1028 when others then
1029 rollback;
1030 error_message := substr(sqlerrm, 1, 100);
1031 error_code := BASE_ERROR + 23; /* Error, reading
1032 * ad_sessions */
1033 goto acquire_error;
1034 end;
1035 end if;
1036
1037 loop_count := 0;
1038
1039 /* Loop for acquiring locks */
1040 loop
1041 if (loop_count > loop_max_count) then
1042 error_code := BASE_ERROR + 24; /* Special return value,
1043 * loop exhausted */
1044 goto acquire_error;
1045 end if;
1046
1047 loop_count := loop_count + 1;
1048
1049 /* Do compatibility checks in DB against "acquired" resource
1050 * locks (only ACTIVE sessions) and also "requested"
1051 * locks of higher priority ACTIVE sessions
1052 */
1053 check_compatibility_with_fifo(x_session_id, conflict_session_ids,
1054 error_code);
1055 if (error_code <> 0) then
1056 error_code := BASE_ERROR + 10; /* Error, doing compatibility_check */
1057 goto acquire_error;
1058 end if;
1059
1060 /* There are no conflicting sessions */
1061 if (conflict_session_ids.count = 0) then
1062 exit; /* Break out of the main loop */
1063
1064 /* There are conflicting sessions */
1065 else
1066 /* Check the stage_code for ACQUIRE_HELD stage */
1067 if (x_stage_code = 'ACQUIRE_HELD') then
1068 /* de-activate session-row in DB */
1069 begin
1070 update ad_sessions set status = 'INACTIVE'
1071 where session_id = x_session_id;
1072 update ad_working_res_locks set session_status = 'INACTIVE'
1073 where session_id = x_session_id;
1074 commit work;
1075 error_code := BASE_ERROR + 11; /* Exit point */
1076 goto acquire_error;
1077 exception
1078 when others then
1079 rollback;
1080 error_message := substr(sqlerrm, 1, 100);
1081 error_code := BASE_ERROR + 12; /* Error, updating rows in
1082 * ad_working_res_locks */
1083 goto acquire_error;
1087 else
1084 end;
1085
1086 /* stage_code <> ACQUIRE_HELD */
1088 /* Read control_code from ad_sessions */
1089 begin
1090 select control_code into x_control_code from ad_sessions
1091 where session_id = x_session_id;
1092 exception
1093 when others then
1094 error_message := substr(sqlerrm, 1, 100);
1095 error_code := BASE_ERROR + 13;/* Error,reading ad_sessions*/
1096 goto acquire_error;
1097 end;
1098
1099 /* Adctrl says to abort -> control_code is 'Q' ? */
1100 if (x_control_code = 'Q') then
1101
1102 /* Wipe out ad_sessions.control_code and
1103 * de-activate session-row in DB
1104 */
1105 begin
1106 update ad_sessions set control_code = null,
1107 status = 'INACTIVE'
1108 where session_id = x_session_id;
1109 update ad_working_res_locks set session_status = 'INACTIVE'
1110 where session_id = x_session_id;
1111 commit work;
1112 error_code := BASE_ERROR + 14;
1113 goto acquire_error;
1114 exception
1115 when others then
1116 rollback;
1117 error_message := substr(sqlerrm, 1, 100);
1118 error_code := BASE_ERROR + 15; /* Error, updating
1119 * ad_sessions, ad_working_res_locks */
1120 goto acquire_error;
1121 end;
1122
1123 /* control_code is not set to 'Q' */
1124 else
1125
1126 /* Detect dead lock */
1127 do_deadlock_detection(x_session_id, error_code);
1128
1129 if (error_code not in (0,1,2,3)) then
1130 error_code := BASE_ERROR + 16;
1131 goto acquire_error;
1132 end if;
1133
1134 /* Deadlock is detected and caller is the lowest
1135 * priority process, so abort
1136 */
1137 if (error_code = 1) then
1138 /* De-activate session-row in DB */
1139 begin
1140 update ad_sessions set status = 'INACTIVE'
1141 where session_id = x_session_id;
1142 update ad_working_res_locks set session_status = 'INACTIVE'
1143 where session_id = x_session_id;
1144 commit work;
1145 error_code := BASE_ERROR + 2;
1146 goto acquire_error;
1147 exception
1148 when others then
1149 rollback;
1150 error_message := substr(sqlerrm, 1, 100);
1151 error_code := BASE_ERROR + 17; /* Error, updating
1152 * ad_sessions, ad_working_res_locks */
1153 goto acquire_error;
1154 end;
1155 end if;
1156
1157 /* Release DB admin lock */
1158 release_admin_lock(lock_status);
1159 if (lock_status <> 0) then
1160 warning_code := 2; /* Warning, not at all held the lock */
1161 end if;
1162
1163 held_lock := 0; /* Lock is not held at present */
1164
1165 /* Delay or sleep here */
1166 dbms_lock.sleep(sleep_duration);
1167
1168 /* Get DB admin lock */
1169 get_admin_lock(lock_status);
1170 if (lock_status <> 0) then
1171 warning_code := 1; /* Warning, not at all held the lock */
1172 end if;
1173
1174 held_lock := 1; /* Lock is held at present */
1175
1176 /* Increment wait loop time */
1177 x_wait_loop_time := x_wait_loop_time + sleep_duration;
1178 begin
1179 update ad_sessions set wait_loop_time = x_wait_loop_time
1180 where session_id = x_session_id;
1181 commit work;
1182 exception
1183 when others then
1184 error_message := substr(sqlerrm, 1, 100);
1185 error_code := BASE_ERROR + 18; /* Error, updating
1186 * ad_sessions */
1187 goto acquire_error;
1188 end;
1189 end if;
1190 end if;
1191 end if;
1192 end loop;
1193
1194 if (stage_code <> 'ACQUIRE_HELD') then
1195 begin
1196 select count(*) into x_mode_count from ad_working_res_locks
1197 where session_id = x_session_id and
1198 lock_mode = 'X';
1199
1200 if (x_mode_count > 0) then
1201 update ad_sessions
1202 set any_change_while_inactive_flag = 'Y',
1203 locks_overridden_by = x_session_id
1204 where
1205 nvl(any_change_while_inactive_flag,'N') <> 'Y' and
1206 session_id in (
1207 select distinct wl.session_id
1208 from ad_working_res_locks wr, -- Requested
1209 ad_working_res_locks wl -- Locked
1210 where wr.session_id = x_session_id and
1211 wl.session_id <> x_session_id and
1212 wl.resource_code = wr.resource_code and
1213 ( wl.appl_top_id = -5 or
1214 wr.appl_top_id = -5 or
1215 wl.appl_top_id = wr.appl_top_id ) and
1216 ( wl.context = 'ALL' or
1220 wr.language = 'ALL' or
1217 wr.context = 'ALL' or
1218 wl.context = wr.context ) and
1219 ( wl.language = 'ALL' or
1221 wl.language = wr.language ) and
1222 ( wl.extra_context1 = 'ALL' or
1223 wr.extra_context1 = 'ALL' or
1224 wl.extra_context1 = wr.extra_context1 ) and
1225 ( wl.extra_context2 = 'ALL' or
1226 wr.extra_context2 = 'ALL' or
1227 wl.extra_context2 = wr.extra_context2 ) and
1228 ( wl.extra_context3 = 'ALL' or
1229 wr.extra_context3 = 'ALL' or
1230 wl.extra_context3 = wr.extra_context3 ) and
1231 wl.session_status = 'INACTIVE');
1232 end if;
1233
1234 select count(*) into x_lock_count from ad_working_res_locks
1235 where (
1236 resource_code || ':' || context || ':' || appl_top_id || ':' ||
1237 language || ':' || extra_context1 || ':' ||
1238 extra_context2 || ':' || extra_context3 || ':' ||
1239 decode (lock_mode, 'X', 'M', lock_mode)
1240 in
1241 (select wl.resource_code || ':' || wl.context || ':' ||
1242 wl.appl_top_id || ':' || wl.language || ':' ||
1243 wl.extra_context1 || ':' || wl.extra_context2 || ':' ||
1244 wl.extra_context3 || ':' || decode (lock_mode, 'S', 'M',
1245 lock_mode)
1246 from ad_planned_res_locks wl
1247 where session_id = x_session_id and
1248 stage_code = x_stage_code and
1249 action_flag = 'P') and
1250 session_id = x_session_id and
1251 in_process_flag = 'N' and
1252 date_acquired is not null);
1253
1254 if (x_lock_count <> 0) then
1255 rollback;
1256 error_code := BASE_ERROR + 21;
1257 goto acquire_error;
1258 end if;
1259
1260 select count(*) into x_lock_count from (
1261 select resource_code, context, appl_top_id,
1262 language,
1263 extra_context1,
1264 extra_context2,
1265 extra_context3
1266 from ad_planned_res_locks wl
1267 where session_id = x_session_id and
1268 stage_code = x_stage_code and
1269 action_flag = 'P'
1270 minus
1271 select resource_code, context, appl_top_id,
1272 language,
1273 extra_context1,
1274 extra_context2,
1275 extra_context3
1276 from ad_working_res_locks where (
1277 resource_code || ':' || context || ':' ||
1278 appl_top_id || ':' || language || ':' ||
1279 extra_context1 || ':' || extra_context2 || ':' ||
1280 extra_context3
1281 in
1282 (select wl.resource_code || ':' || wl.context || ':' ||
1283 wl.appl_top_id || ':' || wl.language || ':' ||
1284 wl.extra_context1 || ':' || wl.extra_context2 || ':' ||
1285 wl.extra_context3
1286 from ad_planned_res_locks wl
1287 where session_id = x_session_id and
1288 stage_code = x_stage_code and
1289 action_flag = 'P') and
1290 session_id = x_session_id and
1291 in_process_flag = 'N' and
1292 date_acquired is not null));
1293
1294 if (x_lock_count <> 0) then
1295 rollback;
1296 error_code := BASE_ERROR + 22;
1297 goto acquire_error;
1298 end if;
1299
1300 /* Acquire resource locks here */
1301 update ad_working_res_locks set date_acquired = sysdate
1302 where session_id = x_session_id and in_process_flag = 'Y';
1303
1304 /* Delete duplicate rows */
1305 delete from ad_working_res_locks where rowid in
1306 (select w.rowid from ad_working_res_locks w,
1307 ad_working_res_locks p
1308 where w.session_id = x_session_id and
1309 w.session_id = p.session_id and
1310 w.resource_code = p.resource_code and
1311 w.context = p.context and
1312 w.appl_top_id = p.appl_top_id and
1313 w.language = p.language and
1314 w.extra_context1 = p.extra_context1 and
1315 w.extra_context2 = p.extra_context2 and
1316 w.extra_context3 = p.extra_context3 and
1317 w.rowid <> p.rowid and
1318 w.in_process_flag = 'N');
1319
1320 update ad_working_res_locks set in_process_flag = 'N'
1321 where session_id = x_session_id and in_process_flag = 'Y';
1322
1323 update ad_planned_res_locks set done_flag = 'N' where
1324 session_id = x_session_id and stage_code = x_stage_code;
1325
1326 update ad_sessions set wait_loop_time = 0
1327 where session_id = x_session_id;
1328
1329 commit work;
1330 exception
1331 when others then
1332 rollback;
1333 error_message := substr(sqlerrm, 1, 100);
1334 error_code := BASE_ERROR + 19;
1335 goto acquire_error;
1336 end;
1337 end if;
1338
1339 if (release_admin_flag = 1) then
1340 /* Release DB admin lock */
1344 end if;
1341 release_admin_lock(lock_status);
1342 if (lock_status <> 0) then
1343 warning_code := 2; /* Warning, not at all held the lock */
1345 end if;
1346
1347 return;
1348
1349 <<acquire_error>>
1350
1351 if (held_lock = 1) then
1352 /* Release DB admin lock */
1353 release_admin_lock(lock_status);
1354 if (lock_status <> 0) then
1355 warning_code := 2; /* Warning, not at all held the lock */
1356 end if;
1357 end if;
1358
1359 return;
1360
1361 exception
1362 when others then
1363 rollback;
1364 error_message := substr(sqlerrm, 1, 100);
1365 error_code := BASE_ERROR + 20; /* Error, unhandled exception */
1366
1367 end acquire_promote_db_locks;
1368
1369
1370 -- Procedure
1371 -- release_demote_db_locks
1372 -- Arguments
1373 -- 1) session_id
1374 -- 2) stage_code
1375 -- 3) acquire_admin_flag - If this flag is set to 1,ADMIN lock is
1376 -- acquired at the beginning.
1377 -- Otherwise, it is assumed that caller
1378 -- has already acquired the lock.
1379 -- 4) release_admin_flag - If this flag is set to 1,ADMIN lock is
1380 -- released at the end.
1381 -- Otherwise, it is assumed that caller
1382 -- will release the lock later. (this
1383 -- procedure retains ADMIN lock, only if
1384 -- the return status is success)
1385 -- 4) error_code - Out parameter
1386 -- 0 --> Success
1387 -- BASE_ERROR + 1 --> This stage is already done.
1388 -- BASE_ERROR + 2 --> Invalid argument.
1389 -- BASE_ERROR + 3 --> Exception,reading ad_planned_res_locks
1390 -- BASE_ERROR + 4 --> Inconsistency state (some of the lock
1391 -- rows have done_flag='Y' and others
1392 -- have done_flag='N')
1393 -- It shouldn't happen.
1394 -- BASE_ERROR + 5 --> Exception,reading ad_planned_res_locks
1395 -- BASE_ERROR + 6 --> Unhandled exception
1396 -- BASE_ERROR + 7 --> Lock rows are not prestaged.
1397 -- BASE_ERROR + 8 --> Data error.Demote to 'X' is not valid
1398 -- BASE_ERROR + 9 --> Error, Locks need to be promoted is
1399 -- not at all held.
1400 -- 5) warning_code - Out parameter
1401 -- 0 --> No warning
1402 -- 1 --> ADMIN db lock is held already by this
1403 -- connection. But an attempt is made to
1404 -- acquire once again.
1405 -- 2 --> ADMIN db lock is not at all held. But
1406 -- an attempt is made to release it.
1407 -- 6) error_message - Out parameter
1408 -- - SQL errors are returned to the caller
1409 -- using this variable.
1410 -- Purpose
1411 -- This procedure releases and demotes locks for a stage.
1412 -- Notes
1413
1414 procedure release_demote_db_locks (session_id in number,
1415 stage_code in varchar2,
1416 acquire_admin_flag in number,
1417 release_admin_flag in number,
1418 error_code out nocopy number,
1419 warning_code out nocopy number,
1420 error_message out nocopy varchar2)
1421 is
1422 type done_flag_t is table of varchar2(1) index by binary_integer;
1423 x_done_flag done_flag_t;
1424 x_stage_code varchar2(31);
1425 x_session_id number;
1426 held_lock number;
1427 x_lock_count number;
1428 lock_status number;
1429 BASE_ERROR number;
1430 begin
1431
1432 BASE_ERROR := 0;
1433 error_code := 0; /* No error */
1434 warning_code := 0; /* No warning */
1435
1436 held_lock := 0;
1437
1438 x_session_id := session_id;
1439 x_stage_code := stage_code;
1440
1441 /* Validate the arguments passed */
1442 if (session_id <= 0) then
1443 error_code := BASE_ERROR + 2; /* Argument error */
1444 return;
1445 end if;
1446
1447 /* Check whether this stage locks have been acquired and
1448 * released earlier or not. (done_flag -> restart purpose)
1449 *
1450 * Are all the prestaged rows for this stage marked as
1451 * Done (done_flag = 'Y')
1452 */
1453 begin
1454 select distinct nvl(done_flag,'N') bulk collect into x_done_flag
1455 from ad_planned_res_locks
1456 where session_id = x_session_id and
1457 stage_code = x_stage_code;
1458
1459 if (x_done_flag.count = 0) then
1460 error_code := BASE_ERROR + 7; /* Error, no lock rows
1461 * are prestaged */
1462 goto release_error;
1463 end if;
1464
1465 if (x_done_flag.count <> 1) then
1466 error_code := BASE_ERROR + 4; /* Error, inconsistancy state,
1470
1467 * it shouldn't happend */
1468 goto release_error;
1469 end if;
1471 if (x_done_flag(1) = 'Y') then
1472 error_code := BASE_ERROR + 1; /* Special return status, this
1473 * stage is done already */
1474 goto release_error;
1475 end if;
1476
1477 exception
1478 when others then
1479 error_message := substr(sqlerrm, 1, 100);
1480 error_code := BASE_ERROR + 5; /* Error, reading
1481 * ad_planned_res_locks */
1482 goto release_error;
1483 end;
1484
1485
1486 if (acquire_admin_flag = 1) then
1487 /* Acquire ADMIN lock */
1488 get_admin_lock(lock_status);
1489 if (lock_status <> 0) then
1490 warning_code := 1; /* Warning, this connection
1491 * holds the lock already */
1492 end if;
1493 end if;
1494
1495 held_lock := 1; /* Lock is held */
1496
1497 /* Release or Demote locks */
1498 begin
1499 if (x_stage_code is not null) then
1500
1501 delete from ad_working_res_locks where (
1502 resource_code || ':' || context || ':' || appl_top_id || ':' ||
1503 language || ':' || extra_context1 || ':' ||
1504 extra_context2 || ':' || extra_context3
1505 in
1506 (select wl.resource_code || ':' || wl.context || ':' ||
1507 wl.appl_top_id || ':' || wl.language || ':' ||
1508 wl.extra_context1 || ':' || wl.extra_context2 || ':' ||
1509 wl.extra_context3
1510 from ad_planned_res_locks wl
1511 where session_id = x_session_id and
1512 stage_code = x_stage_code and
1513 action_flag in ('B', 'R')) and
1514 session_id = x_session_id
1515 );
1516
1517 select count(*) into x_lock_count from ad_planned_res_locks
1518 where session_id = x_session_id and
1519 stage_code = x_stage_code and
1520 action_flag = 'D' and
1521 lock_mode = 'X';
1522
1523 if (x_lock_count <> 0) then
1524 rollback;
1525 error_code := BASE_ERROR + 8; /* Error, Data Error
1526 * check prestage rows */
1527 goto release_error;
1528 end if;
1529
1530 select count(*) into x_lock_count from ad_planned_res_locks
1531 where session_id = x_session_id and
1532 stage_code = x_stage_code and
1533 action_flag = 'D';
1534
1535 update ad_working_res_locks set lock_mode = 'S' where (
1536 resource_code || ':' || context || ':' || appl_top_id || ':' ||
1537 language || ':' || extra_context1 || ':' ||
1538 extra_context2 || ':' || extra_context3
1539 in
1540 (select wl.resource_code || ':' || wl.context || ':' ||
1541 wl.appl_top_id || ':' || wl.language || ':' ||
1542 wl.extra_context1 || ':' || wl.extra_context2 || ':' ||
1543 wl.extra_context3
1544 from ad_planned_res_locks wl
1545 where session_id = x_session_id and
1546 stage_code = x_stage_code and
1547 action_flag = 'D') and
1548 session_id = x_session_id);
1549
1550 if (sql%rowcount <> x_lock_count) then
1551 rollback;
1552 error_code := BASE_ERROR + 9; /* Error, Data Error
1553 * check prestage rows */
1554 goto release_error;
1555 end if;
1556
1557 update ad_planned_res_locks set done_flag = 'Y' where
1558 session_id = x_session_id and stage_code = x_stage_code;
1559 else
1560 delete from ad_working_res_locks where session_id = x_session_id;
1561 end if;
1562 commit;
1563 exception
1564 when others then
1565 rollback;
1566 error_message := substr(sqlerrm, 1, 100);
1567 error_code := BASE_ERROR + 3; /* Error, reading
1568 * ad_planned_res_locks */
1569 goto release_error;
1570 end;
1571
1572 if (release_admin_flag = 1) then
1573 /* Release DB admin lock */
1574 release_admin_lock(lock_status);
1575 if (lock_status <> 0) then
1576 warning_code := 2; /* Warning, not at all held the lock */
1577 end if;
1578 end if;
1579
1580 commit;
1581 return;
1582
1583 <<release_error>>
1584
1585 if (held_lock = 1) then
1586 /* Release DB admin lock */
1587 release_admin_lock(lock_status);
1588 if (lock_status <> 0) then
1589 warning_code := 2; /* Warning, not at all held the lock */
1590 end if;
1591 end if;
1592
1593 return;
1594
1595 exception
1596 when others then
1597 rollback;
1598 error_message := substr(sqlerrm, 1, 100);
1599 error_code := BASE_ERROR + 6; /* Error, unhandled exception */
1600
1601 end release_demote_db_locks;
1602 --
1603 --
1604 procedure set_task_completed(p_session_id in number ,
1605 p_task_id in number ,
1606 p_completion_status in varchar2,
1607 p_end_stage_cd in varchar2,
1608 p_begin_stage_cd in varchar2)
1609 is
1610 begin
1611 --
1612 --
1613 DELETE FROM AD_PLANNED_RES_LOCKS
1614 WHERE SESSION_ID = p_session_id AND
1615 STAGE_CODE <> p_end_stage_cd AND
1616 STAGE_CODE <> p_begin_stage_cd;
1617 --
1618 --
1619 UPDATE AD_SESSION_TASKS
1620 SET COMPLETION_STATUS = p_completion_status,
1621 END_DATE = sysdate ,
1622 LAST_UPDATE_DATE = sysdate
1623 where session_id = p_session_id and
1624 task_number = p_task_id;
1625 --
1626 --
1627 commit;
1628 --
1629 --
1630 return;
1631 --
1632 --
1633 end set_task_completed;
1634 --
1635 --
1636 procedure mv_sessinfo_to_history(p_sess_id in number,
1637 p_complete_status in varchar2,
1638 p_error_code out nocopy number)
1639 is
1640 row_count number;
1641 begin
1642 --
1643 --
1644 p_error_code := 0;
1645
1646 INSERT INTO AD_SESSIONS_HISTORY
1647 ( SESSION_ID, UTILITY_NAME, APPL_TOP_PATH,
1648 RUN_ON_NODE, INVOKDIR, STATUS, PRIORITY,
1649 CONTEXT_INFO, ANY_CHANGE_WHILE_INACTIVE_FLAG, LOCKS_OVERRIDDEN_BY,
1650 JS_TOTAL_JOBS, JS_COMPLETED_JOBS, JS_REMAINING_JOBS,
1651 COMPLETION_STATUS,
1652 START_DATE, END_DATE, CREATION_DATE, LAST_UPDATE_DATE, TOPDIR
1653 )
1654 SELECT
1655 SESSION_ID,UTILITY_NAME, APPL_TOP_PATH,
1656 RUN_ON_NODE, INVOKDIR, STATUS, PRIORITY,
1657 CONTEXT_INFO, ANY_CHANGE_WHILE_INACTIVE_FLAG, LOCKS_OVERRIDDEN_BY,
1658 JS_TOTAL_JOBS, JS_COMPLETED_JOBS,JS_REMAINING_JOBS,
1659 p_complete_status,
1660 START_DATE,SYSDATE, CREATION_DATE, LAST_UPDATE_DATE, TOPDIR
1661 FROM
1662 AD_SESSIONS
1663 WHERE
1664 SESSION_ID=p_sess_id;
1665
1666 row_count := sql%rowcount;
1667 if row_count = 0 then
1668 p_error_code := 1;
1669 end if;
1670 --
1671 --
1672 DELETE FROM AD_SESSIONS
1673 WHERE session_id = p_sess_id;
1674 --
1675 --
1676 INSERT INTO AD_SESSION_TASKS_HISTORY
1677 (SESSION_ID,TASK_NUMBER,COMPLETION_STATUS,
1678 CONTEXT ,START_DATE ,END_DATE,CREATION_DATE,
1679 LAST_UPDATE_DATE)
1680 SELECT
1681 SESSION_ID,TASK_NUMBER,COMPLETION_STATUS,
1682 CONTEXT ,START_DATE , nvl(END_DATE,SYSDATE),SYSDATE,
1683 SYSDATE
1684 from
1685 AD_SESSION_TASKS
1686 where
1687 SESSION_ID=p_sess_id;
1688 --
1689 --
1690 DELETE FROM AD_SESSION_TASKS
1691 WHERE session_id = p_sess_id;
1692 --
1693 --
1694 commit;
1695 --
1696 --
1697 return;
1698 --
1699 end mv_sessinfo_to_history;
1700 --
1701 --
1702 end ad_conc_session_locks;