AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
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. 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. Similarly when you delete a row from table1 Oracle block inserts of the potential children to ensure FK validation. But the same SQL statement can have different execution plans in some cases. You will see ORA-00060: deadlock detected while waiting for resource in the following scenario To validate the FK when you insert a row into table2 Oracle blockd deletes to the parents row in table1. 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. For more information about Oracle (NYSE:ORCL), visit Alex Poole suggested, you definitely want to look for the trace file. Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts. To view full details, sign in with your My Oracle Support account.Äon't have a My Oracle Support account? Click to get started! Important: The notes below are for experienced users - See Details Sign In The purpose of this document is to describe the error ORA-00060. Information in this document applies to any platform. Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later ( always around 40 to 80 dead locks ) I had checked the trace file ( which is located at /oracle//saptrace/usertrace/c.) and got the following deadlock graph.Action: Look at the trace file to see the transactions and resources involved. at 01:29 PM ORA-00060: Deadlock detected 498 Views Follow RSS Feed Hi, I am getting the error 'ORA-00060: Deadlock detected' continuously while DB check. Oracle Database Cloud Schema Service - Version N/A and later ORA-00060: deadlock detected while waiting for resource Cause: Transactions deadlocked one another while waiting for resources. Oracle Database - Standard Edition - Version 7.0.16.0 and later Oracle Database - Personal Edition - Version 7.1.4.0 and later Oracle Database - Enterprise Edition - Version 7.0.16.0 and later OERR: ORA-60 "deadlock detected while waiting for resource"
0 Comments
Read More
Leave a Reply. |