This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jul 14, 2011 10:18 PM by user151536 RSS

Locking in 11g when migrated from 10g

user151536 Newbie
Currently Being Moderated
Dear Experts,

Recently we have migrated our oracle database from 10gR2 to 11gR2. Currently we are using 10gR2 oracle application server and forms and reports for front end.

After migrating we are experiencing the locking issues. ( Some sessions are blocking other sessions). But in 10g we didnt experienced it. There is no changes in the objects after migration.

Please provide us what are the possiblities or how to solve this issue.

Thanks in Advance,

Regards,
Balaji M
  • 1. Re: Locking in 11g when migrated from 10g
    CKPT Guru
    Currently Being Moderated
    Recently we have migrated our oracle database from 10gR2 to 11gR2. Currently we are using 10gR2 oracle application server and forms and reports for front end.

    After migrating we are experiencing the locking issues. ( Some sessions are blocking other sessions). But in 10g we didnt experienced it. There is no changes in the objects after migration.

    Please provide us what are the possiblities or how to solve this issue.
    Check for the owner of the session who is blocking.. Is it any background process? or is this by normal users.
  • 2. Re: Locking in 11g when migrated from 10g
    Franck Pachot Journeyer
    Currently Being Moderated
    Hi,

    Referential integrity locking has changed since 11.1.0.6: An TM-RX lock is held on the parent where it was a TM-RS in previous versions, then blocking concurrent TM-Share. See: Bug 5909305 - Change to DML (TM) lock modes for foreign key constraints
    You may need to avoid TM-Share caused by unindexed foreign keys.
    Regards,
    Franck.
  • 3. Re: Locking in 11g when migrated from 10g
    anand prakash - oracle Pro
    Currently Being Moderated
    Hi,


    What is the wait event of the blocked session.

    Anand
  • 4. Re: Locking in 11g when migrated from 10g
    Iordan Iotzov Expert
    Currently Being Moderated
    Newer DB versions typically have better lock handling, so unless you hit a bug, you should expect less (unnecessary) blocking.

    Change of execution times of SQLs can cause the system to go to a blocked state. That is, it might be that your system was always susceptible to blocking, but SQL execution times in 10g did not push it into blocked mode.
    For instance, if an update statement takes 10ms in 10g, and 100ms in 11g, then we have higher chance of blocking in 11g simply because the locks are held longer.
  • 5. Re: Locking in 11g when migrated from 10g
    jgarry Guru
    Currently Being Moderated
    In dbconsole, you can see all instance locks, or you can find scripts for locks. Look at the objects involved, and the cursors of the sessions involved. Good chance it's the unindexed foreign key Franck mentioned, or app code involved with those objects.
  • 6. Re: Locking in 11g when migrated from 10g
    user151536 Newbie
    Currently Being Moderated
    Thank you all for your valuable reply.

    From the developer point of view i got some data

    Owner is shown as null for blocking session and my db user for blocked session.
    We are currently using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
    Lock Type : TM

    In our application we are using some .net Addin which calls a java program. That java program calls a DB package.procedure which inserts and update into a table with immediate commit. When we are trying to access the same table this lock occurs. There are no DDL in our codes.

    It would be great if you provide scripts to get all the required info about the lock.
    I dont have sysdba rights and sys pw to check in DB console.

    Thanks in advance.

    Regards,
    Balaji M

    Edited by: Suresh Kumar VS on Jun 29, 2011 10:26 AM
  • 7. Re: Locking in 11g when migrated from 10g
    anand prakash - oracle Pro
    Currently Being Moderated
    Hi,

    paste the output of

    select username,sid,serial#,status,event,program from v$session where program='frmweb.exe';


    Anand
  • 8. Re: Locking in 11g when migrated from 10g
    anand prakash - oracle Pro
    Currently Being Moderated
    Hi,

    It seems to be a Unindexed Foreign Keys issue.

    read http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html

    HTH
    Anand
  • 9. Re: Locking in 11g when migrated from 10g
    user151536 Newbie
    Currently Being Moderated
    --------------------------------------------------------------------------------------------------
    USERNAME     SID     SERIAL#     STATUS     EVENT     PROGRAM
    SLA     390     37473     ACTIVE     enq: TM - contention     frmweb.exe
    SLA     1429     20561     ACTIVE     enq: TM - contention     frmweb.exe
    SLA     1812     13419     ACTIVE     enq: TM - contention     frmweb.exe
    SLA     1998     44645     ACTIVE     enq: TM - contention     frmweb.exe
    --------------------------------------------------------------------------------------------------

    Please find the info as per your query.
  • 10. Re: Locking in 11g when migrated from 10g
    anand prakash - oracle Pro
    Currently Being Moderated
    enq: TM Contention seems to be Unindexed Foreign Keys issue.check the link i posted in my previous post.

    Anand
  • 11. Re: Locking in 11g when migrated from 10g
    Dom Brooks Guru
    Currently Being Moderated
    As has already been mentioned, FK locking keeps changing subtly / not so subtly across major releases.

    Tom Kyte has a nice summary here:
    http://www.oracle.com/technetwork/issue-archive/2010/10-nov/o60asktom-176254.html

    Bottom line:
    "And the rule in Oracle Database 11g Release 1 and later is that you will want an index 
    on the foreign key of the child table if you do any of the following: 
    
    Update the parent table primary key
    Delete from the parent table
    Use a merge that either updates the parent table primary key or deletes from the parent table "
    See also:
    http://richardfoote.wordpress.com/2010/11/10/oracle11g-new-locking-modes-when-policing-fk-constraints-a-wolf-at-the-door/
    http://jonathanlewis.wordpress.com/2010/02/15/lock-horror/
  • 12. Re: Locking in 11g when migrated from 10g
    user151536 Newbie
    Currently Being Moderated
    Thank you all for your reply.

    The table which seems causing the lock has primary key but no other table refering it.

    This problem is not there in 10gR2 DB.
  • 13. Re: Locking in 11g when migrated from 10g
    Franck Pachot Journeyer
    Currently Being Moderated
    Hi,
    Suresh Kumar VS wrote:
    The table which seems causing the lock has primary key but no other table refering it.
    When the lock occurs, you can check DBA_WAITERS, then identify what the waiting session is doing (WAITING_SESSION is its SID), and check which object is concerned (LOCK_ID1 is the OBJECT_ID for TM lock), and check what the holding session (HOLDING_SESSION is his SID) has done on that object (or on an object that is linked with referential integrity with it).

    Regards,
    Franck.
  • 14. Re: Locking in 11g when migrated from 10g
    user151536 Newbie
    Currently Being Moderated
    Thanks all for your support.

    After Creating the indexes on foreign Keys locks are gone.
    Still we are monitoring the DB and hope there will not be any locking issue again.

    Thanks once again for all your great help. :)

    Regards,
    Balaji M
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points