一、准备测试环境及数据1.1创建测试表含主键约束CREATETABLETBL_TEST(IDINTPRIMARYKEY,NAMEVARCHAR(50));1.2插入测试数据INSERTINTOTBL_TESTVALUES(1,中文);INSERTINTOTBL_TESTVALUES(2,name2);COMMIT;A:-- 插入一条数据但不提交事务INSERTINTOTBL_TESTVALUES(3,中文);-- 注意此处不执行 COMMITB:-- 插入相同主键的数据此语句会被阻塞一直等待INSERTINTOTBL_TESTVALUES(3,中文);-- 插入不同主键的数据不会被阻塞INSERTINTOTBL_TESTVALUES(4,中文);二、定位查询阻塞源头2.1查询活动会话数活动会话数量达到一定程度后会对数据库性能造成影响。select(selectcount(*)fromv$sessionswherestateACTIVEandsess_id!sessid())act_ses,(selectcount(*)fromv$sessions)tot_ses;2.2查询已执行超过 2 秒的活动 SQL被阻塞的sql语句select*from(SELECTsess_id,sql_text,state,datediff(ss,last_recv_time,sysdate)Y_EXETIME,to_char(SF_GET_SESSION_SQL(SESS_ID))fullsql,clnt_ipFROMV$SESSIONSWHERESTATEACTIVE)whereY_EXETIME2;2.3查询锁SELECTO.NAME,L.*FROMV$LOCKL,SYSOBJECTS OWHEREL.TABLE_IDO.IDANDBLOCKED1;2.4查询阻塞SELECTsysdate stattime,被阻塞的信息wt,s1.sess_id wt_sess_id,s1.sql_text wt_sql_text,s1.state wt_state,s1.trx_id wt_trx_id,引起阻塞的信息fm,s2.sess_id fm_sess_id,s2.sql_text fm_sql_text,s2.trx_id fm_trx_idFROMv$sessions s1,v$sessions s2,v$trxwait wWHEREs1.trx_idw.idANDs2.trx_idw.wait_for_id;分析总结已定位会话140411091346440 insert 后未提交又因为表TBL_TEST的id列为主键不能违反主键唯一性约束导致会话140410679516696 insert时发生阻塞三、处理方式3.1让阻塞源头会话提交或回滚会话140411091346440 直接commit或rollback完成后140410679516696 可以正常insert3.2杀会话直接将引起阻塞的会话kill掉sp_close_session(SESS_ID);https://eco.dameng.com
一、准备测试环境及数据1.1创建测试表含主键约束CREATETABLETBL_TEST(IDINTPRIMARYKEY,NAMEVARCHAR(50));1.2插入测试数据INSERTINTOTBL_TESTVALUES(1,中文);INSERTINTOTBL_TESTVALUES(2,name2);COMMIT;A:-- 插入一条数据但不提交事务INSERTINTOTBL_TESTVALUES(3,中文);-- 注意此处不执行 COMMITB:-- 插入相同主键的数据此语句会被阻塞一直等待INSERTINTOTBL_TESTVALUES(3,中文);-- 插入不同主键的数据不会被阻塞INSERTINTOTBL_TESTVALUES(4,中文);二、定位查询阻塞源头2.1查询活动会话数活动会话数量达到一定程度后会对数据库性能造成影响。select(selectcount(*)fromv$sessionswherestateACTIVEandsess_id!sessid())act_ses,(selectcount(*)fromv$sessions)tot_ses;2.2查询已执行超过 2 秒的活动 SQL被阻塞的sql语句select*from(SELECTsess_id,sql_text,state,datediff(ss,last_recv_time,sysdate)Y_EXETIME,to_char(SF_GET_SESSION_SQL(SESS_ID))fullsql,clnt_ipFROMV$SESSIONSWHERESTATEACTIVE)whereY_EXETIME2;2.3查询锁SELECTO.NAME,L.*FROMV$LOCKL,SYSOBJECTS OWHEREL.TABLE_IDO.IDANDBLOCKED1;2.4查询阻塞SELECTsysdate stattime,被阻塞的信息wt,s1.sess_id wt_sess_id,s1.sql_text wt_sql_text,s1.state wt_state,s1.trx_id wt_trx_id,引起阻塞的信息fm,s2.sess_id fm_sess_id,s2.sql_text fm_sql_text,s2.trx_id fm_trx_idFROMv$sessions s1,v$sessions s2,v$trxwait wWHEREs1.trx_idw.idANDs2.trx_idw.wait_for_id;分析总结已定位会话140411091346440 insert 后未提交又因为表TBL_TEST的id列为主键不能违反主键唯一性约束导致会话140410679516696 insert时发生阻塞三、处理方式3.1让阻塞源头会话提交或回滚会话140411091346440 直接commit或rollback完成后140410679516696 可以正常insert3.2杀会话直接将引起阻塞的会话kill掉sp_close_session(SESS_ID);https://eco.dameng.com
