 | | | sys vs. "normal " User | sys vs. "normal " User 2007-09-05 - By GovindanK
Back
>On Tue, 4 Sep 2007 11:19:10 -0700, [1]"Jared Still"
<jkstill@(protected)> 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> @(protected)
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@(protected)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859 (See http://ISO-8859.ora-code.com)-1" />
<title>Re: sys vs. "normal" User</title>
</head>
<body dir="ltr">
<pre>>On Tue, 4 Sep 2007 11:19:10 -0700, <a href="mailto:jkstill@(protected)
.com">"Jared Still"<br /><jkstill@(protected)></a> said:<br />>
;Just curious - what's wrong with 'SELECT FOR UPDATE' ?<br />>--<br />>
;Jared Still<br />>Certifiable Oracle DBA and Part Time Perl Evangelist</pre>
<pre>Along the lines of what Jared said, here is an example:</pre><pre><strong
>Session 1:<br /></strong>SQL> update my_emp set empname=upper(empname);<br /
><br />1 row updated.<br /><br />SQL>? ? ? ? ? <br /><br /><strong>Session 2:
<br /></strong>SQL> set serveroutput on size unlimited;<br />SQL> DECLARE
<br />? 2? ? row_on_hold? ? ? ? VARCHAR2(1);<br />? 3? BEGIN<br />? 4? ? SELECT
'Y' INTO row_on_hold<br />? 5 ? ? ? FROM my_emp<br />? 6? ? ? WHERE empno=1 FOR
UPDATE OF empname NOWAIT<br />? 7? ? ? ;<br />? 8 ? ? dbms_output.put_line('I
got hold of this row..........');<br />? 9? ? EXCEPTION WHEN OTHERS THEN<br /
>10 ? ? ? IF sqlcode = -0054 THEN<br />11 ? ? ? ? ? dbms_output.put_line('This
row is locked..GO ELSEWHERE');<br />12 ? ? ? ELSE<br />13 ? ? ? ? ? dbms_output
.put_line('This row is not locked..');<br />14 ? ? ? END IF;<br />15? END;<br /
>16? /<br /><strong>This row is locked..GO ELSEWHERE<br /></strong><br />PL/SQL
procedure successfully completed.<br /><br />SQL><br /><br /><strong>Session
1:<br /></strong><br />SQL> rollback;<br /><br />Rollback complete.<br /><br
/>SQL> <br /><br /><strong>Session 2:<br /></strong>SQL> @(protected)<br />SQL>
; set serveroutput on size unlimited;<br />SQL> DECLARE<br />? 2? ? row_on
_hold? ? ? ? VARCHAR2(1);<br />? 3? BEGIN<br />? 4? ? SELECT 'Y' INTO row_on
_hold<br />? 5 ? ? ? FROM my_emp<br />? 6? ? ? WHERE empno=1 FOR UPDATE OF
empname NOWAIT<br />? 7? ? ? ;<br />? 8 ? ? dbms_output.put_line('I got hold of
this row..........');<br />? 9? ? EXCEPTION WHEN OTHERS THEN<br />10 ? ? ? IF
sqlcode = -0054 THEN<br />11 ? ? ? ? ? dbms_output.put_line('This row is locked
..GO ELSEWHERE');<br />12 ? ? ? ELSE<br />13 ? ? ? ? ? dbms_output.put_line(
'This row is not locked..');<br />14 ? ? ? END IF;<br />15? END;<br />16? /<br /
><strong>I got hold of this row..........<br /></strong><br />PL/SQL procedure
successfully completed.<br /><br />SQL></pre><pre><br /><br /><br />HTH<br /
>GovindanK<br /><br /></pre>
</body>
</html>
|
|
|