Could not reserve record (2). Keep trying ?....

I dont think you can make any change to a record that is locked by another user/process. So there are no changes to lose at all because the message comes up as soon as you press a key indicating you are about to update before you update.

Oracle forces the user to re-query so that you only change what you know rather than what you dont know. You should know the current values in the database before changing them which i think makes sense. So I think you shoulnt think of this as an error but very necessary oracle behaviour

Sorry , but let me understand something. what do you want to achieve. is it that you want 2 people to update the same record at the same time and still manage to commit their changes?

If this is the case , i dont think it is possible because Oracle transaction model is 'ISOLATED' meaning each transaction happens before or after another but not at the same time if you are talking about the same resource. This implies that a record can only be viewed in its state before or after a transaction, so 1 of the 2 users will see the record before the transaction and is able to update it and the other will see the record after the transaction and wiull be able to update it. But the only way to see the resource after the update is only by re-quering hence the message to re-query. so I dont think u can go past this one.

Let me know if you crack it. I might be wrong

Denis,

You are only half-way there. Forms does TWO things in the on-lock trigger, it does the "select for update nowait" as you have coded, but it ALSO compares the values fetched into the record during the query with the current values in the database, by adding this to the where clause:

where rowid=:blk.rowid and :blk.Col1=Col1 and :blk.Col2=Col2, and...
(Not sure how they do it, but they have to handle null columns in the comparison, too.

Now, due to the where clause, if any column was changed and committed by the other user, the select will return a no_data_found.

If the ORA-00054 is detected, you get the "record is locked by another user... retry" message. But if select returns a no_data_found, you get the "Record has been updated by another user... Please re-query" message. (And it should be noted that at this point the record is now locked by the user getting the message, AND it cannot be updated by the user. Our forms here do a clear/rollback before the query so that the re-query process will unlock the row, but that is not the default in Forms.)

The two messages come up so rarely in the real-world situations here, that I do not try to override the default processing. However, in special cases, I have written such code, but it is usually in other places, like during special commit processing where other tables and data need to be updated in addition to the base-table data. In those situations, it is quite a task to get everything working correctly.

If you look at the on-line help on the "Lock the Row" topic, in the "continued" image, there is the process, "Has the row been changed or deleted by another operator" check. That is the clue to what you need to do in the second step.

Message was edited by:
Steve Cosner

One additional note: I ran a trace to see the SQL statement that Forms actually issues, and it is not what I gave above. Instead, it is just a select of all the columns in the block, with just the rowid in the where clause, and with the for update clause. Forms apparently fetches all the column data, then compares the newly fetched values with the database_value of each column in the record.

Hi Denis,

It has been a while since I developed Forms but here is my 2 cents worth....

The ON-triggers are instead of normal operation triggers, therefore, if you use ON-LOCK, you are taking over the control and you have to issue your own LOCK_RECORD in the code.

But in this instance, you should be using a ON-MESSAGE (or ON-ERROR trigger). You only want to replace the standard message with your own message.

Regards,

John

Hello Dennis,
i also found this solution on metalink ( 1004126.6 ) and it worked for me.
I 've put all the items from that datablock on a separate canvas and show 'my' message before navigation.

Bernd

Hi ! is there any way to show the oracle USERNAME that is locking the table, in the message...something like "The user USERNAME is locking the table ,please try again later" ...)..
Thanks in Advance!!
Robert

Not possible. Oracle does not record a usernames along with record locks.

If you are desperate, you could implement your own mechanism of doing this but it will need a hell of a code. You know every time someone locks a row he saves the info on the server in order for the others to be able to see it. Hardcore

10,000 sessions (around 8,000 users), 5 TS Database... yes, I'm desperate ...any clues ?
Thanks in advance

I haven't ever done it of course, but I thought that if you need it you could have a table for example that holds locking information about users.. So whenever a user locks a record, hge places an entry in there and accordingly deletes it when he releases it. This would normally require some code on the on-lock trigger of your forms but I'm sure its not as easy as it might look..

That's just a though I made but unfortunatelly I can't back it up technically for you. However I'm open for discussion on this to the extend my knowledge allows me.


regards,
teo

10,000 sessions (around 8,000 users), 5 TS Database... yes, I'm desperate ...any clues

And how many forms do you have running? I am afraid you would need to add code to every form.

Why don't you step back and look at the problem from a different angle... On what table is the problem locking occurring? What forms can lock that table? If it is not too many, maybe someone should look at those forms and try to determine why locking is a problem.

In a well-written OLTP/Forms environment, Forms lock records and release the locks automatically, and there is hardly ever a problem.

However, if forms are written incorrectly, two users just trying to display the same data can lock each other up. Fixing the bugs in the problem forms would be a far more efficient method of correcting your situation.