Dead Locks?

A deadlock occurs when two or more sessions are waiting for data locked by each other, resulting in all the sessions being blocked. Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock. Typically, deadlocks are caused by poorly implemented locking in application code.

the steps necessary to identify and rectify code causing deadlocks are:

• Locate the error messages in the alert log.
• Locate the relevant trace file(s).
• Identify the SQL statements in both the current session and the waiting session(s).
• Use these SQL statements to identify the particular piece of code that is having problems.
• Alter the application code to prevent deadlocks by always locking rows in the tables in the same order.

Types of Locks:-

DML locks (data locks):-DML locks protect data. For example, table locks lock entire tables, row locks lock selected rows.

DDL locks (dictionary locks):-DDL locks protect the structure of schema objects—for example, the definitions of tables and views.

Internal locks and latches:-

Internal locks and latches protect internal database structures such as datafiles. Internal locks and latches are entirely automatic.