Locking Problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Locking Problem

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    Hi,

    When ever a person is updating a record in a table,others are experiencing the locks on that table. Also ,people who are not directly using that table are also experiencing the problem in saving their forms.

    Right now, we are looking at who is locking the object and asking him/her to come out of the form.

    Is there any parameter, which exclusively tells oracle to make it 'Row level locking'.

    How is this thing happening ?

    Badrinath

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    During updates oracle does do a row_level locking on the table. You can check this parameter ROW_LOCKING, it is by default set to ALWAYS, where oracle would obtain a row lock on the table on which it was updating. In your case, check the form application that is causing the table level lock and try to modify that application accordingly.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Jan 2001
    Posts
    642
    Sam,
    Along with the form application,this is happening even if some body is using the db tools like 'toad'.

    First thing for me to understand is whether it is the database related issue or the form based issue.

    Badrinath

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    The question is 'what are the two sessions involved in the block doing?'

    If, for example, the TOAD user runs an UPDATE, all the affected records will be locked until that use eventually gets around to doing a COMMIT. If an application user then tries to UPDATE one of these locked records, they will be stuck. This is completely expected.

    However, I will assume that we are actually dealing with completely different records in the same or different tables. In that case, there are only 2 possibilities:

    - There is a BITMAP index on the table.
    - You have an FK indexing issue. If you don't have an index on an FK, then if someone changes a parent PK or deletes a parent, it will lock all the related rows in the child. See http://oradoc.photo.net/ora8doc/DOC/...1/ch6.htm#1574 for more info.

    HTH,

    - Chris


  5. #5
    Join Date
    Nov 2000
    Posts
    416
    Chris, Can you post scripts that discover all these possible situations.
    An ounce of prevention is worth a pound of cure

Posting Permissions

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



Click Here to Expand Forum to Full Width