Re: Which query is best?
From: Marcin Przepiorowski <pioro1_at_gmail.com>
Date: Tue, 15 Dec 2009 10:45:51 +0000
Message-ID: <3f5690af0912150245l69b6d9b6o54189558d4da1946_at_mail.gmail.com>
On Tue, Dec 15, 2009 at 7:22 AM, Syed Jaffar Hussain <sjaffarhussain_at_gmail.com> wrote:
> Hi everyone,
>
> The following simple join query on one of our business critical RAC
> databases with two instances was frequently leading into a dead lock
> situation ultimately a causing database hang scenario:
>
> SELECT u.u_user_id, u..u_mcr_cust_id
> FROM table1 U,table2 A
> WHERE
> A.UCS_CHNL_ID = :b1 AND A.UCS_LOGIN_NAME = :b2 AND A.UCS_USER_ID =
> U.U_USER_ID FOR UPDATE
>
Date: Tue, 15 Dec 2009 10:45:51 +0000
Message-ID: <3f5690af0912150245l69b6d9b6o54189558d4da1946_at_mail.gmail.com>
On Tue, Dec 15, 2009 at 7:22 AM, Syed Jaffar Hussain <sjaffarhussain_at_gmail.com> wrote:
> Hi everyone,
>
> The following simple join query on one of our business critical RAC
> databases with two instances was frequently leading into a dead lock
> situation ultimately a causing database hang scenario:
>
> SELECT u.u_user_id, u..u_mcr_cust_id
> FROM table1 U,table2 A
> WHERE
> A.UCS_CHNL_ID = :b1 AND A.UCS_LOGIN_NAME = :b2 AND A.UCS_USER_ID =
> U.U_USER_ID FOR UPDATE
>
As I understand this query condition is based on user login name - When exactly this dead lock happen ? when this same user is trying to login twice from two different machines ? Maybe there are some old, unused and not committed sessions ?
regards,
Marcin Przepiorowski
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 15 2009 - 04:45:51 CST