Updating using cursor oracle grtdating ru
So, in forms the logic is something akin to : 1) read the data out with select t.*, rowid from T 2) let the user look at the data 3) when the user decides to delete the "5'th" row in the result set, forms will issue: select t.*, rowid from T where t.c1 = :block.c1 and t.c2 = :block.c2 and .... Assume, at a point in execution all of them issue the following : select c1,c2,c3 from t1 where c2=nvl(rtrim(somevalue)) for update My understanding is that the 'lucky' process will lock the whole table for the duration that it needs to do the update, while all other will wait. December 18, 2002 - pm UTC not the whole table, just the rows that meet the criteria. but in this case you have: where DATABASE_COLUMN = constant (forget the functions - the value is a constant). It is when you have: where f(DATABASE_COLUMN) = value or where DATABASE_COLUMN = variant(say another column in the table) that the index range scan is not going to be used.and t.rowid = :block.rowid FOR UPDATE NOWAIT; if that returns ORA-54 (resource busy), you are notified someone else has that row locked. Oracle_Username = 'SCOTT' / SELECT c FROM t1 WHERE a = 1 AND b = 2 AND c = 2 FOR UPDATE NOWAIT; SELECT do. The reason being use of function in the where predicate, since it will do the full scan and hence lock the whole table. Tom, The function in the query act upon 'bind variables', i am assuming this is also treated as a constant.Tom, why does a SELECT FOR UPDATE statement still lock a row in the table, even though the select returned no rows? COLUMN Object_Name FORMAT A10 create table t1 ( a int , b int , c int ) / insert into t1 values (1,1,1); insert into t1 values (1,2,2); insert into t1 values (5,1,3); commit; update t1 set a = 3 where c = 2 / commit; SELECT do. One of the guys did it right -- they did a select for update, assuring them the row "is theirs".The other guy did it wrong, they blindly updated the data without checking to see if it was modified since they read it out.FOR UPDATE; begin for x in c1 loop some procedural code to look at X if ( some condition based on X ) then delete where current of c1; end if; end loop; end; you really do need the select for update to ensure that no one else updates your data. Now - either sessions would give the following result SQL select empno, ename, job from emp where empno=7369; EMPNO ENAME JOB ---------- ---------- --------- 7369 SMITH2 CLERK Isn't this a case of lost update ?Select for update is a programming tool -- use it when you need to. (not every transcation was using a for update nowait) April 09, 2003 - am UTC yes it is -- your application code is incorrect.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 ?Won't a simple DELETE also lock the rows thus achieving the same result ?
Do we care if someone tries to update them at the same time ?
March 03, 2004 - am UTC well, it is not "unknow" why it is holding the lock, they've ASKED for it.
They are not allowed to update, but that does not prevent them from explicitly locking data upon request and they requested it.
The record locks are released when the next commit or rollback statement is issued.
Hi Tom, I have a very basic question about the use of SELECT FOR UPDATE cursors especially when used with DELETE statements.
create table Employee( 2 ID VARCHAR2(4 BYTE) NOT NULL primary key, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 / Table created.