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:
DELETE FROM MYTABLE by unique IDtook almost 10 seconds at one point.
- There are no other "touches" on
MYTABLEfor that Unique ID on or around the time of the
DELETE FROM MYTABLE by Unique ID.
- That DELETE blocked other
- 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 IDwould complete sub-second, as one would expect.
I verified with our DBAs that:
- We do not have any
on deletetriggers on the table in question.
- 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.