(It comes from a product support task)
Checking the Oracle log file and find out the deadlock graph.
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock:
Deadlock graph:
------------Blocker(s)----------- ------------Waiter(s)------------
Resource Name process session holds waits serial process session holds waits serial
TM-00017036-00000000-00000000-00000000 163 512 SX SSX 62851 266 349 SX SSX 13503
TM-00017036-00000000-00000000-00000000 266 349 SX SSX 13503 163 512 SX SSX 62851
The deadlock graph tells that the lock type is TM having a deadlock graph like 'TM SX SSX SX SSX', which suggest the likely deadlock type "Missing index on foreign key(FK) constraint"
The deadlock happens as delete an invoice. (A invoice has zero or many invoice lines.)
{InvoiceLineToInvoice.java196, when InvoiceApi.deleteInvoice(invoice);}
OK; then mostly, my best guess according to the above, is due to the invoice line has been locked by a process.
I need a piece of evidence to approve this assumption.
I need a piece of evidence to approve this assumption.
as reading from the code, I have such an impression:
it seems that there are two threads or processes, Tr1 and Tr2;
it seems that there are two threads or processes, Tr1 and Tr2;
Tr1 updates invoice line
Tr2 deletes the invoice.
Tr2 deletes the invoice.
An invoice contains many invoice-lines
Tr1 holds an invoice-line (L1).
Tr2 holds an invoice (I).
Tr2 holds an invoice (I).
So when deleting an I (owned by tr1), whose operation cascades to its derivative table(L1);
when updating the L1, which operation backtrack to its parent (I);
when updating the L1, which operation backtrack to its parent (I);
and this may happen simultaneously.
These two processes should be wrapped in one transaction, and then it will be operated in sequential order, rather than a competition.
The Oracle log in production shows that: "The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock "
The deadlock graph tells that the lock type is TM having a deadlock graph like 'TM SX SSX SX SSX', which suggest the likely deadlock type "Missing index on foreign key(FK) constraint"
The deadlock happens as delete an invoice.
OK; then mostly, my best guess according to the above, is due to the invoice line has been locked by a process. I need evidence to approve this assumption.
The deadlock graph tells that the lock type is TM having a deadlock graph like 'TM SX SSX SX SSX', which suggest the likely deadlock type "Missing index on foreign key(FK) constraint"
The deadlock happens as delete an invoice.
OK; then mostly, my best guess according to the above, is due to the invoice line has been locked by a process. I need evidence to approve this assumption.
I found something new, which exactly fits our case. It helps to understand Oracle deadlock graph.
I picked out the reason:
In short: Oracle doesn't create an index for the foreign, but on the primary key.
The detail:
Why unindexed foreign keys cause deadlocks
When you have an unindexed foreign key, Oracle acquires a full table lock on the child table when the primary key in the parent table is modified in some way. This could be as a result of updating the primary key or deleting a row, but not by inserting. The full table lock is acquired and released for every primary key modification that occurs, so if you are updating several rows in the table you will be locking/unlocking the table several times too. Whilst the table is locked, no other sessions may modify the child table, which is what causes the problem.
In conclusion, my recommendation (if you want to avoid deadlocks and performance hits) is to ALWAYS add an index when you create a foreign key.
the reference:
http://harrydoescode.co.uk/oracle-avoid-unindexed-foreign-keys/
I picked out the reason:
In short: Oracle doesn't create an index for the foreign, but on the primary key.
The detail:
Why unindexed foreign keys cause deadlocks
When you have an unindexed foreign key, Oracle acquires a full table lock on the child table when the primary key in the parent table is modified in some way. This could be as a result of updating the primary key or deleting a row, but not by inserting. The full table lock is acquired and released for every primary key modification that occurs, so if you are updating several rows in the table you will be locking/unlocking the table several times too. Whilst the table is locked, no other sessions may modify the child table, which is what causes the problem.
In conclusion, my recommendation (if you want to avoid deadlocks and performance hits) is to ALWAYS add an index when you create a foreign key.
the reference:
http://harrydoescode.co.uk/oracle-avoid-unindexed-foreign-keys/
the drawback of indexing foreign key: (1) When the table is small, it negatively reduces the performance due to extra indexing operations. for our case, invoice-line is a huge table, it will improve the performance. (2) Multiple foreign key indexing will slow down the insertion. for our case, I think it gives more benefits than the single drawback.
No comments:
Post a Comment