Briefly describe the query and Solution to deadlocks in Oracle databases, and briefly describe oracle

Briefly describe the query and Solution to deadlocks in Oracle databases, and briefly describe oracle

Deadlock Principle
If you update or delete a column in a table in the database, this statement is not mentioned after the execution.
The statement that updates this column of data is in the waiting state during execution,
In this case, this statement has been executed, but it has not been successfully executed and no error has been reported.
 
Deadlock Locating Method
By checking the database table, you can check which statement is deadlocked and which machine is causing the deadlock.
 
1) Use the dba user to execute the following statements

select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object) 

If there are output results, it indicates a deadlock exists and you can see which machine is deadlocked. Field description:

• Username: the database user used by the deadlock statement;
• Lockwait: the status of the deadlock. If there is content, it indicates the deadlock.
• Status: Status. active indicates a deadlock.
• Machine: The Machine where the deadlock statement is located.
• Program: the application that generates the deadlock statement.

 
2) You can run the following statements with dba to view the statements that have been deadlocked.

select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object))

Example of deadlock Solution
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.

Articles you may be interested in:

• Solutions to deadlocks in Oracle Data Tables
• How to execute an SQL job in an oracle session deadlock
• How to delete a deadlock process in Oracle
• How Does Oracle handle deadlocks?
• Example of deadlock caused by Oracle foreign key not indexed
• Describes how to end a deadlock process in an Oracle database.