Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Lightbulb Unanswered: Orace locking Issues - Kindly help

    Hello


    Friends, kindly help me in the locking issue which is troubling me.

    Background

    We have shell scripts ( jobs) which call Oracle procedures. There are jobs uoq0150 and uoq0621 which run at the same time ( Average time of run of uoq0621 is 4 hours and uoq0150 is 2.5 hours). The procedures called by them often access the same tables and updates the same tables.

    Problem

    From the past 3 days I have observed that the job uoq0150 have taken around 5.5 hours to finish while the average time of job is 2.5 Hours.

    I monitored the job this morning and found that due to locking issues
    With Customer Termination (uoq0621) job the uoq0150 job was struck at same point for more than 3 hours. While both jobs were running we I cheeked-

    The SQL statement of uoq0150 SQL session was showing Update of
    'MEMBERSHIP_ACCOUNT_HISTORIES' struck from 2 hours.)

    SQL> SELECT owner, object_id, object_name, object_type, v$lock.type,
    v$lock.sid FROM dba_objects, v$lock
    WHERE object_id = v$lock.id1 AND object_name =
    'MEMBERSHIP_ACCOUNT_HISTORIES';


    OWNER OBJECT_ID
    ------------------------------ ----------
    OBJECT_NAME
    ------------------------------------------------------------------------------------------------------------------------
    OBJECT_TYPE TY SID
    ------------------ -- ----------
    OCEAN 20621
    MEMBERSHIP_ACCOUNT_HISTORIES
    TABLE TM 628

    OCEAN 20621
    MEMBERSHIP_ACCOUNT_HISTORIES
    TABLE TM 106



    SQL> Select sid , id1 , lmode ,request , type from v$Lock where sid in
    (628,106)

    SID ID1 LMODE REQUEST TY
    ---------- ---------- ---------- ---------- --
    106 20621 3 0 TM
    628 20621 3 0 TM


    Hence this above statement depicted that TM ( DML lock) was acquired on
    object (20621 -> Membership_account_histories ) by the sessions 106 (
    SQL session of uoq0150 ) and session 628 (SQL session of uoq0621) in Mode 3(Shared row exclusive mode).

    Once the customer termination job uoq0621 completed and session with
    Sid 628 terminated the uoq0150 job moved forward and completed in next few minutes.

    Ca you please advice what to do in such scenario. How can I avoid such locks. While the procedure called by uoq0621 keeps on working and processing the uo0150 procedure gets jammed at one point. How to release the lock and move it forward. Till the point session of uoq0621 remains the session created at uoq0150 stalls at same DML statement.
    The Commit is done in uoq0621 after every 100 records whihc means the COMMIT is done at regular intervals.

    Plz help me in

    1) How do the DML lock is acquired on same object by different sessions. Why the one session keeps on working and executing further DMLs while the others goes in the hang state.
    2) As soon as the first session gets completed and session terminates , how the lock released and the second session resumes its tasks

    Kindly advice me so that I can base my investigations on right track.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Basically the hung session cant update a record being updated by the other so you need to come up with some way of elimanting or reducing the chance of working on a particular record simultaneously. A simple way would be to run one job after the other, alternatively merge the two jobs into a single procedure.

    Alan

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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
    /
    IIRC, tables have a default value of 1 ITL.
    If your tables have an ITL of 1, the 2nd session will go into ENQUEUE wait very quietly & will wait indefinitely for an available ITL slot to become available.
    Needless to say, the fix is to (re)create the table with INITRANS>1

    HTH & YMMV
    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
  •