| field initial value property and sequences [message #401919] |
Thu, 07 May 2009 00:32  |
 |
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 #402055 is a reply to message #401919] |
Thu, 07 May 2009 07:43  |
cookiemonster
Messages: 13963
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 #402771 is a reply to message #402708] |
Tue, 12 May 2009 07:38 |
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
