A simple description of the deadlock query and its solution in Oracle database _oracle

The principle of deadlock
when you perform an update or delete operation on a column of a table in a database, the statement does not mention
Hand, another statement that updates the data for this column will be in the waiting state when executed,
At this point the phenomenon is that the statement has been executed, but has not executed successfully, there is no error.

the method of locating dead lock
by checking the database table, you can check which statement is deadlocked, which is the machine that produces the deadlock.

1 Execute the following statement with the DBA user

Select Username,lockwait,status,machine,program from v$session where SID in 
(select session_id from V$locked_object ) 

If there is a result of the output, then there is a deadlock, and can see the deadlock machine is which. Field Description:

• Username: The database user used by the deadlock statement;
• Lockwait: The state of the deadlock, if there is content to indicate a deadlock.
• Status: State, active indicates deadlock
• Machine: The machine where the deadlock statement resides.
• Program: Which application is the main source of the statement that generated the deadlock.

2 Execute the following statement with the DBA user to view the statement that is deadlocked.

Select Sql_text from V$sql where Hash_value in 
(select Sql_hash_value to V$session where SID in 
(select Session_) ID from V$locked_object))

Example of a deadlock resolution
when a deadlock is processed in Oracle, an automatic transaction-related DML statement is revoked. In other words, Oracle is an active process in dealing with deadlock problems and will actively disconnect one session of the transaction lock.

Let's look at a simple deadlock case.

We create two table lock_test1,lock_test2 and then use two sessions to illustrate.

Session1:

First create two tables in Session1, Lock_test1,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 do a delete operation on the lock_test1.

N1@test11g> Delete from Lock_test1;

Rows deleted.

Session2:

Then switch to Session2 and do a delete operation on Lock_test2.

N1@test11g> Delete from Lock_test2;

Rows deleted.

Immediately thereafter, the delete operation is done on the lock_test2 in the Session1, and there is no response to the blocking situation at this time.

Session1:

N1@test11g> Delete from Lock_test2;

In Session2, we continue to delete the table lock_test1, there will be a short pause, we will find that the Session1 in the transaction was forcibly revoked.

Session2:

N1@test11g> Delete from Lock_test1;

Session1 in the log below, you can see this time the Session1 in the transaction was forcibly revoked.

N1@test11g> Delete from Lock_test2;

Delete from Lock_test2

      *

ERROR in line 1:

Ora-00060:deadlock detected while waiting for resource

This problem can be easily explained in the following steps.

• Session a Table1
• Session b table2
• Session a Table 2
• Session b Table1

So far we can see that the impact of deadlocks is very large, of course, the problem is more than this, there is likely to be a deadlock between multiple tables, for a table, there is also a possibility of deadlock phenomenon.

Let's briefly say an 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 ';

This time will still appear the same deadlock problem, this time in the corresponding line will have the corresponding lock. There will be a brief pause in the Session2, and then the Session1

To be revoked, the resulting log is as follows:

DELETE from TEST WHERE table_name= ' lock_test2 '

 *

ERROR in line 1:

Ora-00060:deadlock detected while Waiti Ng for Resource

Visible deadlock problem is still very easy to produce, in programming to deal with multiple concurrent processing or need a lot of attention.