Oracle deadlock avoidance, query and Processing

What is a deadlock when two (or more) Users wait for each other's locked resources, a deadlock will occur (deadlock ). A deadlock will cause the execution of related transactions to stop. Demonstrate two transactions that generate deadlocks ., At time point A, both transactions obtain the lock on the Data row required for the update operation. At this time, both transactions are normal and can continue to be executed.

What is a deadlock when two (or more) Users wait for each other's locked resources, a deadlock will occur (deadlock ). A deadlock will cause the execution of related transactions to stop. Demonstrate two transactions that generate deadlocks. At time point A, both transactions obtain the lock on the Data row required for the update operation. At this time, both transactions are normal and can continue to be executed.

What is deadlock?
When two or more users wait for each other's locked resources, a deadlock will occur ). A deadlock will cause the execution of related transactions to stop. Demonstrate two transactions that generate deadlocks.

At time point A, both transactions obtain the lock on the Data row required for the update operation. At this time, both transactions are normal and can continue to be executed. Next, both transactions must update the data that is currently locked by the other party. Therefore, a deadlock occurs at the time point B, because both transactions cannot obtain the resources required to continue or terminate. No matter how long the two transactions wait, conflicting locks cannot be released. Therefore, this situation is called a deadlock.

Figure: Two transactions with deadlocks

Deadlock Detection
The database can automatically detect deadlocks and roll back statements that cause deadlocks to release conflicting row-level locks to solve deadlocks. The database returns an error message to the transaction that executes the statement-level rollback.
Avoid deadlocks
If two transactions need to access the same group of tables, locking these tables in the same order in the two transactions usually avoids multi-Table deadlock. For example, if a primary table and a schedule need to be updated in the system, the developer should follow certain rules, such as locking the primary table first and then locking the list. If similar rules can be carefully designed and strictly enforced, a deadlock can be fundamentally prevented. If the developer knows that a series of resources need to be locked in the same transaction, the developer should first consider locking the most exclusive resources.

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
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.
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. Users can
I don't 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:

Sqlplus "/as sysdba" (sys/change_on_install)
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)

Alter system kill session '2010 84 '; (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;

The sid is replaced by the sid of the deadlock: exit
Ps-ef | grep spid

Where spid is the process Number of the Process, kill the Oracle Process

From: http://southking.javaeye.com/blog/550832

Select A. SQL _TEXT, B. USERNAME, C. OBJECT_ID, C. SESSION_ID,
B. SERIAL #, C. ORACLE_USERNAME, C. OS _USER_NAME, C. Process,
''' | C. Session_ID | ',' | B. SERIAL # | ''''
From v $ SQL A, v $ session B, v $ locked_object C
Where A. HASH_VALUE = B. SQL _HASH_VALUE and
B. SID = C. Session_ID