IndahnyaBerbagi

Locking Within SQL*Forms 3.0


SQL*Forms performs implicit locks on database blocks so that the users need
not code their own routines for data consistency. SQL*Forms also provides
the ON-LOCK trigger and LOCK_RECORD packaged procedure to allow further
control of the locks performed within the application.

With RDBMS V6.0 and higher, row level locking is now performed. Whole
table locks are no longer necessary to guarantee data integrity. It is
possible to lock an entire table (using the LOCK TABLE SQL command) but
this is usually undesirable since other users will not be able to update
any records within the table until the lock is released, either through
a COMMIT or a ROLLBACK.

SQL*Forms Use of Locking
~~~~~~~~~~~~~~~~~~~~~~~~

By default, data queried into a database block is not locked, unless an
ENTER_QUERY(FOR_UPDATE) or EXECUTE_QUERY(FOR_UPDATE) is used. The data
displayed is a snapshot of the data in the database at the time of the
query. The actual data can be changed "underneath" the user. The values
of the records obviously do not change as they are being viewed. Only
a subsequent requery will show any data that has been changed by other
users.

Let's say we have two users on the system, A and B. Both query the same
record on the screen. If user A types a character into any of database
fields, a

SELECT { all database fields in the block }
FROM { table which the block is based on }
WHERE rowid = { current row }
FOR UPDATE OF { all database fields in the block }

is performed. The FOR UPDATE option attempts to lock the record selected
so that no other users may change the information at the same time. If
the record is already locked, then the following error occurs:

Attempting to reserve record for update or delete (^C to cancel)...

or on some Unix platforms:

Could not reserve record (3 tries). Continue? ( Yes ) ( No )

Another "locking" issue that can occur is if user A and B query the same
record and user A modifies it and commits the change. User B, has a snapshot
of the "old" data on the screen. Obviously, B should not be allowed to
change the data since B has an "outdated" representation of the data. If
user B attempts to update the record, the following error message results:

FRM-40654: Record changed by another user. Re-query to see change.

No modification of the record can be done at this point until the record is
requeried to show the new changed data.

To review: when a user updates a record, SQL*Forms issues a SELECT ...
FOR UPDATE to determine if the record is currently locked. If it is not,
then SQL*Forms compares the values on the form to the values currently
in the database. If they are equal, then the record has not been changed
"underneath" the user. If they are not equal, then another user has committed
a change.



Locking Facilities Within SQL*Forms 3.0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The ON-LOCK trigger fires whenever SQL*Forms would normally attempt to
lock a row. The default functionality for this trigger is LOCK_RECORD;
Users may modify the ON-LOCK trigger to :

* bypass lock attempts on single user systems.

* lock the entire table by issuing the LOCK TABLE command.

* lock other rows in different tables as necessary.

The LOCK_RECORD packaged procedure "attempts to lock the row in the
database that corresponds to the current record." Other situations which
will cause a lock to be performed:

* Modification of database field on queried data

* ENTER_QUERY(FOR_UPDATE), EXECUTE_QUERY(FOR_UPDATE)

* DELETE_RECORD

Note that in block mode, the edit is allowed but SQL*Forms will place
an exclusive lock on the row during record validation. The ENTER_QUERY
(FOR_UPDATE) and EXECUTE_QUERY(FOR_UPDATE) lock only the records that have
actually been fetched (SQL*Forms queries in batches of records when they
are needed as specified by the ARRAY SIZE). The rest of the records can be
locked by either scrolling through the records or performing a LAST_RECORD
command. Note that an ENTER_QUERY(ALL_RECORDS,FOR_UPDATE) will fetch and
lock all the records satisfying the query conditions immediately.

Locks are released when:

* A commit performs successfully

* A full rollback (rollback to savepoint does NOT)

* The form aborts.

Note that unlike version 5, a CLEAR_RECORD or CLEAR_BLOCK does not
release any of the locks that may have been issued.


Using PL/SQL for greater control:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

A common request of users is to be able to tell whether a row has been
locked or not, since the "Record Locked" message cannot be trapped via
ON-ERROR/MESSAGE triggers. The below PL/SQL block (written by Steve
Muench) takes advantage of the PL/SQL PRAGMA EXCEPTION which allows the user
to define their own exception based on the ORACLE error code. The -54
below results when an attempt is made to lock an already locked record.


DECLARE
dummy CHAR(1);
could_not_obtain_lock EXCEPTION;
PRAGMA EXCEPTION_INIT (could_not_obtain_lock,-54);
BEGIN
SELECT 'X' INTO dummy
FROM emp
WHERE rowid= :emp.rowid
FOR UPDATE OF empno NOWAIT;
MESSAGE('Locked the Current Row Successfully');
EXCEPTION
WHEN could_not_obtain_lock THEN
MESSAGE('Record is Already Locked. Please Try Later ...');
RAISE form_trigger_failure;
END;

The PL/SQL User's Guide and Reference contains information on user defined
exceptions using EXCEPTION_INIT.

Possibly Unexpected Results With Locking
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If the same record is queried and modified from two blocks on the same form
(therefore the same user) only the change on the block with the highest
sequence number in the block definition will be committed to the database.

a) a block level PRE-INSERT/UPDATE/DELETE modifying a database field in
a previous block. What happens is that during a commit, SQL*Forms
processes information block by block in the same order as in the
block definition screen. One or many SQL statements are produced for
each database block that has at least one database field changed.
When it finishes the SQL statement for the current block it moves
on to the next block. The PRE- triggers also fire on a block by block
basis and thus it is possible to modify a field using a PRE- trigger
on a previously processed block. In other words, the SQL statement
produced may be inaccurate. For example, a form has two database
blocks ONE and TWO that has an update ( :one.field='X') and an insert
pending respectively. When a COMMIT is performed, the PRE-UPDATE is
fired for block ONE and the SQL Statement is produced.

UPDATE one SET column=:one.field WHERE rowid= { current rowid }
^
= 'X' (the actual value)

Then SQL*Forms moves on to the next block and fires its PRE-INSERT.
If the PRE-INSERT contains code like:

:one.field := 'Y';

Then the error FRM-40654 will appear if the user attempts to update
block ONE after the COMMIT. The reason is that by the time PRE-INSERT
fires, block ONE's SQL Statement and block status have already been
set. What actually gets committed is the 'X' value even though 'Y'
shows up on the screen. If the user subsequently attempts to modify
block ONE, a

SELECT { fields in block ONE }
FROM one
WHERE rowid= { the rowid }
FOR UPDATE OF {fields in block ONE }

will be successful (since the record is no longer locked after the
commit). However, SQL*Forms will give the error after it determines
that the representation within the database ('X') does not match with
the current field value. It is not recommended to change fields on
different blocks in PRE-INSERT/DELETE/UPDATE triggers.

b) Another situation can arise with number precision. This can
probably best be described by an example. If table "x" contains a
column of type NUMBER (5,2). Then, create a form accessing that
field and type in a number with greater precision, (e.g. 10.243).
Commit the information and then attempt to edit the field. FRM-40654
results since the value that gets stored to the database (10.24)
is not equal to the value on the form (10.243).

c) A more complicated situation is documented in PCR # 93159.
Two users A and B query up the same record. User A modifies and
commits record. User B attempts to modify but gets correctly the
FRM-40654 error. However, if user A attempts to modify his/her
record, the error "Attempting to Reserve Record for Update/Delete"
occurs. The reason is that when User B attempts to modify the
record, a SELECT ... FOR UPDATE is issued first. B locks the
record successfully since A has already committed the change.
However, SQL*Forms then compares the field values and finds that
the representation within the database (modified by A) is different
from B's representation, thus the "Record has been changed" error.
But, the lock is NOT released by B. Thus if A attempts to remodify
the record he/she had just modified, they will be locked out!

d) A strange situation can also occur by clearing a lock in a called form
on an unsuspecting record in the calling form. Given that the calling
form is named FORM_A and the called form is named FORM_B, user A may
make a change in one of the database fields and CALL FORM_B before
validating the field (and therefore letting forms recognize the
change). The lock created by the first keystroke in that field is
still preserved. Note that a commit in FORM_B will now clear that
lock. User A now has misrepresented data on the screen. If a user
B modifies the same record, whoever commits last will have their
change committed. The workaround is to let SQL*Forms be aware that
a change has been made since SQL*Forms does not force validation on
CALL's and SET_INPUT_FOCUS(MENU)'s. Before the CALL, an ENTER; or
some navigation packaged procedure can be invoked to force validation.
The user will then receive the correct message in FORM_B on commit:

FRM-40403: A calling form has un-posted changes. Commit not allowed.