Home » Developer & Programmer » Forms » field initial value property and sequences (forms 6i oracle 10g)
field initial value property and sequences [message #401919] Thu, 07 May 2009 00:32 Go to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hello everyone,

I have a question regarding the practice of having a :sequences.my_sequence.nextval+1 hard-coded as initial value for a primary key field whose value is populated on insert at database level via a before-insert trigger. I have a form which is constructed so: There are, for its primary keys, sequences to assign unique serial numeric values. The thing is that when I was unit testing the form at development level, it worked fine. When the form went live, I came across a recurrent issue : users manipulating the form concurrently often have "Could not reserve record (2 tries)" message. It looks like users manipulating records (changing values) are blocking others which you might say is obvious. Though, I think there might be something else to this problem and I was just trying to figure out.

I used Toad's Session Browser whenever that was reported to me by users and found that :

1] Whenever a user modified a record on the block, a lock is acquired on the underlying table(non blocking though).

2] Sometimes some operations like inserts or updates hang - causing blocking locks on the same table and all other concurrent user-operations on the same table to hang too. (blocking locks for which I have no other choice than killing the culprit sessions)

3] Whenever these blocking locks happen, the Current Statement of the culprit sessions vary between :
"select my_sequence.nextval+1" and "select some fields from table for update of field my_field"

This is all weird to me as of what could cause this problem and what should I change in my form to make it work smoothly.


Below are some facts that might shed some light and help in diagnosing my form :


A. At database level, values for primary keys are populated for new records during a before insert trigger. So In my forms, the related block is defined with a DML Returning Value set to "Yes" to avoid a "Record already inserted" error whenever after a commit I try to modify that same record again.

B. On the block in forms, I define my primary key fields with the Initial Value property as ":sequences.my_sequence.nextval+1" so that for each new record I have a starting value. Also, I do requeries after saves to retrieve the real value of sqnu.

C.One particularity of this form is that for one block, the way you edit records is by positionning you on the grid (database data block) and editing the values on a control block populated via trigger (when-new-record-instance). the changes are passed back to the database-data block by a POST_BLOCK trigger fired on the control block.

D. I have a master-detail relation between two blocks based on the primary-foreign key and the default other properties.

That's sums up the case. Thanks in advance for your help.

regards,
Didier.


Re: field initial value property and sequences [message #401923 is a reply to message #401919] Thu, 07 May 2009 00:53 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No idea at the moment, sorry.

Though, what's the purpose of "+1" in ":sequences.my_sequence.nextval+1"?
Re: field initial value property and sequences [message #401924 is a reply to message #401923] Thu, 07 May 2009 01:04 Go to previous messageGo to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
well I recall I opted for the "+1" when I came across the bug when between the time I invoked the nextval and that when it actually created the record with the primary key, the sequenced would have hopped by 1.

regards,
Didier

[Updated on: Thu, 07 May 2009 01:05]

Report message to a moderator

Re: field initial value property and sequences [message #401968 is a reply to message #401924] Thu, 07 May 2009 02:57 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Search this forum for 'sequence'. If you can live with 'holes' use 'nextval' otherwise use 'max()+1' but ONLY do it in the 'pre-insert' trigger.

David
icon6.gif  Re: field initial value property and sequences [message #401975 is a reply to message #401919] Thu, 07 May 2009 03:19 Go to previous messageGo to next message
azamkhan
Messages: 548
Registered: August 2005
Senior Member
Dear Didier,

The problem of concurrently access or concurrently locking of users is a very common issue when you are running a live system.

The solution is that if there is a machenism that allow only one user to save record at a time and during that if anyother user try to work on that data your system should say that record is being updated or saved by another user please try again.

And I think that for this a useful machenism can be designed.

I hope this would help you.

Regards,
Azam Khan
Re: field initial value property and sequences [message #402055 is a reply to message #401919] Thu, 07 May 2009 07:43 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
As far as your locking issue is concerned I suspect the sequence is a red herring.
Sequences don't hold locks.

This is far more likely to be the culprit:
select some fields from table for update of field my_field

Does the real SQL say FOR UPDATE or FOR UPDATE NOWAIT?

Oracle Forms default locking mechanism uses NOWAIT, so if the record is locked it can report the fact to the user.

If there's no NOWAIT there then it's presumably custom code and it's probably the cause.

Becuase without NOWAIT oracle just sits and waits for the lock to be released.
If it's an oracle form that's issuing it then that'll only happen when the user who first locked the record clicks on save.
Re: field initial value property and sequences [message #402166 is a reply to message #401919] Thu, 07 May 2009 23:22 Go to previous messageGo to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hello cookiemonster,

Indeed the sql mentions NO WAIT. In fact the sql showing at the time of the lock is one generated from the Forms engine itself (as opposed to update and insert cmmands that i would have embedded as pl/sql) As a whole I stick to the good practice of relying solely upon forms db engine for record manipulation and this adds to my frustration when I see the form behaving in such a weird way.

I also would like to remind you of that point C. I mentioned in my statement:
Quote:
C.One particularity of this form is that for one block, the way you edit records is by positionning you on the grid (database data block) and editing the values on a control block populated via trigger (when-new-record-instance). the changes are passed back to the database-data block by a POST_BLOCK trigger fired on the control block.

At some point I feared this could be the code construct causing this issue. You see whenever I navigate through my grid, th currently selected record fields are populated to the control block for an eventual manipulation of values. Then, everytime the user clicks elsewhere, the control block's PSOT_BLOCK trigger would return the values (modified or not) to the corresponding record in the grid. But somehow I believe the same would apply if the user were made to directly change values from the grid itself, isn't it?

regards and thanks to the other contributors Wink

Didz
Re: field initial value property and sequences [message #402708 is a reply to message #402166] Tue, 12 May 2009 02:43 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Why are you using nextval+1? Using nextval is sufficient.

Why use an initial value AND then populate in the 'before_insert' trigger? Get rid of he initial value as you are overwriting it anyway.

David
Re: field initial value property and sequences [message #402771 is a reply to message #402708] Tue, 12 May 2009 07:38 Go to previous message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hello,

sorry for not having kept you guys updated with this post. In fact I've been through the form and managed to eliminate this Nextval mechanism. In fact it's possible to have it without and still avoiding the "Record has already been inserted" thing. The forms is in the process of being tested in staging and hopefully, the locking won't show up this time. I have even removed the master-detail relation that once prevailed in the form (I erally could do without it right from the start ...but thought it were smart to use this at that time).

best regards to you all
Didz
Previous Topic: OLE2 problem
Next Topic: connection description for remote database not found
Goto Forum:
  


Current Time: Sat Dec 03 06:18:34 CST 2016

Total time taken to generate the page: 0.04124 seconds