How to Lock and Unlock Records in Oracle Forms

In Oracle Forms applications, multiple users often access and modify the same data at the same time. To avoid data conflicts and ensure consistency, Oracle Forms provides mechanisms to lock and unlock records. This functionality prevents two users from editing the same record simultaneously and helps maintain database integrity.

In this tutorial, we will explore how locking works in Oracle Forms, how to programmatically lock and unlock records, the difference between optimistic and pessimistic locking, and best practices for managing concurrency.

Why Locking Records is Important

When users share access to the same table or block, conflicts may occur:

• Two users trying to edit the same employee record.
• A user editing data while another deletes it.
• Updates being lost because the second user overwrote the first user’s changes.

By locking records:

• Only one user can edit a record at a time.
• Data consistency is maintained.
• Errors and overwrites are minimized.

How Oracle Forms Handles Locks by Default

Oracle Forms uses pessimistic locking by default. This means:

• When a user starts editing a record (navigates into a field and makes a change), Forms automatically issues a SELECT ... FOR UPDATE statement behind the scenes.
• This locks the row in the database until the transaction is committed or rolled back.
• Other users can view the record but cannot update it until the lock is released.

Thus, in most cases, developers do not need to explicitly lock records. However, Oracle Forms also provides built-ins for greater control.

Locking a Record with LOCK_RECORD

The LOCK_RECORD built-in explicitly locks the current record in the block.

Example:

BEGIN
   LOCK_RECORD;
EXCEPTION
   WHEN FORM_TRIGGER_FAILURE THEN
      MESSAGE('Unable to lock record. It may already be locked by another user.');
      MESSAGE(' ');
END;

• If the record is available, it gets locked for the current user session.
• If another session already holds the lock, an error occurs.
• Always handle exceptions, since lock failures are common in multi-user environments.

Unlocking a Record

In Oracle Forms, records remain locked until:

1. Commit – Changes are saved, and the lock is released.
2. Rollback – Changes are discarded, and the lock is released.
3. Exit Form without saving – Locks are automatically cleared.

There is no direct UNLOCK_RECORD built-in. Instead, you use COMMIT_FORM or ROLLBACK to release the lock.

Example: Unlocking by Rollback

BEGIN
   ROLLBACK;
END;

This releases all record locks held by the current session.

Checking if a Record is Locked

You can use the SYSTEM.RECORD_STATUS and error handling logic to detect record locks.

• NEW – Record has not been saved yet.
• CHANGED – Record has been modified but not committed.
• QUERY – Record fetched from the database (may be locked by another session if being edited).

If a record is locked by another session, attempting to use LOCK_RECORD will raise an error, which you can handle gracefully.

Practical Example: Preventing Duplicate Editing

Suppose you want to ensure that a user cannot edit a record unless they explicitly lock it. You can add code in the WHEN-NEW-RECORD-INSTANCE trigger:

BEGIN
   LOCK_RECORD;
EXCEPTION
   WHEN FORM_TRIGGER_FAILURE THEN
      MESSAGE('Record is already locked by another user.');
      MESSAGE(' ');
      GO_BLOCK('MAIN_MENU');
END;

This prevents users from editing a record that another user is already modifying.

Best Practices for Locking Records

• Lock only when necessary – Overuse of locks can cause contention and block other users.
• Keep transactions short – Long locks reduce concurrency and may frustrate users.
• Handle errors gracefully – Always inform users if a record is locked by someone else.
• Avoid manual locking for simple forms – Default Forms behavior already handles most scenarios.
• Use Commit or Rollback promptly – Ensure locks are released as soon as possible.

Common Errors with Locks

• FRM-40501: Oracle error: unable to reserve record for update
  • Occurs when another session has already locked the record.
• Deadlocks
  • Rare but possible if multiple sessions lock records in different orders.
  • Resolve by rolling back transactions.
• Lost updates
  • Happen if locking is disabled or not properly handled.

Conclusion

Managing concurrent access is essential in any multi-user Oracle Forms application. By default, Forms automatically applies pessimistic locking to prevent conflicts. However, developers can explicitly Lock and Unlock Records in Oracle Forms using the LOCK_RECORD built-in along with COMMIT or ROLLBACK.

Always remember to keep transactions short, handle exceptions, and provide clear feedback to users. Proper use of record locking ensures data integrity, prevents overwrites, and creates a smoother user experience in enterprise applications.