frm-40509: unable to update record [message #417772] |
Tue, 11 August 2009 08:38 |
emadnabil
Messages: 179 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 |
cookiemonster
Messages: 13963 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 |
emadnabil
Messages: 179 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 |
cookiemonster
Messages: 13963 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 |
emadnabil
Messages: 179 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
|
|
|
|