Will try to simulate a deadlock scenario when insert/update/delete happens on TableB we need to sum the amount(amt) and then update it in TableA.total_amt ~]$ sqlplus dixdroid/oracle90 I am going to create two tables – TableB is child table of TableA and some supporting objects to simulate the deadlock case. I am going to try with the Level 4 here as I want to get system state dump and don’t want to complicate this scenario. Level 2 will give you cached cursors, process state info, session wait history for all sessions and the system state which is not possible in case of level 1. In todays post I will try to simulate a deadlock scenario in one of my test box and will generate the 10027 trace with level 2 to get more information. The minimum amount of trace information is written at level 1, at this level it will hardly contain deadlock graph and current AQL statements of the sessions involved. I mean the default trace file for deadlock (ora-60) contains cached cursors, a deadlock graph, process state info, current SQL Statements of the session involved and session wait history.Įvent 10027 may be used to augment the trace information with a system state dump or a call stack in an attempt to find the root cause of the deadlocks. Though the deadlock itself creates a trace file in DIAG directory, but 10027 trace event gives you a better control over the amount and type of DIAG information generated in response to the deadlock case. The solution is usually either a schema fix (add an index on a foreign key, convert a bitmap index to a b-tree index), control access to a table (serialize access or at least make sure the statements process in the same order), and as a last resort use exception handling.Recently someone asked me about Oracle debug 10027 trace event which we use in case of a deadlock scenario i.e. However, I'm not advocating single-row processing. If the transaction is committed after every single row there is no way for a deadlock to occur. A transaction must hold a lock and ask for another one. Deadlocks require two transactions and at least one of them must have performed work and then tried to do more work. This query can help you find statements and plans: select sql_id, plan_hash_value from gv$sql where lower(sql_text) like '%table_name%' Īuto-commit might explain why the first version did not cause errors. It might help to find the SQL statements and check for multiple execution plans, and try to fix one plan. For example, if the CHUNK_SIZE bind variable is different that could cause a change in the execution plan. But the same SQL statement can have different execution plans in some cases. The same statement with the same execution plan will always return data in the same order (in practice, but this is not guaranteed!). However, simply adding an ORDER BY likely won't help. Don't assume you know which statements are causing the deadlock, there are several weird ways that deadlocks can happen.Īs ibre5041 pointed out, deadlocks depend on the order in which data is retrieved. The file lists all the related objects and SQL statements. Every deadlock creates a separate trace file on the database. As Alex Poole suggested, you definitely want to look for the trace file.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |