Home » Developer & Programmer » Forms » frm-40509: unable to update record (forms runtime 6i)
frm-40509: unable to update record [message #417772] Tue, 11 August 2009 08:38 Go to next message
emadnabil
Messages: 163
Registered: August 2007
Senior Member
Hii all

I have a strange problem

i got this error
frm-40509: unable to update record

this error happen when 2 user make update on the same table but
on different row

i used display_error in the form
to show the error and i got this error
sql statment in error:
UPDATE VEHICLES SET NAME=:1,VEHICLE_CODE=:2,ACTIVE=:3,CLASSIFICATION=:4,TRANSMISSION_TYPE=:5,LPN=:6,VIN=:7,
ENGIN_NUMBER=:8,DESCRIPTION=:9,LICENSE_PER_YEAR=:10,LICENSE_EXPIRE_DATE=:11,PERMIT_DATE_S=:12,
PERMIT_DATE_E=:13,LICENSE_PLACE=:14,LICENSE_TYPE=:15,LICENSE_CATEGORY=:16,INSURANCE_DOCUMENT=:17,
INSURANCE_PER_YEAR=:18,INSURANCE_CATEGORY=:19,FUEL_CODE=:20,FUEL_CARD_ID=:21,METER_CODE=:22,
CURRECT_ODOMETER=:23,PURCHASE_DATE=:24,PURCHASE_VALUE=:25,CURRENT_VALUE=:26,MONTH_TO_DEPRECIATE=:27,
SEGMENT_CODE=:28,ATTACHED_VEHICLE_CODE=:29,CREATION_DATE=:30,CREATOR_USER=:31,MODIFICATION_DATE=:32,
MODIFIED_USER=:33,OWNER_COMPANY=:34 WHERE ROWID=:35

error:
ORA-00060: deadlock detected while waiting for resource

N.B we make update on 2 different row in the VEHICLES table

Thanks
[EDITED by DJM: cut overly long update statement]

[Updated on: Tue, 11 August 2009 19:44] by Moderator

Report message to a moderator

Re: frm-40509: unable to update record [message #417773 is a reply to message #417772] Tue, 11 August 2009 08:50 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Two different sessions updating two different rows can't give a deadlock error, unless they are also trying to modify something else in the same transaction.
So is there any code in your form that will modify the DB apart from this update?
Alternatively are there any database triggers on the VEHICLES table?

And next time you post code can you please format it so it doesn't scroll off the side of the screen.
Re: frm-40509: unable to update record [message #417774 is a reply to message #417772] Tue, 11 August 2009 09:06 Go to previous messageGo to next message
emadnabil
Messages: 163
Registered: August 2007
Senior Member
Thanks for reply
what do you mean by this

So is there any code in your form that will modify the DB apart from this update?


and for this
are there any database triggers on the VEHICLES table?

yes there is a database trigger on the vehicles table
and it work after update and also it work on another table
and here is the trigger
CREATE OR REPLACE TRIGGER "ADD_VAN_TO_SFIS_STEP1" AFTER
INSERT ON "VEHICLES" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW 



BEGIN

DECLARE

    v_exist Number;
    
BEGIN

     select count(*) 
     INTO V_EXIST
     from SFIS_VEHICLES
     where  SFIS_VEHICLES.Vehicle_Code = :NEW.Vehicle_Code ;
     
     if v_exist = 0 then 
       insert into sfis_vehicles
         (vehicle_code,segment_code,classification,vin,lpn, engin_number )
       values
         (:NEW.vehicle_code,:NEW.segment_code,:NEW.classification,:NEW.vin,:NEW.lpn, :NEW.engin_number ) ;
        
     end if ;            
   
    
END;

END;
Re: frm-40509: unable to update record [message #417776 is a reply to message #417772] Tue, 11 August 2009 09:18 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
I mean - is there more than one database datablock in the form, or is there any direct insert/update/delete statements coded in the form (in triggers or procedures).

As for your DB trigger, according to its specification it doesn't fire after update so it probably isn't relevant here.

The basic logic that causes deadlocks is this:

1) User 1 locks row 1.
2) User 2 locks row 2 (can be the same or different table to row 1).
3) User 1 tries to lock row 2 - gets blocked.
4) User 2 tries to lock row 1 - deadlock.

Because of the way oracle detects deadlocks the deadlock error can be reported in either session.

What you should find is that oracle will have generated a trace file for the deadlock. Have a look in the alert log for references to deadlock and it'll tell you which trace file the details are in.
That trace file should tell you what row 1 and row 2 are.
Re: frm-40509: unable to update record [message #417789 is a reply to message #417772] Tue, 11 August 2009 09:42 Go to previous messageGo to next message
emadnabil
Messages: 163
Registered: August 2007
Senior Member
Thanks for reply
for this
Quote:
is there more than one database datablock in the form, or is there any direct insert/update/delete statements coded in the form (in triggers or procedures).


yes there is more database block in the form

but my question still exist
if two user works on 2 different row on the same table
how a deadlock happens?

[EDITED by DJM: change incorrectly used 'code' tags to 'quote' tags]

[Updated on: Tue, 11 August 2009 19:43] by Moderator

Report message to a moderator

Re: frm-40509: unable to update record [message #417790 is a reply to message #417776] Tue, 11 August 2009 09:46 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
I just explained that.

Have a look for the trace file.
Previous Topic: built-in package (dbms_xmlgen) (merged 5)
Next Topic: Problem in writing file to %userprofile%
Goto Forum:
  


Current Time: Sun Dec 04 16:53:16 CST 2016

Total time taken to generate the page: 0.04101 seconds