事务未提交导致行锁等待问题处理

# 事务未提交导致行锁等待

1. 巡检发现有6个会话一直没执行完成:update workflow\_nodeformgroup set isadd\=:1 ,isedit\=:2 ,isdelete\=:3 ,ishidenull\=:4 ,isdefault\=:5 ,isneed\=:6 ,isopensapmul\=:7 ,defaultrows\=:8 ,isprintserial\=:9 ,allowscroll\=:10 ,isopenpaging\=:11 ,mobileallowscroll\=:12 ,mobilecardedit\=:13 ,iscopy\=:14 ,isprintthead\=:15 where nodeid\=:16 and groupid\=:17

![](/media/202602/20260228131834386317.png)

1. 执行开始时间是昨天晚上17点多，已经执行了超12小时。

![](/media/202602/20260228131834401718.png)

1. 他们等待的会话都是494

![](/media/202602/20260228131834412795.png)

1. 494会话如下：

![](/media/202602/20260228131834421656.png)

1. 494会话查询：
	1. SELECT sid, serial\#, username, status,
	2. program, machine, logon\_time,
	3. to\_char(logon\_time, 'YYYY\-MM\-DD HH24:MI:SS') as login\_time
	4. FROM v$session
	5. WHERE sid \= 494;
2. 查询当前数据库阻塞情况：
	1. SELECT
	2. w.sid waiting\_session,
	3. h.sid holding\_session,
	4. w.type wait\_type,
	5. h.type hold\_type,
	6. w.id1, w.id2,
	7. w.ctime seconds\_waiting,
	8. s1\.username waiting\_user,
	9. s2\.username holding\_user,
	10. s1\.program waiting\_program,
	11. s2\.program holding\_program
	12. FROM v$lock w, v$lock h, v$session s1, v$session s2
	13. WHERE h.id1 \= w.id1
	14. AND h.id2 \= w.id2
	15. AND w.request \> 0
	16. AND h.block \> 0
	17. AND w.sid \= s1\.sid
	18. AND h.sid \= s2\.sid;

![](/media/202602/20260228131834431517.png)

1. 我们认为是494会话执行完后（会话状态inactive）未提交，导致其他会话等待其提交导致阻塞。
2. 解决方案，杀死会话494：ALTER SYSTEM KILL SESSION '494,44504' IMMEDIATE;
3. 在查看阻塞情况
	1. SELECT
	2. w.sid waiting\_session,
	3. h.sid holding\_session,
	4. w.type wait\_type,
	5. h.type hold\_type,
	6. w.id1, w.id2,
	7. w.ctime seconds\_waiting,
	8. s1\.username waiting\_user,
	9. s2\.username holding\_user,
	10. s1\.program waiting\_program,
	11. s2\.program holding\_program
	12. FROM v$lock w, v$lock h, v$session s1, v$session s2
	13. WHERE h.id1 \= w.id1
	14. AND h.id2 \= w.id2
	15. AND w.request \> 0
	16. AND h.block \> 0
	17. AND w.sid \= s1\.sid
	18. AND h.sid \= s2\.sid;

![](/media/202602/20260228131834441498.png)

1. 其他会话也释放了

![](/media/202602/20260228131834460551.png)

1. 会话恢复正常

![](/media/202602/20260228131834468888.png)