Results 1 to 3 of 3

Thread: locking..

  1. #1
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Question Unanswered: locking..

    hello oracle DBAs,
    We are using oracle 9i rel 2 on solaris 8.
    one of the table is seldom getting locked in exclusive mode..
    Actually we are applying a delete statement on that table from a java program(application server is tomcat5.0).
    And the program just hangs there..when we open oracle enterprise manager console--sessions..we could see an exclusive lock on that table.when we killed the session from there then its working fine..
    We could not figure out coz. it happens seldom..and to our knowledge exclusive locks are applied explicitly using lock table command.
    could anybody help me in this regard..

    ..sridhar

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    While an exclusive lock may exist, it may not necessarily be the direct culprit.
    The "hung" session may be in enqueue wait.
    ===============================================
    select object_name, do.object_id, session_id, serial#, osuser, username, locked_mode , start_time, module
    from dba_objects do, v$session, v$locked_object lo, v$transaction
    where to_date(start_time,'MM/DD/YY HH24:MIS') < (sysdate-(1/1440))
    and locked_mode in (3,5,6)
    and session_id = sid
    and saddr = ses_addr
    and lo.object_id = do.object_id
    order by start_time desc
    /
    =============================================
    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
    /
    ================================================== ======
    One of hese two queries might provide more visibility into the situation.
    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
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    locking..

    thanks anacedent,
    Actually when we try view the details in enterprise manager console..its showing this table in exclusive lock..and that comes only this is excecuted from java program.
    And we executed the queries given by you.. but says no rows selected...
    we come to know that..we need to give commit after every 'x' number of rows..like 10..100..So..we tried the following procedure.. but when we execute it.. it hangs there..
    CREATE or replace PROCEDURE Proc_Delete(v_sess_id varchar2) AS
    cursor cur_dtl_ftr is SELECT a_number anum,b_number bnum
    FROM tbl_detail_filter where sess_id=v_sess_id;
    recno number := 0;
    begin
    for c1 in cur_dtl_ftr loop
    delete from tbl_detail_filter where a_number=c1.anum
    and b_number=c1.bnum;
    recno := recno + 1; -- Commit after every X records
    if recno > 10 then
    commit;
    recno := 0;
    end if;
    end loop;
    commit;
    end;

    Could you plz..help us to solve the problem..
    thanking you..
    sincerely
    sridhar

Posting Permissions

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