锁超时或者事务等待解决方法【阻塞】

1 模拟执行DML和DDL语句锁超时

查看锁超时数据字典/动态视图：v$trxwait、v$sessions、v$trx、v$lock;

–查询死锁历史记录
select * from V$DEADLOCK_HISTORY;
–查询死锁涉及的事务信息
select dh.trx_id,sh.sess_id,wm_concat(top_sql_text) from v$deadlock_history dh,v$sql_history sh where dh.trx_id=sh.trx_id and dh.sess_id=sh.sess_id group by dh.trx_id,sh.sess_id;
–查询阻塞锁
select * from v$lock where blocked=1;
–查询锁等待
select * from v$lockwait;
–查询等待事件
select * from v$system_event order by time_waited desc; #如果“time_waited”字段值很高，可能就有阻塞或者死锁
–查询会话
select * from v$sessions;

会话1：SQL> create table t_test(id int,name varchar(20));
会话1：SQL> insert into t_test values(1,‘name1’); --不提交
会话2：SQL> alter table t_test add column age int;
alter table t_test add column age int;
[-6407]:锁超时.
已用时间: 00:00:10.765. 执行号:0.

会话1执行DML语句不提交，会话2在执行DDL语句10秒后，就会报“锁超时”。 在10秒内会话1执行“commit”，则会话2就会执行成功。等待10秒由参数DDL_WAIT_TIME参数值决定要等待多少时间。

SQL> select * from v$dm_ini where para_name like ‘%DDL_WAIT_TIME%’;
行号 PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE DEFAULT_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE

---

1 DDL_WAIT_TIME 10 0 604800 10 N 10 10 Maximum waiting time in seconds for DDLs SESSION

会话1在10秒内执行查询事务锁语句
SQL> select * from v$trxwait;
行号 ID WAIT_FOR_ID WAIT_TIME THRD_ID

---

1 17124 17118 2757 47707

2 模拟执行DML和DML语句卡住

备注：在表有主键的情况下才产生事务等待
会话1：SQL> create table pk_test(id number not null primary key,name varchar2(20));
会话1：SQL> insert into pk_test values(1,‘name1’);
会话1一直不提交，则执行会话2就会一直卡住
会话2：SQL> insert into pk_test values(1,‘name1’);
此时会话2就会一直卡住
会话1：SQL> select * from v$trxwait;
行号 ID WAIT_FOR_ID WAIT_TIME THRD_ID

---

1 17130 17129 341713 47707
此时THRD_ID就是被等待的线程ID
解决方法1：直接杀掉THRD_ID就可以结束被等待的会话
解决方法2：
SQL> select a.SESS_ID,a.SQL_TEXT,a.TRX_ID,a.STATE,a.THRD_ID,c.SESS_ID,c.SQL_TEXT,c.TRX_ID,c.STATE,c.THRD_ID from v$sessions a,v$trxwait b,v$sessions c where a.TRX_ID = b.ID and b.WAIT_FOR_ID = c.TRX_ID;

SQL> sp_close_session(139783401724144); --结束被等待的会话，则会话1的DML语句就会回滚，会话2的DML语句就会执行成功。

产生了等待还可以用以下语句查询出来
SQL> select * from v$lock t where t.BLOCKED = 1;



社区地址：https://eco.dameng.com