Re: SELECT NULL FROM DUAL FOR UPDATE NOWAIT
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: sbecker6925@xxxxxxxxx
- Date: Tue, 3 Jul 2007 09:19:46 -0700
On 7/3/07, Sandra Becker <sbecker6925@xxxxxxxxx> 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
Other related posts:
