早上9点多的时候,客户反映在七点多他发现数据库中有TX锁,他执行了alter system kill session动作,现在希望我们帮忙查一下当时那个时间点造成锁的原因、语句等信息。
他执行kill session的操作是 ALTER SYSTEM KILL SESSION ‘401,40975’;
这种情况下可以通过v$active_session_history视图查询到历史记录
SQL> col event for a20
SQL> set linesize 180 pagesize 10000
SQL> select SAMPLE_TIME,session_id,sql_id,event,p1,BLOCKING_SESSION from v$active_session_history where event = 'enq: TX - row lock contention'
2 and sample_time >to_timestamp('2021-03-03 04:00:00','yyyy-mm-dd hh24:mi:ss')
3 and sample_time <to_timestamp('2021-03-03 09:00:00','yyyy-mm-dd hh24:mi:ss') and session_id=401;
SAMPLE_TIME SESSION_ID SQL_ID EVENT P1 BLOCKING_SESSION
--------------------------------------------------------------------------- ---------- --------------------------------------- -------------------- ---------- ----------------
03-MAR-21 07.53.25.282 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.24.282 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.23.282 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.22.282 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.21.282 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.20.282 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.19.282 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.18.272 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.17.272 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.16.272 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.15.272 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.14.272 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.13.272 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.12.252 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.11.252 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.10.252 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.09.252 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.08.252 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.07.252 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.06.252 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.05.252 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.04.242 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.03.242 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.02.242 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.01.242 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.53.00.242 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.59.232 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.58.232 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.57.222 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.56.222 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.55.222 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.54.222 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.53.222 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.52.212 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.51.212 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.50.212 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.49.212 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.48.212 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.47.212 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.46.202 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.45.202 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.44.202 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.43.192 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.42.192 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.41.192 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.40.192 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.39.192 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.38.192 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.33.182 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.32.182 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.31.182 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.30.182 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.29.172 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.28.172 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.27.172 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.26.162 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.25.162 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.24.162 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.23.162 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.22.162 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.21.162 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.20.162 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.19.162 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.18.152 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.17.152 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.16.152 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.15.152 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.14.152 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.13.132 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.12.105 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.11.105 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.10.105 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.09.105 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.08.105 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.07.105 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.06.105 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.05.105 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.04.105 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.03.095 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.02.095 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
03-MAR-21 07.52.01.095 AM 401 50s6rmpvj3hg8 enq: TX - row lock c 1415053318 2284
ontention
81 rows selected.
查询得知,在早上7点52分左右,401会话上执行了50s6rmpvj3hg8 SQL语句,造成了大量了阻塞,阻塞了2284会话
通过v$sql查询该SQL的文本信息
SQL> set linesize 200;
SQL> col sql_text for a100;
SQL> select sql_id,sql_text from v$sql where sql_id='50s6rmpvj3hg8';
SQL_ID SQL_TEXT
--------------------------------------- ----------------------------------------------------------------------------------------------------
50s6rmpvj3hg8 update GY_xhb SET dqz =dqz + zz WHERE bm =:1
50s6rmpvj3hg8 update GY_xhb SET dqz =dqz + zz WHERE bm =:1
50s6rmpvj3hg8 update GY_xhb SET dqz =dqz + zz WHERE bm =:1
50s6rmpvj3hg8 update GY_xhb SET dqz =dqz + zz WHERE bm =:1
50s6rmpvj3hg8 update GY_xhb SET dqz =dqz + zz WHERE bm =:1
50s6rmpvj3hg8 update GY_xhb SET dqz =dqz + zz WHERE bm =:1
50s6rmpvj3hg8 update GY_xhb SET dqz =dqz + zz WHERE bm =:1
50s6rmpvj3hg8 update GY_xhb SET dqz =dqz + zz WHERE bm =:1
8 rows selected.
将SQL语句递交给客户研发部门,至此,问题解决