User A deletes, but does not commit.
Copy Code code as follows:
sql> Delete from test where object_id<10;
8 rows have been deleted.
User B performs a deletion or updates a id<10 record, and is blocked.
Copy Code code as follows:
sql> Update test set flag= ' N ' where object_id<10;
To meet this blockage, you first need to identify the problem. You can use the following script.
Copy Code code as follows:
Select T2.username,t2.sid,t2.serial#,t2.logon_time
From V$locked_object t1,v$session T2
where T1.session_id=t2.sid order by T2.logon_time;
The results are as follows:
Copy Code code as follows:
USERNAME SID serial# Logon_time
------------------------------ ---------- ---------- --------------
Lihuilin 14 87 September-November-13
Lihuilin 139 655 September-November-13
or use
Copy Code code as follows:
Select
(select username from v$session where sid=a.sid) blocker,
A.sid, ' is blocking ',
(select username from v$session where sid=b.sid) Blockee,
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;
The results are as follows:
Copy Code code as follows:
Blocker sid ' Isblocking ' blockee sid
------------------------------ ---------- ------------- ------------------------------ ----------
Lihuilin Blocking Lihuilin 139
Kill causes a blocking session
Copy Code code as follows:
Select ' Alter system kill session ' ' | | sid| | ', ' | | serial#| | '; ' cmd from v$session where username= ' Lihuilin ' and sid=14;
The results are as follows:
Copy Code code as follows:
Cmd
-----------------------------------------
Alter system kill session ' 14,87 ';
Finally executes the alter system command, blocking the release.
