if we have two tables in a join in a select statement and if we don't specify which table to lock in the for update clause, how does Oracle figure out which tables to lock. The FOR UPDATE clause can take additional optional arguments specifying the tables to be locked.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.From this it looks like the data that is selected for update has an impact on how SELECT FOR UPDATE works. supply a complete test case including o create table statement o any and all indexes o inserts My example did not even have a primary key... DEPARTURE_ID FOR UPDATE NOWAIT; The problem with this query is that the locking order is largely dependent on the execution plan and the row source order.My question is, is there any relation between the usage of SELECT FOR UPDATE clause and the primary key of a table? In any case, I cannot reproduce - you'll have to show us how to step by step by step: [email protected] For example, it is possible that the rows in SO_LINE_DETAILS can be locked before the rows in SO_PICKING_LINE_DETAILS. If one user ran this query under the RBO, and another user ran this query under the CBO, locking order issues could arise due to the likelihood of a plan difference.