Selceting and updating records oracle Free non registration adult web cam

As shown above, it worked and it worked even when i ran this query without the AUTONOMOUS_TRANSACTION and from a different session.

selceting and updating records oracle-52

Until I rollback the updates which i performed in the first session, the plsql in the second session was not successful. first, I'll fill up a table, then run the pf31 block. WORLD declare 2 cursor c1 is 3 select PRS_WOO_PRCS_ID,prs_sts 4 from prcs_sts 5 where PRS_WOO_PRCS_ID = 'PF31' 6 for update of prs_sts; 7 begin 8 for c1_rec in c1 loop 9 update prcs_sts 10 set prs_sts = 'Y' 11 where current of c1; 12 end loop; 13 end; 14 / PL/SQL procedure successfully completed. WORLD declare 2 PRAGMA AUTONOMOUS_TRANSACTION; 3 cursor c1 is 4 select PRS_WOO_PRCS_ID,prs_sts 5 from prcs_sts 6 where PRS_WOO_PRCS_ID = 'PF31' 7 for update of prs_sts; 8 begin 9 for c1_rec in c1 loop 10 update prcs_sts 11 set prs_sts = 'Y' 12 where current of c1; 13 end loop; 14 end; 15 / declare * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 4 ORA-06512: at line 9 [email protected] In an atempt to understand this concept more clearly, I ran the example which you presented. I realised that there is something that i am grossly doing wrong.

I'll re-run the pf31 block again as an AUTONOMOUS (sub or nested transaction -- runs AS IF it were in another session) transaction to show that it would be blocked. WORLD declare 2 PRAGMA AUTONOMOUS_TRANSACTION; 3 cursor c1 is 4 select PRS_WOO_PRCS_ID,prs_sts 5 from prcs_sts 6 where PRS_WOO_PRCS_ID = 'PF32' 7 for update of prs_sts; 8 begin 9 for c1_rec in c1 loop 10 update prcs_sts 11 set prs_sts = 'Y' 12 where current of c1; 13 end loop; 14 COMMIT; 15 end; 16 / PL/SQL procedure successfully completed. The only difference I can see in your plsql and mine is the kind of data that was selected for update.

How to track the autonomous transaction with v$ views Thanks SQL declare 2 PRAGMA AUTONOMOUS_TRANSACTION; 3 cursor c1 is 4 select PRS_WOO_PRCS_ID,prs_sts 5 from prcs_sts 6 where PRS_WOO_PRCS_ID = 'PF31' 7 for update of prs_sts; 8 begin 9 for c1_rec in c1 loop 10 update prcs_sts 11 set prs_sts = 'Y' 12 where current of c1; 13 end loop; 14 end; 15 / declare * ERROR at line 1: ORA-06519: active autonomous transaction detected and rolled back ORA-06512: at line 10 SQL declare 2 PRAGMA AUTONOMOUS_TRANSACTION; 3 cursor c1 is 4 select PRS_WOO_PRCS_ID,prs_sts 5 from prcs_sts 6 where PRS_WOO_PRCS_ID = 'PF31' 7 for update of prs_sts; 8 begin 9 for c1_rec in c1 loop 10 update prcs_sts 11 set prs_sts = 'Y' 12 where current of c1; 13 end loop; 14 end; 15 / declare * ERROR at line 1: ORA-06519: active autonomous transaction detected and rolled back ORA-06512: at line 10 August 24, 2003 - am UTC umm, which example exactly are you running?

if you run the block of plsql code that DOES NOT GET BLOCKED -- yes, it needs either a commit or a rollback at the end of it. As you said "an autonomous transaction is purely a "you are doing it in your session" sort of thing.

What are the consequences of not specifying the table you want to lock? declare 2 pragma autonomous_transaction; 3 begin 4 update t1 set x = 1; 5 end; 6 / declare * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 4 [email protected] declare 2 pragma autonomous_transaction; 3 begin 4 update t2 set y = 1; 5 end; 6 / declare * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 4 another transaction cannot update t1 or t2 -- both locked [email protected] declare 2 pragma autonomous_transaction; 3 begin 4 update t1 set x = 1; 5 end; 6 / declare * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 4 [email protected] declare 2 pragma autonomous_transaction; 3 begin 4 update t2 set y = 1; 5 commit; 6 end; 7 / PL/SQL procedure successfully completed. If you don't specify the table or the column name in a "select for update" clause, in which order does Oracle acquire the locks (first t1, then t2 or first t2, then t1)? For example, FOR UPDATE OF DEP means that only the rows in WSH_DEPARTURES should be locked.

here we can and did update t2, but not t1 since t1.x was the "for update" column hopefully -- that shows you the consequences? If the order is not fixed then this perhaps has consequences in terms of deadlock (one session getting it in order (t1, t2) and the other getting it in the order (t2, t1). August 21, 2003 - pm UTC consider it "atomic" for all intents and purposes. the order for a given query would be given the same plans and all -- but it'll be a function of how the data is accessed. PICKING_LINE_DETAIL_ID FROM WSH_DEPARTURES DEP, WSH_DELIVERIES DEL, SO_LINE_DETAILS LD, SO_PICKING_LINE_DETAILS PLD WHERE DEP. The solution to this query is to specify the tables to be locked in the FOR UPDATE clause via the FOR option, or break the query into separate cursors such that each cursor locks a single table only.

Sometimes it's ok, but for our data, usually it's wrong.

--=Chuck And just to add another comment, "LIKE" ,"IN", and "BETWEEN" do not remove the Optimizer's ability to use an INDEX.

declare cursor c1 is select PRS_BSNS_SGMNT, PRS_BSNS_SUB_SGMNT, PRS_DATE, PRS_WOO_PRCS_ID,prs_sts from prcs_sts where PRS_BSNS_SGMNT = 'FRX' AND PRS_BSNS_SUB_SGMNT = 'USDINR' -- AND PRS_DATE = '28-MAR-02' AND PRS_WOO_PRCS_ID = 'PF31' for update of prs_sts; begin for c1_rec in c1 loop update prcs_sts set prs_sts = 'Y' where current of c1; end loop; end; / PL/SQL procedure successfully completed.

Tags: , ,