ORA 00060 DEADLOCK DETECTED WHILE WAITING FOR RESOURCE UPDATE
You issue a delete, or update on the key, on the parent table. Issue with unindexed foreign keys leads to S locks on child table when
Referential integrity also acquires TM locks.
To understand the situation I recommend to read this article by Franck Pachot.īelow is citation from this article, which directly relevant to your situation(note that SSX and SRX abbreviations are equivalent): So, Sessions #24 and Session #72 blocks each other: deadlock happens.īoth lock types (SX and SSX) are table-level locks. This (second row) demonstrates exactly same situation, but in opposite direction: Session #24 waits for SSX lock become available, but blocked by Session #72 which already holds SX lock on same table. Resource Name process session holds waits process session holds waits This session blocked by Session #24 which already holds table-level lock of a same type (SX) and waits while SSX lock would be available. Session #72 holds table-level lock (TM) with "Row Exclusive" type (SX) and want to acquire "Share Row Exclusive" (SSX) lock on same table. for update which locks data like update since Oracle 9i, but there are no for update clause in the query from question.
Kindly post, in case any more information is need.įirst of all, select statement never lock anything in Oracle, just uses last available consistent version of data. Would adding an index on this column help? The sql hint says "all_rows", so does it mean that this table gets table level lock when deleting records from employee table? i dont have an index on the foreign key column currently. The employee_salary table has foreignkey constraint on EMPSAL_EMP_ID column. select /*+ all_rows */ count(1) from "USERS"."EMPLOYEE_SALARY" where EMPSAL_EMP_ID=:1 Below is the query i see in that section. I also read in some blogs that "sqltxt" section from the trace file can suggest the cause. Also the rows waited on section says no rows. I would appreciate if some one can tell me what the "Deadlock graph::" is saying. Current SQL Statement for this session (sql_id=dyfg1wd8xa9qt). End of information for the OTHER waiting sessions. Pid: 208 O/S info: user: zgrid, term: UNKNOWN, ospid: 2439 Information for the OTHER waiting sessions. Information may aid in determining the deadlock: To user error in the design of an application The following deadlock is not an ORACLE error. Below is bits of data from the trace file, which i hope would help in locating the cause. I have got the trace file from the oracle Admin, but need help in reading it. I have been getting this "ora-00060 deadlock detected while waiting for resource" error often now in my application when multiple users are using the application.
0 kommentar(er)
