This content has been marked as final. Show 22 replies
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
When I am not using the on-lock trigger, Oracle forms behave properly but I get this not user friendly "Could not reserve record..." message. Meaning that if you try to update a record which is locked, you get the "Could not reserve record..." message. After the record gets commited by the other user, you get the "Record has been changed... Please re-query". That way, no user can lose it's changes.
However, using the on-lock trigger solution to trap the error message, Oracle forms will let me change a record which has been commited by another user, without telling me to first re-query. Therefore, that other user will lose it's changes because I will commit over them. I should have got instead the "Record has been changed...please re-query" message.
Any ideas of what causes that or how to avoid that situation using the on-lock trigger ?
Hope it is clear.
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
What I want to achieve is to trap the message "Could not reserve record (2)...Keep trying'. I want to replace that message with a more meaningfull message.
So the solution I found here was to write code within the on-lock trigger (see above).
This works since I can write my own message instead of the "Could not reserve..." message. However, a side effect of it is that as soon as user A commit his changes, user B is able to change that same record, without re-querying, which cause user A to loose his changes. Without the on-lock trigger, user B gets the standard "Record has been changed...you must re-query" which is the correct behavior but I'm stuck with that "Could not reserve..." message.
I am wondering why with the on-lock trigger Oracle let a user modify a changed record (by another user) without giving the message to re-query. Maybe that someone ran into the same problem using that on-lock trigger solution and they found a work around.
Hope I made it clearer.
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:
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.
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.
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.
Thanks everyone for your tips, it gave me some clue on how to fix the problem.
Here is the re-written on-lock procedure that will work.
is_locked boolean := false;
Select null INTO dummy from mytable
where rowid = :myblock.rowid
for update nowait;
exception when Others then
If sqlcode = -00054 Then
is_locked := True;
IF is_locked then
Showmessage('Another user is updating this record. Try again later.');
If Not Form_success Then
Message was edited by:
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!!
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.
10,000 sessions (around 8,000 users), 5 TS Database... yes, I'm desperate ...any cluesAnd 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.