Home » Developer & Programmer » Forms » Table locks and forms operations hanging (Forms 6i Oracle 10g)
Table locks and forms operations hanging [message #388340] Tue, 24 February 2009 06:46 Go to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hello everyone,

I need some guidance and clarifications on an issue. I often come across problems with table locks acquired by concurrent sessions on a given module giving rise to other user's windows hanging on delete operations and the like. There is a form that is shared among local users for performing some usual CRUD operations on a set of related tables. The thing is that it often happens that some users acquire locks on one of these tables and lock out others that are using the same form on other manupulations on the same underlying table. I wanted to know if that behaviour could be caused by the way the form was created/coded (I use parent-child relations) and if there were anything that could be done to work-around this instead of always having the Admin killing sessions around. I have noticed that some locks remain pending when users have left their terminal unattended or forgot to close the application (this might have nothing to do with our problem)

Your input is most welcome.


regards,
Didz
Re: Table locks and forms operations hanging [message #388668 is a reply to message #388340] Wed, 25 February 2009 21:49 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
when you say TABLE LOCK do you really mean table lock? These are generally only acquired in application code by issuance of a LOCK TABLE command, in which case your answer is the app is doing it.

Another other way to get table locks would be if you were doing some kind of DDL operation on them, but these will not hang around after the operation completes.

If your issue is row locking then maybe you have some "hidden" column updates going on and you are locking many rows even though your users are never actually changing any data. This was a common problem for many forms app where developers did not pay attention to how they filled in values on rows. You might consider testing the form in debug mode, tedious but often enlightening.

As for dead sessions, you might wish to turn on sqlexpire time or other such things in order to get your oracle sever to detect and kill dead and/or idle sessions. I was having a problem with this recently and my dba turned it on for me. Not sure if it has fixed my issue but I have not seen the problem since so maybe so.

Good luck, Kevin
Re: Table locks and forms operations hanging [message #388726 is a reply to message #388340] Thu, 26 February 2009 00:38 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
What 'Validation Unit' are you using at the form level?

What 'Locking Mode' are you using at the block level?

David
Re: Table locks and forms operations hanging [message #388760 is a reply to message #388340] Thu, 26 February 2009 02:15 Go to previous messageGo to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
First, thanks a lot for your feeds.

To answer both of your questions, in my forms, I do not use any explicit lock table commands. I do use some dml commands and block-level data manipulation though. I believe it might be this mixture of block and embedded dml that I run against the database that might be causing these delays.

My form validation unit is Item

My interaction mode Blocking

Isolation mode Read Comitted

example :
I have a block which represents a synthesis of records for which, each row might stand for a group of records. At this level, the user can choose to delete the whole bunch or he can decide to look further into it and manipulate the underlying records (sort of drill-down approach). It can happen sometimes that the delete method (which i implement by issuing a delete from... where... in pl/sql) hangs while there might be users manipulating records further down into the bunch. Obviously this explains that but the delete transaction actually never completes and in Toad session browser the table holding the records is shown as locked. Killing the the culprit session is the only way to overcome this.

furthermore I sometimes also get the 'Cannot reserve record for update/delete' thing too but it happens less often.

If that sheds more light on what I described earlier, feedback is still welcome Razz


regards,
Didz


P.S : I like that dead session suggestion, because I did mention that sometimes unattended terminals still had pending session on the database.
Re: Table locks and forms operations hanging [message #388764 is a reply to message #388340] Thu, 26 February 2009 02:30 Go to previous messageGo to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
...yes I forgot, as for block locking mode properties: Automatic (Forms default. same for key mode too).


regards,
Didz
Re: Table locks and forms operations hanging [message #388806 is a reply to message #388340] Thu, 26 February 2009 04:55 Go to previous messageGo to next message
cookiemonster
Messages: 12405
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you got unindexed foreign keys by any chance?
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754
Re: Table locks and forms operations hanging [message #388826 is a reply to message #388340] Thu, 26 February 2009 05:44 Go to previous messageGo to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Thanks cookiemonster,

actually I do have unindexed foreign keys. I think I can hear a bell ringing around here. Thanks a lot, I'll implement this solution and see how things work out.


regards,
Didz

Re: Table locks and forms operations hanging [message #388987 is a reply to message #388806] Thu, 26 February 2009 21:24 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
@cookiemonster,

Asking whether a poster has "unindexed foreign keys" is something I would never have considered. Thanks for the input.

David
Re: Table locks and forms operations hanging [message #389077 is a reply to message #388340] Fri, 27 February 2009 03:41 Go to previous message
cookiemonster
Messages: 12405
Registered: September 2008
Location: Rainy Manchester
Senior Member
de nada.

It's one of those things you never think about untill you get bitten by it.
After that it's generally the first thing that comes to mind when someone says "table locks"
Previous Topic: Combo list item, like Internet Explorer Address bar
Next Topic: Change the Number of records Displayed
Goto Forum:
  


Current Time: Tue Dec 06 02:40:58 CST 2016

Total time taken to generate the page: 0.12330 seconds