Re: Which query is best?

From: Syed Jaffar Hussain <sjaffarhussain_at_gmail.com>
Date: Tue, 15 Dec 2009 16:34:12 +0300
Message-ID: <97b7fd2f0912150534y59cf1019u4c22f26bd95db6bd_at_mail.gmail.com>

Thank you all for your valuable inputs. I had a little discussion with the developers and tried to understand their logic and questioned them what actually leads into this situation. They have explained me that, they have a SELECT .. FOR UPDATE and some other series of actions and then followed by an UPDATE command. It could be possible that a user might not have completed the previous transaction, (due to any technical or nontechnical reasons) might have tried with another session for the same action. Hence, they said, they are going to put the time out value at the end of SELECT .. FOR UPDATE command, probably 60 seconds time out for the lock.

Regards,

Jaffar

On Tue, Dec 15, 2009 at 4:22 PM, Niall Litchfield < niall.litchfield_at_gmail.com> wrote:

> comments in line
>
> On Tue, Dec 15, 2009 at 9:50 AM, Stephane Faroult <sfaroult_at_roughsea.com>wrote:
>
>>
>> And although I don't agree on everything with Celko, I have always found
>> he hits the nail on the head here
>> http://www.information-management.com/issues/20050601/1028737-1.html
>>
>> Four years old article, and still fresh ...
>
>
> It is a nice article, sadly I suspect it will still apply in 10 years from
> now.
>
>
>
>> SF
>>
>> Syed Jaffar Hussain wrote:
>> > My main idea behind looking for an alternative sql was to reduce the
>> > waiting time for the select time to avoid long locking period for the
>> > record.
>>
>
> snipped to hopefully avoid over quoting.
>
> Any chance of sharing the update and it's plan?, and maybe how it is called
> - I often see this sort of thing in a loop in pl/sql (or worse in the client
> language)
>
> <pseudocode>
> for <some loop condition> loop
> select <key values> where <current loop condition values>
> update something where key = <key values from previous select>
> end loop
> </pseudocode>
>
> nearly always what is meant is
>
> update something where <loop condition>
>
> :(
>
> It looks like your logic might be a little different though. As Stephane
> implies understanding the whole of the transaction and thinking through the
> appropriate business approach is often the best approach in cases like
> these. Incidentally do you have very many more iterations of this logic than
> you have user sessions (assuming it is an interactive query)
>
> Niall
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>

-- 
Best Regards,

Syed Jaffar Hussain
Oracle Certified Master (10g)
http://www.oracle.com/technology/ocm/shussain.html
Oracle ACE
http://apex.oracle.com/pls/otn/f?p=19297:4:4640302666204919::NO:4:P4_ID:186
OCP 8i,9i & 10g DBA
RAC Certified Expert
Official Oracle RAC SIG Representative for Saudi Arabian region  (
http://www.oracleracsig.org/)
I blog at
http://jaffardba.blogspot.com/
LinkedIn : http://www.linkedin.com/pub/syed-jaffar-hussain/2/a71/918
--------------------
"Winners don't do different things. They do things differently."

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 15 2009 - 07:34:12 CST