Analysis on the Problem of "ORA-00060: deadlock detected while waiting for resources"

Recent field feedback: a messaging program accessing the Oracle database records some error logs about "ORA-00060: deadlock detected while waiting for resources. However, after the communication program record is abnormal, it does not crash and can continue to run, and the communication data looks quite normal.

In fact, because the database has a large number of concurrent accesses, as long as there is concurrent access, the deadlock cannot be completely avoided, it is nothing more than the probability. The oracle database has the function of detecting the current session deadlock. When a deadlock occurs, two or more concurrent transactions wait for each other and the execution can no longer be completed without external force. When the database detects a deadlock, it will roll back the deadlocked transactions and throw a ORA-00060 exception. Therefore, the data access program, especially the unattended program, must be able to handle this exception. After this exception occurs in the database, record it in the log and continue (re) access the database.

Of course, we can also reduce the chance of deadlocks by improving program efficiency. First, you can provide database access efficiency, optimize the database, optimize SQL, and minimize the database access time, thus reducing the possibility of concurrent deadlocks with other sessions. In addition, when the business permits, the scope of database transactions should be minimized, a large transaction should be divided into small transactions and submitted as soon as possible. Of course, the division of transactions is determined by the business and cannot be separated at will.