Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SELECT NULL FROM DUAL FOR UPDATE NOWAIT

Re: SELECT NULL FROM DUAL FOR UPDATE NOWAIT

From: John Darrah <darrah.john_at_gmail.com>
Date: Tue, 3 Jul 2007 11:31:26 -0600
Message-ID: <ec40ac060707031031j68c4415eyfb6da7113eb690d9@mail.gmail.com>


Does the app run inside an app server? I've seen jboss run this select before. Alexander is correct, SQL*Plus will do this as well. I don't remember the exact realease this stopped but I think there is a thread on ask tom the gives the version this sql statement was removed from sql*plus. Either way, your developers are probably not lying when they deny writing that particular piece of code, 99% chance its the app server or sql*plus.

On 7/3/07, Jared Still <jkstill_at_gmail.com> wrote:
>
> On 7/3/07, Sandra Becker <sbecker6925_at_gmail.com> wrote:
> >
> > RHEL4, 9.2.0.8
> >
> > Last Tuesday night we put some new code into production. The
> > performance has been slower than usual, but no one seems to believe that it
> > was the new code.
> >
>
> Prime reason that DBA's often to not trust developers.
>
>
>
> >
> > SELECT NULL FROM DUAL FOR UPDATE NOWAIT;
> >
> > No one is owning up to writing this code and I can prove it came from
> > the application.
> >
>
> Of course not.
>
>
>
>
> > Questions:
> > 1) Could this statement have contributed to the overall problems?
> >
>
> Possibly.
>
> If they are silly enough to include use that statement to start with ( if
> they wont' own up to
> it, there's probably not a good reason for using it) they might also be
> silly enough to do
> something like this:
> declare
> not_ready exception;
> pragma exception_init(not_ready,-54);
> x integer;
> begin
> loop
> begin
> select null into x from dual for update nowait;
> exit;
> exception
> when not_ready then
> dbms_lock.sleep(1);
> when others then
> raise;
> end;
> end loop;
> end;
> /
>
> Who knows?
>
> Do they, um, "test" their changes by any chance?
> In a system that has realistic data and number of users?
>
>
>
> > 2) Is this something I should concern myself with or would I be
> > spinning my wheels?
> >
>
> Escalate it. After all, the database is being blamed, which implies to
> management that
> you are somehow not doing your job properly, which doesn't appear to be
> the case.
> How you escalate it is very dependent on company culture.
>
>
> 3) Why would anyone write a statement like this? What would be the
> > point? Since I don't know which developer wrote it and no one is owning up
> > to it, I haven't been able to find out what the desired result was supposed
> > to be.
> >
>
> Perhaps someones ingenious method to get a user lock, as implied by the
> preceding PL/SQL.
> Run the PL/SQL in 2 sessions.
>
> Perhaps the developers should be introduced to dbms_lock?
> Or maybe not, not knowing your devleopers.
>
>
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 03 2007 - 12:31:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US