[Forms 4.5-10gR2] FRM-40501 unable to reserve record for update

**star** · 17/01/2007, 05h33

Bjr,
J'ai un comportement bizard avec Forms 4.5 et une BD 10gR2 que je n'ai pas avec une BD 7.3.4.
D'ailleurs, je ne l'ai pas non plus avec Forms 10gR2 et une BD 10gR2 !
Lorsque j'insère une ligne dans un bloc et que je committe, puis que je la modifie juste après, j'obtiens ce message d'erreur ?
S'auriez-vous pourquoi cette erreur se produit et comment la contourner ?
D'avance merci de votre aide
.

**star** · 19/01/2007, 04h03

Après quelques tests en live dans le doute, j'ai fait l'essai suivant :
Interogation de la base via le bloc de ma form, modifications successives de ma ligne avec commit après chaque modification.
Dans ce cas, je n'obtiens pas l'erreur, donc j'en ai déduit qu'elle se manifeste que lorsque j'insère une ligne et que la modifie juste après.
Bien, sauf que j'ai toujours l'erreur suite à l'insert !
Vos précieuses suggestions m'aideraient beaucoup
.

**taska** · 19/01/2007, 09h00

FYI

Subject: Getting FRM-40501, How is Lock Processing Handled By Oracle Forms?
Doc ID: Note:1004126.6 Type: PROBLEM
Last Revision Date: 22-MAR-2005 Status: PUBLISHED

Problem Description:
====================

You are receiving the following error from SQL*Forms or Oracle Forms:

FRM-40501 "ORACLE error : unable to reserve record for update or delete."

This Forms error may also be associated with:

ORA-00054 : resource busy and acquire with NOWAIT specified

Problem Explanation:
====================

FRM-40501 occurred while Oracle Forms was trying to select and lock the record
for update. How is lock record processing handled in Oracle Forms?

Solution Description:
=====================

1. Another user or process may be locking the record in the database that you
are trying to update or delete within your Forms application.

The user or process must release the lock, and you can retry the update or
delete. You may need to review the design of the Forms application
itself to determine if modifications need to be made to reduce locking
while running the application.

See the following Oracle Support Bulletins for additional
information on debugging lock errors in Forms:

2. Use the Display Error to determine if an Oracle Error occurred.

The Display Error key or menu option will invoke a dialog window that will
contain the SQL statement that Forms was trying to execute when the
FRM-40501 error occurred, and any ORACLE error that occurred.

Display Error can be invoked using the following methods:

a. Display Error is available from the HELP menu using the Forms Default
menu.

b. A [Display Error] key is available. Use SHOW KEYS to determine what
device-specific key is mapped to the Display Error functionality for
your application.

c. The Display Error functionality can be invoked programmatically using
the DISPLAY_ERROR Forms built-in.

Solution Explanation:
=====================

For Forms applications that are running against an ORACLE database, the
locking mode is immediate. This specifies that Forms should attempt to lock
the corresponding row immediately after an operator modifies an item value in
a queried record. Forms locks the record as soon as the operator presses a
key to enter or edit the value in a text item.

Locking Mode IMMEDIATE will acquire row locks on the database table under any
of the following conditions:

a. If the operator enters or edits a base table item
b. If the operator uses the Record->Lock menu item while running the form
c. If a change is made programmatically using an UPDATE or DELETE SQL
statement
d. If the DELETE_RECORD, ENTER_QUERY(FOR_UPDATE), EXECUTE_QUERY(FOR_UPDATE)
or LOCK_RECORD built-in functions are used

When a COMMIT or ROLLBACK is performed, the database releases the locks on
this data. CLEAR_RECORD will not release the row locks on this record.
CLEAR_BLOCK and CLEAR_FORM will only release locks if the commit_mode or
rollback_mode has been specified to perform a COMMIT or ROLLBACK.

Additional Information:
=======================

Oracle Documentation:
---------------------
Oracle Forms 4.X Reference Manual Volume 2,
Chapter 5, Properties
Locking Mode

SQL*Forms Version 3.0 Designer's Reference,
Chapter 3, Fundamental Processing
Locking

Oracle7 Server Application Developer's Guide,
Chapter 4, Processing SQL and PL/SQL Statements
Chapter 6, Maintaining Data Integrity

Solution Description:
=====================

The following row-level lock processing occurs when updating a record in a
base table block.

If user A types a character in a field, Forms sends a SQL SELECT statement to
the Oracle Server as follows:

SELECT * FROM table WHERE col1=:col1 AND col2=:col2 AND col3=:col3 AND
ROWID='xxxxxxxx.xxxx.xxxx' FOR UPDATE NOWAIT;
^
|
+-- known in Forms record after insert

If the row is already modified by user B, the Oracle server will return
ORA-00054 to alerting Forms User A that this row is modified by another user.

User B then commits the row.

If user A tries to modify the row again, Forms sends the same SQL statement
to the Oracle server and knows that the row longer exists with that exact
criteria because it was just updated by user B (NO_DATA_FOUND). Thus, user
A can now update the record and the FRM-40501 message does not occur.

Solution Explanation:
=====================

A SELECT... FOR UPDATE statement will lock all of the rows returned by the
query. Because Forms includes the ROWID in the WHERE clause, only one row is
ever returned and thus only one row is ever locked.

ROWID is database pseudocolumn that contains the exact address for a row,
uniquely identifying that one row in the database.

Additional Information:
=======================

Oracle Documentation:
---------------------
Oracle7 Server SQL Language Reference Manual,
Chapter 2, Elements of SQL
ROWID
Chapter 4, Commands
SELECT

Oracle Forms 4.5 Reference Manual,
Chapter 8, Processing Flowcharts
Lock the Row

Oracle Forms 4.0 Processing Manual
Chapter 3, Processing Flowcharts
Lock the Row

Solution Description:
=====================

The 'Locking Mode' block property controls when Oracle Forms will lock rows in
the database corresponding to modifying queried records in a Forms base table
block at runtime. This property can be set to 'Immediate' or 'Delayed'.

Immediate:
----------
If 'Locking Mode' is set to 'Immediate', the row is immediately locked upon
modification. Locking occurs as soon as a key is pressed to enter or edit
that value in the item. 'Immediate' is the default setting.

Delayed:
--------
If 'Locking Mode' is set to 'Delayed', the row is not locked until
commit-time, specifically until changes are 'posted' to the database. Oracle
Forms then compares the current value to the database value before issuing the
UPDATE statement. The user will get an error if the row has been updated
since it was fetched.

The 'Locking Mode' can be set at Design time or programmatically at Runtime:

Design Time:
------------
Set 'Locking Mode' in the block property sheet using the Oracle Forms Designer.

Runtime:
--------
'Locking Mode' can be changed at runtime with the SET_BLOCK_PROPERTY built-in.
For example:

SET_BLOCK_PROPERTY('block_name', LOCKING_MODE, IMMEDIATE);
or
SET_BLOCK_PROPERTY('block_name', LOCKING_MODE, DELAYED);

To find the current value of 'Locking Mode' at runtime, use the
GET_BLOCK_PROPERTY built-in. This function will return a character value
of 'IMMEDIATE' or 'DELAYED'.

lock_mode := GET_BLOCK_PROPERTY('block_name', LOCKING_MODE);

Solution Explanation:
=====================

The 'Locking Mode' property is included primarily for Oracle Forms
applications that are connecting to non-Oracle data sources. It is
recommended to use the default locking mode, IMMEDIATE, when connecting to an
Oracle database.

Additional Information:
=======================

Oracle Documentation:
---------------------
Oracle Forms 4.X Reference Manual, Volume 2
Chapter 5, Properties
Locking Mode

Solution Description:
=====================

Assume that you have a base table block based on the 'dept' table and wish to
suppress the FRM-40501 "Could not reserve record" Forms alert which gives you
an option to try again by pressing "yes" or don't try again by pressing
"no".

Use the On-Lock trigger to explicitly lock the record in the table and define
an exception in case the record was locked and handle the exception
accordingly.

By suppressing FRM-40501 you are defining the lock functionality to be
NO-WAIT. If the record is locked by another user, then do not wait for the
lock to be released, which basically implements the same behavior as pressing
the "NO" button on the Form alert. For example:

ON-LOCK Trigger : BLOCK Level

DECLARE
dummy VARCHAR2(1);
could_not_lock EXCEPTION;

/* Define Exception for ORA-00054, which occurs when an attempt */
/* is made to lock an already locked record. */
PRAGMA EXCEPTION_INIT (could_not_lock, -54);
BEGIN
SELECT 'X'
INTO dummy
FROM dept /* this is the table you are trying to access */
WHERE rowid = :dept.rowid
FOR UPDATE OF deptno NOWAIT;

MESSAGE('Locked Current Row ');

EXCEPTION
WHEN could_not_obtain_lock THEN
MESSAGE('Record locked by another user. Try Again Later');
RAISE FORM_TRIGGER_FAILURE;
END;

Solution Explanation:
=====================

FRM-40501 cannot be captured using the ON-ERROR or ON-MESSAGE triggers because
these errors occurs after the the locking process.

Additional Information:
=======================

Oracle Documentation:
---------------------
Oracle Forms 4.X Reference Manual, Volume 2
Chapter 2, Triggers
On-Lock Trigger

SQL*Forms 3.0 Designer's Reference
Chapter 14, Triggers
On-Lock Trigger

Forms Developer's Companion
Chapter 7, Locking
Controlling Locking with PL/SQL

.

Bookmarks Admin Profile Feedback Sign Out Help
Copyright © 2006, Oracle. All rights reserved. Legal Notices and Terms of Use | Privacy Statement

CDLT.

**star** · 20/01/2007, 07h16

Merci pour la note, mais cependant, ce n'est pas tout à fait le cas que je rencontre !
Càd qu'il n'y a personne d'autre que moi-même qui verrouile et modifie la ligne dans la base.
Donc, il s'agit d'un autre cas dans lequel la modification après l'insert ne se fait pas correctement.
A croire que :
SELECT * FROM table WHERE col1=:col1 AND col2=:col2 AND col3=:col3 AND
ROWID='xxxxxxxx.xxxx.xxxx' FOR UPDATE NOWAIT;
après un insert suivi d'un commit et d'une modification de la ligne dans le bloc ne permet pas le verrouillage de la ligne ????
La même transaction, avec la même form compilée, attaquant une base 7.3.4 ne pose aucun problème !
Vos lanternes me seraient d'un grand secours
.

**star** · 25/01/2007, 19h04

Question complémentaire :
Pourriez-vous m'expliquer comment, après une insertion d'une ligne dans un bloc et une consolidation dans la BD, Forms procède pour récupérer le ROWID de la ligne tout juste insérée qui lui sert en suite pour verrouiller la ligne lorsqu'une modification d'un item est effectué juste après ?
Merci d'avance pour vos réponses
.

**taska** · 25/01/2007, 21h03

4YI

Subject: HOW TO GET ROWID DYNAMICALLY IN FORMS
Doc ID: Note:1042539.6 Type: PROBLEM
Last Revision Date: 20-OCT-2005 Status: PUBLISHED

To capture the rowid of the record in the current block,
concatenate '.ROWID' column to :SYSTEM.CURRENT_BLOCK,
a system variable which stores the name of the block
that the cursor is in.

Apply the NAME_IN built-in to return the value of the
concatenated variable:

NAME_IN(:SYSTEM.CURRENT_BLOCK || '.ROWID');

Create the following sample code:

DECLARE
v_rowid VARCHAR2(30);
/* v_rowid ROWID; */
BEGIN
v_rowid := NAME_IN(:SYSTEM.CURRENT_BLOCK || '.ROWID');
END;

CDLT.

**star** · 26/01/2007, 18h46

Merci Taska pour la réponse, mais elle ne répond pas vraiment à la question.
Ta réponse me permet de connaitre le ROWID de la ligne mais pas de savoir comment Forms procède pour l'obtenir ni à quel moment il le valorise.
Peut être une explication à ce niveau m'aiderait à comprendre le problème que je rencontre.
.

**bpprive** · 06/09/2007, 03h03

Est-ce que tu as un trigger qui alimente une séquence ou un trigger qui alimente un champ basé de ton form, dans la bd?

La raison est bien simple, c'est qu'après un commit, Forms considère que tous les verrous sont relachés et avec raison. Pour refaire un verrou (lorsque tu saisis le premier caractère dans un champ après avoir sauvegardé) la première chose que forms va faire est d'exécuter le trigger on-lock et essayer de verrouiller la ligne que tu modifies. Pour ce faire, forms procède un peu de la sorte :
select 1
from ta_table
where champ_base1 = :1
and champ_base2 = :2
...
and champ_basen = :n
and champ_pk =

for update nowait;

Donc, si je résume en francais, trouve et verrouille l'occurence correspondante à la clé primaire que je te fournis et par le fait même, vérifie si cette occurence est encore EXACTEMENT pareille à quand j'ai fais le fetch (ou le commit, dans le cas d'un insertion). Si tu as inséré une ligne à 10 champs, dont 1 null (mais qui est alimenté par un trigger de table), tu te ramasses avec une condition telle que champ_base6 = null, ce qui est toujours faux.

**bpprive** · 06/09/2007, 03h04

Désolé, je n'avais pas vu la date à laquelle ce message a été posté! J'ai peut-être répondu pour rien!

**Garuda** · 06/09/2007, 09h31

Envoyé par star

Bjr,
J'ai un comportement bizard avec Forms 4.5 et une BD 10gR2 que je n'ai pas avec une BD 7.3.4.
D'ailleurs, je ne l'ai pas non plus avec Forms 10gR2 et une BD 10gR2 !
Lorsque j'insère une ligne dans un bloc et que je committe, puis que je la modifie juste après, j'obtiens ce message d'erreur ?
S'auriez-vous pourquoi cette erreur se produit et comment la contourner ?
D'avance merci de votre aide
.

Forms 4.5 et Oracle 10G ???
Oracle 10G n'est pas supportée par Forms 6i ! Alors Forms 4.5 !!!
A ta place, je laisserais tomber (ou passerais à une version plus récente !)