This is a case about enqueue from the book Oracle Core. The case demonstrates two feature of oracle enqueue:
1. Lock conversion
Lock conversion appears in the common foreign key problem, If supporting index is missing on a foreign key, and a session is try to update or delete on the parent table, it will lock the child table with mode 4. If the session already hold a lock on child table with mode 3, when it update or delete on the parent table, it’ll convert the lock to mode 5.
2. The priority of the converters and waiters
There are 3 queues per enqueue, owners/converters/waiters. Once the owner release the enqueue, which session will hold the lock first, the one in converters queue or the one in waiters queue? 继续阅读