Re: SELECT NULL FROM DUAL FOR UPDATE NOWAIT

From: Jared Still <jkstill_at_gmail.com>
Date: Tue, 3 Jul 2007 09:19:46 -0700
Message-ID: <bf46380707030919r3c71307am65c928818575ffe9@mail.gmail.com>

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 - 11:19:46 CDT