oracle - Detecting a Deadlock in a Stored Procedure -

-



i trying write deadlock proof stored procedure. based on following concept.

i have been trying write stored procedure based on following concept. procedure try drop constraint on table , if in case detects deadlock situation, wait time before trying again. important thing should retry in case of deadlock or nowait error, other errors handled via exceptions.

procedure test  begin <<label>>  drop constraint on table  if (deadlock(ora-00060)/nowait error (ora-0054)) detected sleep 60 seconds  goto label  exception when others.

it great if of experts please me this. similar example highly helpful. thank help.

while people harbour irrational aversion goto remains true can implement same logic without using construct. true here: simple while loop necessary:

create or replace procedure drop_constraint_for_sure     ( p_table_name in varchar2       , p_constraint_name in varchar2     )     x_deadlock exception;     pragma exception_init(x_deadlock, -60);     x_nowait exception;     pragma exception_init(x_nowait, -54); begin     loop         begin             execute immediate 'alter table '|| p_table_name                           || ' drop constraint ' || p_constraint_name                          || ' cascade' ;             exit;         exception             when x_deadlock null;             when x_nowait null;         end;         dbms_lock.sleep(60);     end loop; end; /

note sleep function requires execute privilege on sys.dbms_lock. not granted default, if don't have you'll need ask friendly dba grant it.

also note implementation doesn't have form of abort. loop eternally, until constraint dropped or other exception occurs. in real life should include loop count additional exit test on threshold count. although in real life wouldn't want stored procedure anyway: prefer knowing possible when using table i'm trying alter.





wiki

Comments

Post a Comment

Popular posts from this blog

python - Read npy file directly from S3 StreamingBody -

-
i have many .npy (numpy) , .json files saved in s3, need download , load these files. for json files downloading files , loading dictionary using following code: obj = s3.object(bucket, key) response = obj.get() body = response['body'].read().decode('utf-8') data = json.loads(body) that way avoid writing .json file disk , reading it. now want same .npy files, equivalent? currently downloading file, saving temp file on disk , loading np.load avoid if possible. following https://stackoverflow.com/a/28196540/3509999 : obj = s3.object(bucket, key) io.bytesio(obj.get()["body"].read()) f: # rewind file f.seek(0) arr = np.load(f) wiki
Read more

kotlin - Out-projected type in generic interface prohibits the use of metod with generic parameter -

-
i start using kotlin , defined interface this: interface aadapter<vh : recyclerview.viewholder> { fun oncreateaviewholder(parent: viewgroup): vh fun onbindaviewholder(v: vh, position: int) } and when try use on code: class klasa ( private val adapter: aadapter<*> ) { fun dosth(){ //... val vh = this.adapter.oncreateaviewholder(parent) //on below line error adapter.onbindaviewholder(v, position) //... } } i error out-projected type 'aadapter<*>' prohibits use of 'public abstract fun onbindaviewholder(v: t, position: int): unit i tied add "in" or "out" definition i'm confused. how allow it. replace aadapter<*> aadapter<recyclerview.viewholder> . class klasa ( private val adapter: aadapter<recyclerview.viewholder> ) { // ... because have declared aadapter has type parameter vh type subclass of recyclerview....
Read more

Asterisk AGI Python Script to Dialplan does not work -

-
i'm stuck project have check database of sip extension incoming caller id , dial sip extension. below receive when run script on asterisk cli. -- registered sip '9125' @ 192.168.1.102:50142 > saved useragent "zoiper rv2.8.40" peer 9125 [2017-08-22 13:53:33] notice[5095]: chan_sip.c:24558 handle_response_peerpoke: peer '9125' reachable. (17ms / 2000ms) == using sip rtp tos bits 184 == using sip rtp cos mark 5 -- executing [0112617769@from-trunk:1] answer("sip/obitrunk1-0000000d", "") in new stack > 0x1ca50d0 -- probation passed - setting rtp source address 192.168.1.98:16834 -- executing [0112617769@from-trunk:2] agi("sip/obitrunk1-0000000d", "test.py") in new stack -- launched agi script /var/lib/asterisk/agi-bin/test.py args: ['/var/lib/asterisk/agi-bin/test.py'] env line: agi_request: test.py env line: agi_channel: sip/obitrunk1-0000000d env line: agi_language: en env line: a...
Read more