Forum Stats

  • 3,780,546 Users
  • 2,254,409 Discussions
  • 7,879,377 Comments

Discussions

Where and how does Oracle Forms store its Information about locked records?

2808533
2808533 Member Posts: 5
edited Dec 12, 2014 11:09AM in Forms

Where and how does Oracle Forms store its information about locked records?

Is there a specific lock table which holds user or time information for the locked records?

I´m using Oracle (Forms) 11g

Thanks in advance!

Tagged:

Answers

  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,134 Gold Trophy

    Oracle Forms does not hold information on the locked rows.

    Oracle database holds information on the locked rows.

    In short, every row in the database has information on whether it is locked.

    Regards,

    Zlatko

  • 2808533
    2808533 Member Posts: 5

    Thanks, but this seems very unspecific for me.

    Where exactly is the locking information held?

  • fxmatz
    fxmatz Member Posts: 230

    Hi,

    this works:

    SELECT

      a.object_name,

      a.owner object_owner,

      Decode(b.locked_mode, 0,

         'None', 1,

         'Null (NULL)', 2,

         'Row-S (SS)', 3,

         'Row-X (SX)', 4,

         'Share (S)', 5,

         'S/Row-X (SSX)', 6,

         'Exclusive (X)',        

         b.locked_mode) locked_mode,

         b.session_id sid,

         b.oracle_username,

         b.os_user_name,

         process

    FROM all_objects a, v$locked_object b

    WHERE a.object_id = b.object_id

    ORDER BY 1

    Hope it helps.

  • 2808533
    2808533 Member Posts: 5
    edited Dec 11, 2014 9:06AM

    Okay, but still my problem is that I dont know where Oracle Forms takes the information about the existing locks.

    For example in this Screenshot

    Unbenannt.PNG

    OracleForms informs the user about an existing lock and provides additional information e.g. the number of attempts to edit a specific record. Hence to me it looks like there is some additional Oracle Forms logic around that, so where does it take the information from?

  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,134 Gold Trophy

    If you change the Database Item on a block that has a Locking Mode = Automatic (default) or Immediate, Forms automatically sends to the database SQL command

    SELECT ...FROM table_name WHERE rowid =: ROWID FOR UPDATE, and the database then tries to lock this row.

    If a row is already locked by another session, you get a message that you displayed.

    Regards,

    Zlatko

  • 2808533
    2808533 Member Posts: 5
    edited Dec 12, 2014 3:06AM

    In my opinion or from my experience working with other tools / technologies, the ... FOR UPDATE clause is only used to lock atomic parts of work. E.g. like the synchronized keyword in Java.

    An appropriate example would be locking a record as long as a query needs to be finished, not until a user is finished editing a record within the application (thats how the example Screenshot or the default workflow behaves in Oracle Forms).

    From what you are telling me, it seems like Oracle Forms locks a record for a huge period of time with the use of the FOR UPDATE clause? For example as from the record is being started editing until its being persited. Is this really the case?

    Moreover, ">>If a row is already locked by another session, you get a message that you displayed." is what my question is mostly all about. While implementing locking manually and if a row is already locked by another session, the requesting session just waits. I don't know how to display a message dialog by myself because I dont know where to get information about the locked records from. Is this information really held in the Database (and where?) for requesting it to inform a user about an already locked record?

  • 2808533
    2808533 Member Posts: 5
    edited Dec 12, 2014 5:20AM

    While trying to access the v$locked_object table a "table or view not found" error is displayed.

  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,134 Gold Trophy
    edited Dec 12, 2014 11:09AM

    > From what you are telling me, it seems like Oracle Forms locks a record for a huge period of time with the use of the FOR UPDATE clause?

    Explicit locking with SELECT ... FOR UPDATE, and implicit locking with DML commands (INSERT, UPDATE, DELETE), locks the row(s) until the end of the transaction (COMMIT or ROLLBACK).

    Or ROLLBACK TO SAVEPOINT (if we have a SAVEPOINT), but then the row remains locked to anyone who has already tried to lock. See:

    This is not exactly the same as locking in Java with synchronized (or lock () from Java 5).

    Regards,

    Zlatko

    2808533
  • Andreas Weiden
    Andreas Weiden Member Posts: 10,870 Gold Crown
    >In my opinion or from my experience working with other tools / technologies, the ... FOR UPDATE clause is only used to lock atomic parts of work. E.g. like >the synchronized keyword in Java.
    

    A lock in the database is different from a lock in a programming language.

    >An appropriate example would be locking a record as long as a query needs to be finished
    

    There is no need for locking anythimng during a query in Oracle (and it is in fact not happening).

    >not until a user is finished editing a record within the application
    

    Well, the reason behind locking a record in the database is that the current user wants to change something in the record and therefore no other user should change the reason in between. So, it of course makes sense to keep the lock until the user either commits or discards his changes (thats called pessimistic locking)

    >From what you are telling me, it seems like Oracle Forms locks a record for a huge period of time with the use of the FOR UPDATE clause? For example as from the record is >being started editing until its being persited. Is this really the case?
    

    Yes, but there is a property called "locking mode" at form-level which can be changed to enable "optimistic locking". If you want to go with that, use that property.

This discussion has been closed.