Solution to deadlock in Oracle Data Tables: oracle Solution

Solution to deadlock in Oracle Data Tables: oracle Solution

During database management, tables are often locked due to some unreasonable operations on data tables. The following describes how to find which tables are locked by which users, and how to unlock:
1. Find the locked table:

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 blocked, you can view the following statement:

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 =. sid) | 'blocked '| (select username |': '| sid |': '| serial # from v $ session where sid = B. sid) from v $ lock a, v $ lock B where. block = 1 and B. request> 0 and. id1 = B. id1 and. id2 = B. id2;

2. Determine the sid and serial number of the locked table user (either determined by the oracle user or by the System user)
A. determined by the oracle user

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

B. determined by the System user

select sid,serial# from v$session where username='administrator';

3. Kill the process that causes the deadlock (it is known that 123 blocks the process No. 124 and can be unlocked after it is killed)

alter system kill session ‘123,3935';

At this point, the deadlock in the table is eliminated, and the data table is locked normally. After the transaction is committed, the deadlock will naturally disappear.

Oracle rac Deadlock Detection Mechanism
In the rac environment, the deadlock check is completed through the LMD process. By default, the deadlock check is performed once every 60 seconds. That is to say, the deadlock check takes at least one minute or longer, it depends on the system load.
In addition, in the rac environment, Deadlock Detection requires communication between all nodes, so it is much slower than in the single-host environment. Here, the implicit parameter _ lm_dd_interval controls the frequency of LMD operation (in general, do not adjust this parameter)

How can we avoid deadlocks?
Corresponding to the internal deadlock in oracle, oracle will handle it by itself, but manual intervention is required due to deadlocks caused by applications or human deadlock.
The following four conditions are required for an Oracle deadlock:
1) resources cannot be shared and can only be used by one process.
2) the process that has obtained the resource can apply for a new resource again.
3) allocated resources cannot be forcibly deprived from the corresponding process.
4) several processes in the system form a ring. Each process in the ring is waiting for resources occupied by adjacent processes.

Articles you may be interested in:

• Brief description of the deadlock query and solution in Oracle Database
• How to execute an SQL job in an oracle session deadlock
• How to delete a deadlock process in Oracle
• How Does Oracle handle deadlocks?
• Example of deadlock caused by Oracle foreign key not indexed
• Describes how to end a deadlock process in an Oracle database.