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.
Be careful when porting applications that use this extension.
According to the standard, the source value for a parenthesized sub-list of column names can be any row-valued expression yielding the correct number of columns.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.
The Ask TOM team is taking a break over the holiday season.
declare PRAGMA AUTONOMOUS_TRANSACTION; 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 = 'PF32' 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; COMMIT; end; / PL/SQL procedure successfully completed.
As shown above, it worked and it worked even when i ran this query without the AUTONOMOUS_TRANSACTION and from a different session.
Then, I tried the same thing with one of primary key disabled in the WHERE clause of the plsql.
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.
You should specify the exact table that you want to lock in your "for update" clause because otherwise you would end up locking all the tables - the order of the locking itself is not important (or is atomic - so does not come into play - it is the fact that more locks are acquired than necessary that is of concern. I don't think the alternative of breaking up into multiple cursors is a good idea - I would instead specify the tables to be locked in a single query. "SQL statements that lock rows should be analyzed carefully to insure that deadlock and lock ordering issues are avoided. Consider the following cursor that attempts to lock qualifying rows: CURSOR lock_departure(x_dep_id NUMBER) IS SELECT DEP. You should either break up the SQL statement into multiple single table cursors or specify the FOR August 22, 2003 - am UTC I think the deadlock concerns are way overrated here.