Common causes and solutions for Oracle deadlocks

Common causes and solutions for Oracle deadlocks

1. deadlock caused by deletion and update

The reason for the deadlock is that multiple threads or processes compete for or depend on the same resource. Here is an example of a deadlock caused by competition for the same resource.

CREATE TABLE testLock(  ID NUMBER, test VARCHAR(100)  ) COMMIT  INSERT INTO testLock VALUES(1,'test1'); INSERT INTO testLock VALUES(2,'test2'); COMMIT; SELECT * FROM testLock 

        ID TEST  ---------- ----------------------------------           1 test1           2 test2 

Recurrence of deadlock:
1. Execute in the SQL window:

SELECT * FROM testLock for update; -- apply a row-Level Lock and modify the content. Do not submit

Query deadlocks:

select s.username,l.object_id, l.session_id,s.serial#, s.lockwait,s.status,s.machine,s.program from v$session s,v$locked_object l where s.sid = l.session_id;

Field description:

Username: Database User used by the deadlock statement; SID: session identifier, session identifier. session is a context between the communication parties from the beginning to the end of the communication. SERIAL #: sid will be reused, but when the same sid is reused, serial # will increase and will not be repeated. Lockwait: this field can be used to query information about the currently waiting lock. Status: used to determine the session Status. Active: the SQL statement is being executed. Inactive: Waiting for operation. Killed: marked as deleted. Machine: The Machine where the deadlock statement is located. Program: the application that generates the deadlock statement.

At this time, there is a line in the Command waiting for the Inactive operation

View the statement that caused the deadlock:

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

There is no deadlock statement.

2. Open another command window and execute: delete from testLock where id = 1;

A deadlock occurs at this time (note that a new window should be opened at this time, otherwise THE system will prompt: post the change record to the database. Click yes and Force commit ):

View deadlocks:

SQL>  select s.username,l.object_id, l.session_id,s.serial#, s.lockwait,s.status,s.machine,s.program from v$session s,v$locked_object l where s.sid = l.session_id;

View the statement that caused the deadlock:

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

Check the following statement deadlock:

delete from testLock where  ID = 1 

Deadlock handling: alter system kill session 'session _ id, serial #';

alter system kill session '301,16405'; 

Check the deadlock again and you will find that there is no stauts active record, and the deadlock statement has been terminated.

2. deadlock caused by no index on the foreign key

The customer's 10.2.0.4 RAC for AIX environment frequently encountered a ORA-60 deadlock problem, resulting in the application could not run smoothly.
After a series of diagnoses, it is found that the final problem is that no index is created on the foreign key. Because the program deletes data on the primary table and the index is missing, the row-Level Lock is upgraded to the table-Level Lock, this eventually leads to a large number of lock waits and deadlocks.
The following uses an example to simulate the problem:

SQL> create table t_p (id number primary key, name varchar2(30)); Table created. SQL> create table t_f (fid number, f_name varchar2(30), foreign key (fid) references t_p); Table created. SQL> insert into t_p values (1, 'a'); 1 row created. SQL> insert into t_f values (1, 'a'); 1 row created. SQL> insert into t_p values (2, 'b'); 1 row created. SQL> insert into t_f values (2, 'c'); 1 row created. SQL> commit; Commit complete. SQL> delete t_f where fid = 2; 1 row deleted. 

In this case, the sub-table is also deleted in session 2:

SQL2> delete t_f where fid = 1; 1 row deleted. 

Return to session 1 and delete the master table:

SQL> delete t_p where id = 2; 

When the session is locked, return to session 2 and delete the master table:

SQL2> delete t_p where id = 1; 

Session is also locked, when Session 1 Statement is rolled back, the ORA-60 deadlock error occurs:

delete t_p where id = 2 

* ERROR at line 1: ORA-00060: deadlock detected while waiting for resource SQL> rollback; Rollback complete. 

Roll back Session 1. Session 2 also rolls back and creates an index on the foreign key column:

1 row deleted. SQL2> rollback; Rollback complete. SQL2> create index ind_t_f_fid on t_f(fid); Index created. 

Repeat the preceding step Session 1 to delete the sub-Table record:

SQL> delete t_f where fid = 2; 1 row deleted. 

Session 2 delete sub-table records:

SQL2> delete t_f where fid = 1; 1 row deleted. 

Session 1 deletes a master table record:

SQL> delete t_p where id = 2; 1 row deleted. 

Session 2: delete a master table record:

SQL> delete t_p where id = 1; 1 row deleted. 

All the delete operations can be successfully performed. The difference in lock information between the two cases will not be further analyzed here. The key is to create an index on the foreign key column.

Although some articles have mentioned that, in some cases, indexes cannot be created on the foreign key column, my point is that since foreign keys are created, do not care about an additional index, because the cost of an index is negligible compared to the problems caused by missing indexes.