ORA-01591: lock held by in-doubt distributed transaction
同事想删除某个表的数据，但是一直报错，跟我说是锁。通过如下SQL查询，没有发现表锁相关会话：
SELECT ddl.owner AS 用户,
ddl.name AS 对象,
ddl.type AS 类型,
vs.osuser AS os_user,
vs.machine,
vs.status,
vs.program,
vs.logon_time AS "LOGIN_TIME",
vp.spid,
'kill -9 ' || vp.spid AS os_kill,
vs.sid,
vs.serial#,
'alter system kill session ''' || vs.sid || ',' || vs.serial# ||
''';' "ORACKE_KILL",
vs.inst_id
FROM dba_ddl_locks ddl, gv$session vs, gv$process vp
WHERE ddl.session_id = vs.sid
AND vs.paddr = vp.addr
但是我尝试删除去删除的时候，报出错误：ORA-01591: lock held by in-doubt distributed transaction.
意思大概是有一个分布式事务的会话把表锁了。
查询分布式事务相关视图：select * from dba_2pc_pending:
select 'commit force '''|| local_tran_id||''';' from dba_2pc_pending;
select 'rollback force '''|| local_tran_id||''';' from dba_2pc_pending;
将执行commit force***, rollback force ***之后，可以正常删除
SELECT ddl.owner AS 用户,
ddl.name AS 对象,
ddl.type AS 类型,
vs.osuser AS os_user,
vs.machine,
vs.status,
vs.program,
vs.logon_time AS "LOGIN_TIME",
vp.spid,
'kill -9 ' || vp.spid AS os_kill,
vs.sid,
vs.serial#,
'alter system kill session ''' || vs.sid || ',' || vs.serial# ||
''';' "ORACKE_KILL",
vs.inst_id
FROM dba_ddl_locks ddl, gv$session vs, gv$process vp
WHERE ddl.session_id = vs.sid
AND vs.paddr = vp.addr
但是我尝试删除去删除的时候，报出错误：ORA-01591: lock held by in-doubt distributed transaction.
意思大概是有一个分布式事务的会话把表锁了。
查询分布式事务相关视图：select * from dba_2pc_pending:
select 'commit force '''|| local_tran_id||''';' from dba_2pc_pending;
select 'rollback force '''|| local_tran_id||''';' from dba_2pc_pending;
将执行commit force***, rollback force ***之后，可以正常删除
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8520577/viewspace-1969724/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
