Two Oracle deadlock solutions for instances

Two Oracle deadlock solutions for instances

About deadlocks in databases. If you encounter a problem in the application, you will not hesitate to transfer it to the DBA. However, from the current deadlock issue, it is basically the same as the official Oracle description.
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:

When a deadlock is handled in Oracle, the DML statements related to the transaction are automatically revoked. In other words, it is an active process for Oracle to handle the deadlock problem. It will take the initiative to cut off the transaction lock of one of the sessions.

Let's look at a simple deadlock case.

We create two tables lock_test1 and lock_test2, and then use two sessions to describe them.

Session1:

First, create two tables in session1: lock_test1 and lock_test2.

N1 @ TEST11G> create table lock_test1 as select * from cat;

Table created.

N1 @ TEST11G> create table lock_test2 as select * from cat;

Table created.

Then try to delete lock_test1.

N1 @ TEST11G> delete from lock_test1;

20 rows deleted.

Session2:

Switch to session2 and delete lock_test2.

N1 @ TEST11G> delete from lock_test2;

21 rows deleted.

Then, perform the delete operation on lock_test2 in session1. At this time, blocking occurs and no response is returned.

Session1:

N1 @ TEST11G> delete from lock_test2;

 

In session2, we continue to perform the delete operation on the table Lock_test1. At this time, there will be a short pause and we will find that the transaction in session1 has been forcibly revoked.

Session2:

N1 @ TEST11G> delete from lock_test1;

 

The logs in session1 are as follows. We can see that the transactions in session1 are forcibly revoked at this time.

N1 @ TEST11G> delete from lock_test2;

Delete from lock_test2

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

The following steps can be used to describe this problem.

? Session a table1

? Session B table2

? Session a table 2

? Session B table1

So far, we can see that the deadlock has a great impact. Of course, there are more problems than that. There may be deadlocks between multiple tables. For a table, there may also be deadlocks.

Here is a simple example.

Session1:

Create table test as select * from user_tables;

N1 @ TEST11G> delete from test where table_name = 'lock _ test1 ';

1 row deleted.

Session2:

N1 @ TEST11G> delete from test where TABLE_NAME = 'lock _ test2 ';

1 row deleted.

Session1:

N1 @ TEST11G> delete from test where TABLE_NAME = 'lock _ test2 ';

Session2:

N1 @ TEST11G> delete from test where TABLE_NAME = 'lock _ test1 ';

At this time, there will still be the same deadlock problem. At this time, there will be a corresponding lock on the corresponding row. There will be a short pause in session2, and then

The log is as follows:

Delete from test where TABLE_NAME = 'lock _ test2'

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

It can be seen that the deadlock issue is still very easy to generate. pay more attention to the issue of handling multiple concurrency in programming.