Oracle 11g delete by unique key slow

Question

We have been plagued by a deadlock issue for quite some time that relates to deletes and inserts on the same table. It happens sporadically, but had been increasing in frequency. It would manifest itself as a DELETE FROM MYTABLE by unique ID statement BLOCKING INSERT INTO MYTABLE statements.

Initially, we could not see the bind variables because we were using Hibernate and we could not turn on show_sql for Hibernate since there is far too much traffic on other Hibernate-managed POJOs in other areas of the application. We had thought Hibernate might be doing something with its cache at unexpected times.

Recently, I removed Hibernate for the table in question, replaced it with JDBC, and log EVERY touch (every SELECT/INSERT/UPDATE/DELETE) against that table.

After doing that, I've only seen 1 "blocker". So I don't know if the problem is fixed by virtue of removing Hibernate and doing everything via JDBC. However, I would be remiss if I didn't attempt to run down that blocker instance.

Here's what I see:

• A DELETE FROM MYTABLE by unique ID took almost 10 seconds at one point.
• There are no other "touches" on MYTABLE for that Unique ID on or around the time of the DELETE FROM MYTABLE by Unique ID.
• That DELETE blocked other INSERT INTO statements.
• The "blocker" Delete statement completed in 10 seconds and cleared itself, and after that, everything was fine.
• It only happened once, the rest of the day DELETE FROM MYTABLE by Unique ID would complete sub-second, as one would expect.

I verified with our DBAs that:

1. We do not have any on delete triggers on the table in question.
2. All FKs in that table are indexed.

Does anyone have any insight/recommendations on what might cause a Delete statement by Unique ID to possibly take 10 seconds, when there is no other touches on that table for that row (no select, update, or insert by that Unique ID)?

I realize that there probably isn't a precise and exact answer to this questions, I'm just hoping someone can share insight or anecdotal information on their own experiences.

Thank you sincerely in advance.

• user1822: Is it possible, that the insert was trying to insert the same (unique) ID as the delete was trying to delete? Did you check e.g. dba_waiters during that time? Or v$lock$ or v$session_wait or V$SESSION_BLOCKERS
• Chris Saxon: Are there any updates taking place on this table? If so, is it possible that the updates are blocking the delete? Also, how large is the table?
• Colin 't Hart: While Oracle does row-level locking, there is a limit to how many rows can be locked per block. If you have a lot of concurrent updates resulting in contention on a particular block, this could be causing your problem.

Answer 1

This could happen if: - Your table MYTABLE has a unique column UNIQCOL - This MYTABLE.UNIQCOL is referenced by some column in another table, say MYTABLE2.UNIQCOL_REF - This MYTABLE2.UNIQCOL_REF is not indexed.

Adding a (non-unique) index to MYTABLE2.UNIQCOL_REF could then solve the problem.

(You said all FKs in MYTABLE are indexed, but you didn't say that all FKs referencing MYTABLE are indexed - they should be to avoid whole-table locks on MYTABLE2)

• Philip Tenn: bingo. Yesterday, our DBAs actually did find out that a child table had a FK to MYTABLE that was unindexed. The delete from MYTABLE was causing a table lock on the child table. Good answer, you were correct.

Answer 2

Delete is a DML command and stores the data in redo log till the delete operation is committed. This means that if data to be removed by delete is slightly large[even though search time is less] it will take longer time as it will move data to redo log.

So may be the instance when your operation took longer large no. of rows were being deleted to many queries might be writing to redo log.

• Philip Tenn: Thanks for the response and the information about the redo log. However, I'm certain the delete only took one single row, since it was a delete by unique ID.
• loki: Do you have sql trace for this? Is this issue reproducible? or it just happened one?

Answer 3

Consider a parent/child table such as client/order.

You can't delete a client that has an order. Say client 123 has an order A123. Fred does a delete for that order but does not commit. Then "Jane" tries to delete client 123.

Since Fred's statement can potentially rollback, the client can't be deleted because it isn't allowed to the leave the order orphaned. Jane's delete transaction will wait on the lock that Fred's Delete has on the order. If Fred commits, then Jane's can complete.

Similar situations can happen with uncommitted updates and inserts. It is possible that there's some ugly connection pooling issue where a single front end action is using two database transactions to try to delete a client and his orders.

Doesn't explain it blocking the other inserts into MYTABLE though.

If you can catch it in the act again, try to look at the EVENT in V$SESSION for the long-running session as that will indicate what it is actually waiting for/doing.

Answer 4

1. i would check that the deltee is using the index. run explain plan and make sure the execution plan includes a unique index access path. this is trivial but god is in the details.

2. maybe what you got here is a hot spot issue. are the rows you are deleting/inserting are of high consecutive pk values ?

b-tree index is stored as a sorted index - entries are organized based on their value from left to right. every delete statement from the table is also deleting an entry from the index and balancing the tree - so not only table locks are obtains but also index locks.

if you are deleting high pk values, lets say pk=1000, and inserting a new row with pk = 1001 - the right block of the index is becoming a hot spot and you have a locking problem. what you do in a case like that is making your index descending.

create index uq1 on table1(pk_columns desc);

• important : descending indexes does not allow range scans.