Ora10g: ORA-00060 Deadlock detected (II)

Following the previous post I must mention that there are other types of locks that are caused by conflicting design that attaches to the peculiarities of oracle.

First left trace example:

 *** ACTION NAME: () 21/04/2011 14:08:01.227
*** MODULE NAME: (MiPrograma.exe) 21/04/2011 14:08:01.227
*** SERVICE NAME: (SYS $ USERS) 04/21/2011 14:08:01.227
*** CLIENT ID: () 04/21/2011 14:08:01.227
*** SESSION ID: (1636.58026) 04/21/2011 14:08:01.227
DEADLOCK DETECTED (ORA-00060)
[Transaction Deadlock]
The Following is Not an ORACLE deadlock error.It is a deadlock due to user error in the design of an application or Issuing from incorrect ad-hoc SQL. The Following information May Aid in Determining the deadlock: Deadlock graph: --------- Blocker (s )-------- --------- Waiter (s) --- Resource Name process session ------ Holds Holds waits process session waits TM-99-00000000 0001f1b8 SX SSX 1636     92 SX SSX TM-1461-00000000 0001f1b8 SX SSX 92 1461     1636 99 SX SSX session 1636: DID 0001-0063-0003159E session 1461: DID 0001-005C-000375B1 session 1461: DID 0001-005C-000375B1 session 1636: DID 0001-0063-0003159E Rows Waited on: Session 1461: no row Session 1636: no row 

Here the first thing that strikes us is that we already have locked records as shown below.Moreover, the type of lock is different (before X or exclusively, shared or exclusive SX now?). In the documentation we can see more of the types.

Here the theme is that SX locking deadlocks occur when manipulating tables with foreign keys where the field is not indexed (in the fk, pk not in the parent table) and we intende to update / delete on the table.To put it another way oracle needs to maintain referential integrity and see the daughter for further compliance.

Solution in this case? Always create the primary key in the main table, an index and a foregin key in the child table. Nothing more ...