>On Tue, 4 Sep 2007 11:19:10 -0700, [1]"Jared Still"
<jkstill_at_gmail.com> 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
- mailto:jkstill_at_gmail.com
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 05 2007 - 20:06:25 CDT
