Oracle multiple processes updating same table
It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. (All the information I requested is available in the trace file.) As Alessandro mentioned, it's possible for sessions locking different rows in the same table to deadlock due to unindexed foreign keys on the child table of a parent/child relationship.The following information may aid in determining the deadlock: Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-0007003e-0081d6c3 45 790 X 104 20 X TX-00080043-0085e6be 104 20 X 45 790 X session 790: DID 0001-002D-000035F9 session 20: DID 0001-0068-000007F6 session 20: DID 0001-0068-000007F6 session 790: DID 0001-002D-000035F9 Rows waited on: Session 790: obj - rowid = 0000F0C8 - AAAPDIAAMAAAEf IAAA (dictionary objn - 61640, file - 12, block - 18376, slot - 0) Session 20: obj - rowid = 0000F0C8 - AAAPDIAAMAAAEf GAAA (dictionary objn - 61640, file - 12, block - 18374, slot - 0) ----- Information for the OTHER waiting sessions ----- Session 20: sid: 20 ser: 4225 audsid: 57496371 user: 72/RPT_TABLE flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/- flags2: (0x40009) -/-/INC pid: 104 O/S info: user: oracle, term: UNKNOWN, ospid: 20798 image: [email protected] client details: O/S info: user: gtsgen, term: unknown, ospid: 1234 machine: caiapp08p-node0program: JDBC Thin Client application name: JDBC Thin Client, hash value=2546894660 current SQL: delete from RPT_TABLE. Also, It's possible that you could have deadlocks on two sessions updating different rows of the same table, even if the table is not part of a parent/child relationship, if, for example, the table has a shortage of ITL entries.In Oracle 10 versions, can update and delete on the same table cause deadlocks even if they are operating on different rows of same table concurrently? R_ID=: B1 ) ----- PL/SQL Stack ----- ----- PL/SQL Call Stack ----- object line object handle number name 45887d750 24 procedure RPT_TABLE.The table has primary key made-up of two columns, and do not have any FK associated/refereed with any other table. T1_UPDATE_Stored Proc 6399ba188 1 anonymous block If you could update your question with the deadlock graph, that would be useful information.However, parallelism can be used against a single dmp file by breaking the dmp file into equal-sized chunks and loading each into the target table(s) simultaneously.However, a Data Pump import is dependent upon other objects, such that they must be done serially, and cannot be parallelized.
In addition to the data, sometimes metadata is can loaded in parallel when the "parallel" clause is set to a degree greater than one.And there is no parent/child relation with other table What I believe is, it will not create a deadlock, but I'm facing a issue in my application. (When your application encounters a deadlock, Oracle will raise an ORA-00060, and a tracefile will be written to the user_dump_dest.) If you look in the trace file, you'll find a section called the "Deadlock Graph".adding the oracle trace : The following deadlock is not an ORACLE error. If you can post that, and also post the statement that caused the deadlock and other statements involved in the deadlock, then we can begin to draw some conclusions.TEMP_TABLE_T1 where TEMP_T1_ID=:1 ----- End of information for the OTHER waiting sessions ----- Information for THIS session: ----- Current SQL Statement for this session (sql_id=bsaxpc2bdps9q) ----- UPDATE RPT_TABLE. Again, post the information requested above, and I'm confident we can determine the root cause of your deadlock.Added on 7/30/2012 ** Adding the following, now that the deadlock trace file has been supplied: Ok, first off, based on the trace file contents, this is a simple deadlock due to sessions overlapping/colliding on the rows they are trying to lock.