Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2008
    Posts
    6

    Unanswered: Locking Problem: 2 unrelated tables

    Hello everyone,

    I have a locking problem I have not been able to solve. If I place an exclusive lock on one table (FEES), that stops a second table (JOB) from being inserted into.

    The thing is, the two tables are unrelated. There are no triggers on JOB, and JOB doesn't have any foreign keys to FEES.

    I know that it is the lock on FEES that is blocking my insert because once the FEES lock is released, the JOB insert goes through. I can also see the lock in OEM.

    Can anyone suggest anything on how I can figure out why the insert into JOB is blocked? I even checked database triggers, and we only have a login trigger that doesn't affect JOB or FEES.

    Thanks and cheers,
    Miguel
    Miguel,Pascoe@cgi.com

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >If I place an exclusive lock on one table (FEES),
    WHY?
    Almost without exception such action is unnecessary with Oracle.
    With Oracle readers do not block writers & writers do not block readers.
    So why do you think an exclusive lock on FEES is necessary?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2008
    Posts
    6
    Thanks for your reply.

    The locking comes from a legacy application that we cannot modify at the moment.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    by using SQL*Plus along with CUT & PASTE post results from whole session that clearly demonstrates what you claim to be happening?

    What OS name & version?
    Which Oracle version to 4 decimal places?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2008
    Posts
    6
    Thanks again.

    I'm using Oracle 10.2.0.3.0 - 64bit on a Sun Intel on Linux 2.6.9-42.ELsmp

    Session 1
    SQL*Plus: Release 10.2.0.3.0 - Production on Wed Apr 23 11:26:05 2008

    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options

    SEED @ luton in SQA> lock table ACCOUNT_FEE in exclusive mode;

    Table(s) Locked.


    Session 2

    SQL*Plus: Release 10.2.0.3.0 - Production on Wed Apr 23 11:26:37 2008

    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options

    SEED @ luton in SQA> INSERT INTO job(id, PROGRAM, executable, ... )
    VALUES( 'ZZZZZ', ... );
    <session hangs>


    Session 1
    SEED @ luton in SQA> rollback;

    Rollback complete.

    Session 2
    1 row created.

    SEED @ luton in SQA> select * from user_constraints uc1
    2 where constraint_type = 'R'
    3 and table_name = 'JOB'
    4 and r_constraint_name in (
    5 select constraint_name
    6 from user_constraints uc2
    7 where uc2.table_name = 'ACCOUNT_FEE'
    8 and uc2.constraint_type in ('U', 'P')
    9 );

    no rows selected

    SEED @ luton in SQA> select * from user_triggers where table_name = 'JOB';

    no rows selected

    SEED @ luton in SQA>

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Confirm the SessionID of each session involved before initiating the "hang".
    Then from 3rd session run the following query:

    Code:
    SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, 
    id1, id2, lmode, request, type
    FROM V$LOCK
    WHERE (id1, id2, type) IN
    (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
    ORDER BY id1, request
    /
    & post results back here
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Apr 2008
    Posts
    6
    Here you go

    Session 1
    SEED @ luton in SQA> SEED @ luton in SQA> select sid, serial# from v$session where audsid = sys_context('USERENV','SESSIONID');

    SID SERIAL#
    ---------- ----------
    357 45803

    1 row selected.

    SEED @ luton in SQA> lock table ACCOUNT_FEE in exclusive mode;

    Table(s) Locked.
    Session 2

    SEED @ luton in SQA> select sid, serial# from v$session where audsid = sys_context('USERENV','SESSIONID');

    SID SERIAL#
    ---------- ----------
    268 26788

    1 row selected.

    SEED @ luton in SQA> rollback;

    Rollback complete.

    SEED @ luton in SQA> INSERT INTO job(id, PROGRAM, executable, status, started, employee, DAY, phase,
    oracle_pid, oracle_user, unix_pid, unix_uid, unix_name, unix_host, arguments,
    release_name, release_sequence_id, session_id, expiry_date, owner_institution, owner_firm)
    2 3 VALUES('ZZZZY', LOWER('sac-s200'), 'SAC-S200', 'R', sysdate, 'VR', to_date('23-APR-2008', 'DD-MON-YYYY'),
    4 'D', 12345, USER, '12345', '12345', 'vriga', 'luton', null, null,
    5 null, decode(0, 0, NULL, 0), TRUNC(sysdate) + 30, null,
    6 null); 7

    <session hangs>
    Session 3
    SYS @ luton in SQA> SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
    id1, id2, lmode, request, type
    FROM V$LOCK
    2 WHERE (id1, id2, type) IN
    (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
    3 ORDER BY id1, request
    4 / 5 6 7

    SESS ID1 ID2 LMODE REQUEST TY
    ------------------------------------------------ ---------- ---------- ---------- ---------- --
    Holder: 357 206446 0 6 0 TM
    Waiter: 268 206446 0 0 2 TM

    2 rows selected.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Locking Problem
    Yes, you have a locking problem
    >2 unrelated tables
    Oracle disagrees with the contention that they are unrelated.
    I have to accept the fact that Oracle more accurately represents reality than you.

    Code:
    SELECT * FROM DBA_DEPENDENCIES WHERE NAME IN ('ACCOUNT_FEE','JOB');
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Apr 2008
    Posts
    6
    I agree -- they cannot be totally unrelated if locking one blocks the other. That is why I am asking for help in how to figure out what the connection is.

    I tried your query -- thanks. Unfortunately, it doesn't return anything:

    SYS @ luton in DEV> SELECT * FROM DBA_DEPENDENCIES WHERE NAME IN ('ACCOUNT_FEE','JOB');

    no rows selected

    Do you have any other suggestions?

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    try using dba_constraints (or all_constraints) instead of user_constraints in your query.
    --=cf

  11. #11
    Join Date
    Apr 2008
    Posts
    6
    Thanks for the suggestion. Unfortunately, also nothing.

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Please post results from following query
    Code:
    SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS
    WHERE OBJECT_NAME IN ('ACCOUNT_FEE','JOB')
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •