Deadlock resolution for Oracle data tables _oracle

In the process of database management, the data table is often the user's unreasonable operation caused the table to be locked, the following describes how to find which tables are locked by which users, and how to unlock:
1. Find locked tables:

Select Object_name,session_id,os_user_name,oracle_username,process,locked_mode,status 
from V$locked_object L, All_objects a 
where l.object_id=a.object_id;

If you want to know which process is blocking which process, you can use the following statement to view:

Select Username,v$lock.sid,trunc (Id1/power (2,16)) Rbs,bitand (Id1,to_number (' ffff ', ' xxxx ')) +0 Slot,id2 Seq,lmode, Request from V$lock, v$session where v$lock.type = ' TX ' and v$lock.sid = v$session.sid and v$session.username = ' CENTER ';

Or

 Select
 (select Username| | ': ' | | sid| | ': ' | serial# from V$session where sid=a.sid) | | 
 Blocked up ' | | 
 (select username; | | sid| | ': ' |  serial# from V$session where Sid=b.sid) 
 from V$lock A, V$lock b 
 where A.block = 1 and 
 b.request > 0 
 and A.ID1 = B.id1 and 
 a.id2 = B.id2;

2. Determine the SID and serial number of the locked table user (can be determined by the Oracle user or by the system user)
A. Identifying through Oracle users

Select sid,serial# from v$session where username= ' center ';

B. Determining through system users

Select sid,serial# from v$session where username= ' Administrator ';

3. Kill the process that caused the deadlock (known to be 123 blocking the 124th process, kill to unlock)

Alter system kill session ' 123,3935 ';

This table deadlock, there is now a normal data table lock, and so on after the transaction is committed to disappear naturally.

Oracle RAC Deadlock detection mechanism
in a RAC environment, deadlock checking is done through the LMD process, which is 60s by default, which means that deadlock checking takes at least 1 minutes or more to be related to the system load.
In addition, in the RAC environment, deadlock detection to the communication between all nodes back and forth, so relative to a stand-alone environment, will be much slower. Where the implied parameters _lm_dd_interval control the frequency of LMD work (in general, do not adjust this parameter)

How to avoid deadlocks?
Oracle internal deadlocks are handled, but due to application-led deadlocks or human-caused deadlocks, manual intervention is required.
The four prerequisites for Oracle deadlock are as follows:
1 resources cannot be shared and can only be used by one process.
2 the process of obtaining resources can again apply for new resources.
3 The resources already allocated cannot be forcibly deprived from the corresponding process.
4 several processes in the system form a ring, and each process in the ring waits for the resources that are being occupied by the adjacent process.