I have the following piece of Oracle procedure where i do a update for table in a single row.
UPDATE
INVD_MAIN
SET STATUS_ID= 3,
EXTERNAL_ID_TYPE=9,ASSIGNED_ACCT_NO=0,STATE=1,STATE_DATE=SYSDATE,
ORDER_ID=11111,ORDER_ID_RESETS=0,AGING_STARTED_DATE=TRUNC(SYSDATE)
WHERE INVENTORY_ID = 5290074
AND INVENTORY_ID_RESETS = 1
If i do not commit or roll back this update. I am not able to do another update on same table for any other record. I am using another session to do the following update
UPDATE
INVD_MAIN
SET STATUS_ID= 3,
EXTERNAL_ID_TYPE=9,ASSIGNED_ACCT_NO=0,STATE=1,STATE_DATE=SYSDATE,
ORDER_ID=11111,ORDER_ID_RESETS=0,AGING_STARTED_DATE=TRUNC(SYSDATE)
WHERE INVENTORY_ID = 5290074
AND INVENTORY_ID_RESETS = 1
I get a table locked error. The error message is as follows:
ORA-00060: deadlock detected while waiting for resource
Why it is happening what is the solution.
any advice will be appreciated.
Thanks
Manoj