resource speeds have nothing to do with deadlocks. How can an application design point to a resource and say that is causing a deadlock in the database???? that is a ludicrous statement.


there are three major causes of deadlocks in Oracle

a) over-locking a resource. For example, if you have an unindexed foreign key and you update the parent table primary key or delete from the parent table - we require a full table lock on the child table during the time it takes to do the update or delete (which can take a long time if the child table is large since we obviously have to full scan it now before we can update/delete that parent key!!).

solution: application developers implement an index, an index that would be required to make the delete or update run in a reasonable amount of time in any case - forget about the lock - think about the full scan!!


Or, having a bitmap index in place. If you put a bitmap index on EMP(JOB) for example and you update an employee from having a job of CLERK to a job of ANALYST - you will update two keys in the bitmap index. However those two keys will point to hundreds/thousands or more rows in the table. If someone else tries to update someone to an ANALYST or CLERK - they may well be blocked by first transacation.

solution: never ever use bitmap indexes in situations where you update small numbers of rows, like an OLTP system. They make no sense there.


over-locking a resource covers many many situations of deadlocks. speed of resources has NOTHING to do with anything. CPU, disk, network could be incredibly fast or slow - it will still happen.


b) locking resources in an unpredicable fashion. Transaction T1 locks row(A). Transaction T2 locks row(b). Transaction T1 tries to lock row(b) - it obviously blocks. Transaction T2 tries to lock row(A). Deadlock.

I don't care how fast or slow your cpu, disk, network is - T1 locking A and T2 locking B (at exactly the same instant!!! there is no network involved in accessing the SGA, the data can be assumed to be in the buffer cache, and T1 and T2 each have their own infinitely fast CPU to run on) - followed by T1 trying to lock B and T2 trying to lock A will result in a deadlock.

solution: application developers design their applications to gain resources in a predicable, orderly fashion.


c) internal limits which cause over-locking of a resource. In this case, an implementation in the database causes you to appear to lock more than anticipated. The most common cause of this would be ITL blocking (interested transaction lists). Every block in the database has an ITL- each slot in the ITL represents a transaction active on that block. The ITL will grow dynamically (starts at a minimum of two, defined by INITRANS, and can grow to 255). That represents the maximum number of concurrent transactions on a given block.

If the ITL, which starts at two, is denied the ability to grow (because a block is jam packed with data) you might end up having ITL waits. For example, suppose a block is very very full and the ITL list can only grow to 10 entries (at 23 bytes per entry). Suppose there are 100 rows on this block. Suppose 10 of those rows are locked by 10 distinct transactions. Suppose an 11th transaction comes along and tries to update an un-locked row. it will not be able to because there is no ITL slot for it - it will stop and wait for one of the 10 to complete. Now, if that 11th transaction has already been doing some work and has locked resources itself - it may begin to block the transactions active on the block it is waiting on. We are now in a situation just described in (a) again - over locking resources


solution: increase initrans, or decrease the number of rows per block (minimize records per block, or pad the rows, or set pctfree to a much higher value).


again, the speed of the machine has nothing to do with it at all.





Ask the developers to lay out an example where cpu speed, network speek, or disk speed is the cause of a deadlock. I'd be very interested in that story (and it would be a fictional story - because we'd be able to parse the story apart and explain how cpu/disk/network speed had NOTHING to do with the actual deadlock).