Won't a simple DELETE also lock the rows thus achieving the same result ?Is it absolutely essential to use SELECT FOR UPDATE for doing updates in an environment where the same data can be updated by multiple people ?
It does this since you read the row out at time T1 and you delete it at time T2. There are no TX (transaction) locks -- hence no rows are locked in that table.
It wants to make sure that in the time between T1 and T2 -- no one UPDATED that row. Dear Tom, Please consider this requirement: Field_Code Value Picked_up 10 10 No 20 15 No 10 20 No All the records with "Picked_up=No" are to be selected and populated into another table with "group by" on Field_Code, i.e.
It wants to make sure you do not delete the row without having at least looked at the modified information (that would what is commonly known as a "lost update"). Field_Code Value 10 30 20 15 The Picked_up is to be updated to 'Yes'.
It also does this to ensure that when the time comes, the delete will proceed in a non-blocking fashion. If we first do a Select for Update, we might have another record coming in when we group by subsequently. Regards, ed Wrote file 1 declare 2 cursor c is 3 select * from equipment_category_lookup for update nowait ; 4 i number; 5 begin 6 for c1 in c loop 7 update equipment_category_lookup 8 set equipment_category_lookup_seq = main_seq.nextval 9 where current of c; 10 end loop; 11* end; SQL / where current of c; * ERROR at line 9: ORA-06550: line 9, column 18: PLS-00404: cursor 'C' must be declared with FOR UPDATE to use with CURRENT OF ORA-06550: line 7, column 1: PL/SQL: SQL Statement ignored SQL ed Wrote file 1 declare 2 cursor c is 3 select * from equipment_category_lookup for update ; 4 i number; 5 begin 6 for c1 in c loop 7 update equipment_category_lookup 8 set equipment_category_lookup_seq = main_seq.nextval 9 where current of c; 10 end loop; 11* end; SQL declare 2 cursor c is 3 select * from emp for update of sal nowait ; 4 i number; 5 begin 6 for c1 in c loop 7 update emp 8 set sal = main_seq.nextval 9 where current of c; 10 end loop; 11 end; 12 / PL/SQL procedure successfully completed.
Hi Tom, I have a very basic question about the use of SELECT FOR UPDATE cursors especially when used with DELETE statements.
Could you please explain to me why someone would want to get an an exclusive lock on the rows that are to be deleted ?
Do we care if someone tries to update them at the same time ?
Also, if the result that was to be achieved with a combination of SELECT FOR UPDATE and DELETE CURRENT OF statement could be done in a single delete statement (with a sub-query) can it be error-prone in a multi-user env ?
The select for update forms puts on the row ensures o the row was not changed o the row will NOT be changed o the row is locked by you and you will be able to delete it. For Update and Group By cannot be used simultaneously. Tom, Just want to confirm the following : I have 4 identical (same code) processes running.