Query and handling of oracle deadlock issues

Oracle deadlock query and handling I. Database deadlock phenomenon during execution of the www.2cto.com program, click OK or save. The program does not respond or reports an error.
Ii. deadlock principle when an update or delete operation is performed on a column in a table in the database, the statement will not be handed over after execution, 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 method by checking the database table, we can check which statement is deadlocked and which machine is causing the deadlock. 1) Use the dba to execute the following statements: select username, lockwait, status, machine, program from v $ session where sid in (select session_id from v $ locked_object, it indicates that there is a deadlock and you can see which machine is deadlocked. Field Description: Username: the database user used by the deadlock statement; Lockwait: the deadlock status. If there is content, it indicates the deadlock. Status: Status. active indicates 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 ))
4. solution to the deadlock generally, you only need to submit the statement that generates the deadlock, but in the actual execution process. You 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) process for deadlock query: 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) 3) if the problem persists: select pro. spid from v $ session ses, v $ process pro where ses. sid = XX and ses. paddr = pro. addr, where sid uses the si of the deadlock D Replace: exitps-ef | grep spid where spid is the process Number of the process, kill the Oracle process KILL-9 "SPID just found" on the WINDOWS platform, it can be even the orakill. 4) query the deadlock statement select. 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 Cwhere. HASH_VALUE = B. SQL _HASH_VALUE andB. SID = C. session_ID streamline processing steps -- 1. Find the object_idselect object_id from all_objects where object_name = upper ('table _ name') of the locked table ') and object_type = 'table' -- 2. Search for the session IDselect session_id of the locked object according to the object_id found in step 2 from v $ locked_object where object_id = 1st -- 3. Check the session_id according to step 3. search for serial # select sid, serial # from v $ session where sid = 284 -- 4. Based on session_id and serial #, kill the process alter system kill session 'sid, serial # '-- query the operation statement on the locked object select SQL _text from v $ sqlarea where address = '00' -- address is the SQL _address field in v $ session