Query and handling of Oracle deadlocks!

How to query and handle Oracle deadlocks

During project development, table design issues may cause deadlocks during table row-level operations, the following describes how to check for deadlock statements and related solutions for your reference:

Check Methods for database deadlocks

I. Database deadlock

When the program is executed, click OK or save. The program does not respond or reports an error.

Ii. deadlock Principle

When you update or delete a column in a table in the database, the statement is not submitted after the execution is completed, another statement that updates this column of data is in the waiting state during execution. This statement is still being executed but has not been successfully executed, no error is reported.

Iii. deadlock locating

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 ))

Iv. Solution to deadlock

Generally, you only need to submit statements that generate deadlocks, but in the actual execution process. The user may not know which statement is used to generate a deadlock. You can close the program and restart it.

This problem is often encountered during the use of Oracle, so a few solutions are also summarized.

1) Find the deadlock process:

Log On As A sysdba user and run the following query statement to find the deadlock process:

Select S. username, L. object_id, L. session_id, S. Serial #,

L. oracle_username, L. OS _user_name, L. Process

From v $ locked_object L, V $ session s where L. session_id = S. Sid;

2) Kill the deadlock process:

Alter system kill session 'sid, serial # '; (SID = L. session_id)

3) if the problem persists:

Select pro. spid from V $ session SES, V $ process pro where SES. Sid = XX and SES. paddr = pro. ADDR;

Replace the SID with the SID of the deadlock:

PS-Ef | grep spid (this command must be executed in the Command window)

Spid is the process Number of the process and kill the Oracle process.

 

In addition, in order to better avoid deadlocks, we should consider the table structure design architecture when designing the database. If there is a master-slave table, the slave table should establish a foreign key association with the master table, the Foreign keys in the table should be indexed to avoid deadlock!