OraFAQ Forum: Forms » Table locks and forms operations hanging

Home » Developer & Programmer » Forms » Table locks and forms operations hanging (Forms 6i Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Table locks and forms operations hanging [message #388340]

Tue, 24 February 2009 06:46

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

Report message to a moderator

Re: Table locks and forms operations hanging [message #388668 is a reply to message #388340]

Wed, 25 February 2009 21:49

Kevin Meade
Messages: 2103
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

Report message to a moderator

Re: Table locks and forms operations hanging [message #388726 is a reply to message #388340]

Thu, 26 February 2009 00:38

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

Re: Table locks and forms operations hanging [message #388760 is a reply to message #388340]

Thu, 26 February 2009 02:15

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.

Report message to a moderator

Re: Table locks and forms operations hanging [message #388764 is a reply to message #388340]

Thu, 26 February 2009 02:30

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

Report message to a moderator

Re: Table locks and forms operations hanging [message #388806 is a reply to message #388340]

Thu, 26 February 2009 04:55

cookiemonster
Messages: 13960
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

Report message to a moderator

Re: Table locks and forms operations hanging [message #388826 is a reply to message #388340]

Thu, 26 February 2009 05:44

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

Report message to a moderator

Re: Table locks and forms operations hanging [message #388987 is a reply to message #388806]

Thu, 26 February 2009 21:24

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

Re: Table locks and forms operations hanging [message #389077 is a reply to message #388340]

Fri, 27 February 2009 03:41

cookiemonster
Messages: 13960
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"

Report message to a moderator

Previous Topic:	Combo list item, like Internet Explorer Address bar
Next Topic:	Change the Number of records Displayed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 01:06:47 CST 2024