Just to remind me of one the possible causes of these, and what the resolution is. Running a simple load test at work we started to get deadlocks. First thing to do is to look at the trace files that Oracle writes when these occur. The file names end in .trc so if you don't want to look at the Oracle init parameters you can just search for the files. Each file shows what the various participants in the deadlock are holding and waiting for. A classic cause of a deadlock is to be updating rows in both a parent and a child table that are related by a foreign key relationship with no index on the foreign key in the child table. The update to the parent row will cause Oracle to lock the row and the related row in the index on the parent's key. The attempt to update the child will wait until the row-lock on the index of the parent table is cleared. So if one process updates the parent record first and then tries to update the child, while another tries to update the child you'll have a deadlock, even if the process updating the child does not try to update the parent. The parent updater will lock the index row, and then try to update the child row, but the child updater will have locked the child row and will be waiting for the lock on the index to release.

Adding an index on the foreign key in the child table prevents the problem. Now the update to the child does not need to wait for the lock on the parent index row to be released. The parent updater can update the parent row, and acquire a lock on the index row, it can then wait for the child updater to finish its transaction, which the child updater is able to do because it does not need to wait for the parent index row to unlock. This information comes from the Concurrency Control, Indexes, and Foreign Keys section of the Oracle91 Database Concepts manual.