Problem Description:
Customer side of the front-end application of an operation hang, no error and no operation of the normal ending prompt.
View the lock information as follows:
Sql> with LK as (select Blocking_instance| | '. ' | | blocking_session blocker, inst_id| | '. ' | | Sid Waiter
2 from Gv$session
3 where blocking_instance is not null
4 and blocking_session are NOT null)
5 Select Lpad (", level-1) | | Waiter Lock_tree from
6 (SELECT * FROM LK
7 UNION ALL
8 SELECT distinct ' Root ', blocker from LK
9 where blocker not in (select Waiter from LK)
Ten connect by prior Waiter=blocker start with blocker= ' root ';
Lock_tree
-------------------------------------------------------------------------------
1.71
1.1349
2.136
After confirming with the customer side, kill holds the lock session:
Sql> alter system kill session ' 71,36519 ';
Alter system kill session ' 71,36519 '
*
ERROR at line 1:
Ora-00031:session marked for Kill
sql> select spid, Osuser, s.program from V$session S, v$process p where s.paddr = p.addr and s.sid = 71;
SPID Osuser Program
------------------------ ------------------------------
16783
[Email protected] ~]$ kill-9 16783
Check back in about 3 seconds:
[Email protected] ~]$ kill-9 16783
-bash:kill: (16783)-No such process
Sql>/
Lock_tree
-------------------------------------------------------------------------------
1.1349
2.136
Sql> Select sid,serial# from v$session where sid=1349;
SID serial#
---------- ----------
1349 3539
Kill the session with the same error ora-00031:session marked for kill.
sql> select spid, Osuser, s.program from V$session S, v$process p where s.paddr = p.addr and s.sid = 1349;
SPID Osuser Program
------------------------ ------------------------------
8581
[Email protected] ~]$ kill-9 8581
[Email protected] ~]$ Ps-ef |grep 8581
Oracle 22749 21897 0 10:34 pts/1 00:00:00 grep 8581
Sql>/
Lock_tree
-------------------------------------------------------------------------------
1.1349
2.136
sql> select spid, Osuser, s.program from V$session S, v$process p where s.paddr = p.addr and s.sid = 1349;
SPID Osuser Program
------------------------ ------------------------------
8581
Check back in about 5 seconds:
[Email protected] ~]$ kill-9 8581
-bash:kill: (8581)-No such process
Sql>/
Lock_tree
-------------------------------------------------------------------------------
1.1349
2.136
Sql>/
No rows selected.
It is determined that the background rollback operation has not ended after the kill session, so this SID process ends slowly.
After the kill correlation holds the lock process, the re-execution of the page operation results in a new row lock wait.
Analysis:
The SQL statements that were executed during the 1.71 process (the bearer process for each row lock) are:
Sql> Select Sid,sql_text from v$session a,v$sql b where Sid in () and (b.sql_id=a.sql_id or b.sql_id=a.prev_sql_id);
The query result is an INSERT statement.
The SQL statement that was executed by the customer to confirm the 1.71 process (the bearer process of the row lock) is the SQL statement that the customer is holding the page executing.
Select Owner,object_type from dba_objects where object_name= "test_table"; the result is a synonym.
The query confirms that the object is a synonym for the Oracle test_table, and the client confirms that the insert operation is written directly by Oracle to the SQL Server database; The object test_table is a business table on the SQL Server side. Oracle creates a dblink connection to SQL Server via the gateway, pushing data from the Oracle side to SQL Server. (This Oracle end-of-current lock issue is due to the SQL Server side database insert Hang, which causes the Oracle end insert to fail.) )
Locate the client side of the system SQL Server owner and try to hang the same on SQL Server local insert, and the SQL Server owner continues to troubleshoot the issue.
SQL Server Side Insert problem Resolution (SQL Server Engineer Rebuilds table test_table), the Oracle side does not reappear with row locks. The client business can proceed normally.
As for the reason why the Oracle side generates row locks, it is further analyzed by business developers based on the business logic.
Example: Because the insert produces a row lock experiment:
Sql> CREATE TABLE t (ID int primary key);
Table created.
Sql> INSERT INTO T values (1); --Insert Not submitted
1 row created.
Session 2:
Sql> INSERT INTO T values (1); --SQL generation waits when the same primary key record is inserted at this time
Session 3:
Sql> Select Sid,type,id1,id2,lmode,request,block from V$lock where SID of (834,854) Order by 1, 2;
SID TY ID1 ID2 lmode REQUEST BLOCK
---------- -- -------------- ------- ---------- ----------
834 TM 91874 0 3 0 0
834 TX 262174 192335 6 0 0
834 TX 458776 193901 0 4 0
854 TM 91874 0 3 0 0
854 TX 458776 193901 6 0 1
Sql> Select Sid,event from v$session_wait where Sid in (834,854);
SID EVENT
---------- ----------------------------------------------------------------
834 Enq:tx-row Lock contention
854 Sql*net Message from client
Description: When a row lock wait occurs, the table data can be queried normally.
Row lock waits caused by Oracle insert hang
