Hi everyone,
we're having a hard time with deadlocks, and we have no idea what's causing it (we have some guesses but no proofs). We have to "main" tables - DOCUMENTS and MESSAGES, that are not directly linked (there's no foreign key between them), and in most cases there are two sessions - session A is running an UPDATE on the MESSAGES table, session B INSERTs rows into the DOCUMENTS table. And these two sessions deadlock for some strange reason - session A waits for session B and vice versa. The funny thing is the objects the sessions are waiting for are quite separated from the main tables, and we have no idea why the DML operations try to acquite locks on them (X, SX, SSX).
More precisely - these tables are involved:
DOCUMENTS
MESSAGES
ATTACHMENTS
CHECKS
MESSAGE_LOG (FK to MESSAGES.ID)
ATTACHMENTS_CHECKS (FK to CHECKS.ID and FK to ATTACHMENTS.ID)
DOCUMENTS_ATTACHMENTS (FK to ATTACHMENTS.ID and FK to DOCUMENTS.ID)
so it's something like this
MESSAGES - [N:1] - MESSAGE_LOG - [1:N] - CHECKS - [1:N] - ATTACHMENTS_CHECKS - [N:1] - ATTACHMENTS - [1:N] - DOCUMENTS_ATTACHMENTS - [N:1] - DOCUMENTS
The problem is that once the two sessions try to do this:
A: UPDATE MESSAGES SET ID = :id, .... WHERE ...
B: INSERT INTO DOCUMENTS VALUES (...)
we receive a deadlock :-( But one of the 'locked resources' is in the ATTACHMENTS_CHECKS tables (usually a SYS_LOB00000.... segment belonging to a BLOB column) and the other one is (usually) in the DOCUMENTS table (a bitmap index). I have no idea why the DML operations listed above try to lock the SYS_LOB0000 object - the queries has nothing to do with it. And I don't see why the 'UPDATE MESSAGES ...' query tries to lock a bitmap index in the DOCUMENTS table (it is not a FK column). There are no triggers, no related constraints etc. on any of the mentioned tables.
Is there a way to see what resources is a query going to lock and why?
A few notes - First, I know updating a primary key (AS in 'UPDATE MESSAGES SET ID = :id ...' is a very bad idea as it may lock referencing tables, and we're going to fix it), but it probably is not the root cause here. Second, we do have some bitmap indexes here, but those are not listed in the deadlock info so I guess it's not the cause either.