Hi everyone,
we've received an ORA-00060 today, and I'm a bit confused by the message we got:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "APP_USER.APP_SCHEDULER", line 160
ORA-06512: at "APP_USER.APP_SCHEDULER", line 419
ORA-02290: check constraint (APP_USER.PROCESS_TIME_CHECK) violated
ORA-06512: at line 1
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
...
The SQL that failed just updates a very simple table - a list of processes
PROCESS_LIST (
PROCESS_KEY INT PRIMARY KEY,
PROCESS_NAME VARCHAR2(64),
... some more details ...
PROCESS_START DATE,
PROCESS_END DATE,
CONSTRAINT PROCESS_TIME_CHECK CHECK (PROCESS_START <= PROCESS_END)
)
and the SQL is
UPDATE process_list SET process_end = sysdate WHERE process_key = :id
The rest of the values is commited before the process actually starts (and performs this update at the end), and there are no other sessions accessing this row. There are other sessions updating rows for other processes (usually nearby this one). I've seen / fixed many types of deadlocks (caused by locked rows, bitmap indexes, ...) and I know it's usually a sign of poor application logic, but I'm not sure how a check constraint can lead to a deadlock ...
First, I really doubt that check constraint can be violated, because the sequence of commands is something like this:
1) mark the process as started, set PROCESS_START=SYSDATE
2) commit
3) run the process (may be a PL/SQL procedure, external command or something)
4) mark the process as finished, set the PROCESS_END=SYSDATE
So I doubt the constraint is actually violated (contrary to the ORA-02290 exception listed above).
Second, I really wonder how could such simple check constraint lead to a deadlock? There are no foreign keys, no indexes (except the PK index, but the PK column is not updated), nothing suspicious ...
The only thing I've been able to come up with is a problem with low INITRANS - usually there's just one transaction updating the table, so it may happen that the block gets full. Then suddenly several processes end at the same time (usually when there's a burst of short processes), and they all need to update the table at once - this actually gets worse as the updated rows are 'next to each other' i.e. on the same block. In that case the transactions can't extend the ITL and wait (and I guess this could lead to a deadlock). But how is this related to the check constraint?
I've been thinking about increasing the INITRANS (using ALTER TABLE ... MOVE), but it's still just a theory and I'd like to know it really fixes the problem.
PS: Yes, I know there is a tracefile with more details about the deadlock - I'll get that tomorrow, as I don't have access to tracefiles directly and this is a test environment so I don't want to bother the DBA at night.