>On Tue, 4 Sep 2007 11:19:10 -0700, [1]"Jared Still"
<jkstill@xxxxxxxxx> said:
>Just curious - what's wrong with 'SELECT FOR UPDATE' ?
>--
>Jared Still
>Certifiable Oracle DBA and Part Time Perl Evangelist
Along the lines of what Jared said, here is an example:
Session 1:
SQL> update my_emp set empname=upper(empname);
1 row updated.
SQL>
Session 2:
SQL> set serveroutput on size unlimited;
SQL> DECLARE
2 row_on_hold VARCHAR2(1);
3 BEGIN
4 SELECT 'Y' INTO row_on_hold
5 FROM my_emp
6 WHERE empno=1 FOR UPDATE OF empname NOWAIT
7 ;
8 dbms_output.put_line('I got hold of this row..........');
9 EXCEPTION WHEN OTHERS THEN
10 IF sqlcode = -0054 THEN
11 dbms_output.put_line('This row is locked..GO ELSEWHERE');
12 ELSE
13 dbms_output.put_line('This row is not locked..');
14 END IF;
15 END;
16 /
This row is locked..GO ELSEWHERE
PL/SQL procedure successfully completed.
SQL>
Session 1:
SQL> rollback;
Rollback complete.
SQL>
Session 2:
SQL> @lck
SQL> set serveroutput on size unlimited;
SQL> DECLARE
2 row_on_hold VARCHAR2(1);
3 BEGIN
4 SELECT 'Y' INTO row_on_hold
5 FROM my_emp
6 WHERE empno=1 FOR UPDATE OF empname NOWAIT
7 ;
8 dbms_output.put_line('I got hold of this row..........');
9 EXCEPTION WHEN OTHERS THEN
10 IF sqlcode = -0054 THEN
11 dbms_output.put_line('This row is locked..GO ELSEWHERE');
12 ELSE
13 dbms_output.put_line('This row is not locked..');
14 END IF;
15 END;
16 /
I got hold of this row..........
PL/SQL procedure successfully completed.
SQL>
HTH
GovindanK
References
1. mailto:jkstill@xxxxxxxxx
