说明:
ORA-02049是一个分布式事务等待超时的错误,当一个session 持有一个行级锁,另一个分布式事务比如通过dblink也想修改相同行的数据,就会产生行锁队列等待,当等待时间超过了系统参数distributed_lock_timeout的值时,就产生了这个错误,任何通过通过DBLINK的操作都是分布式事物。
模拟:
测试库1:
创建测试表并插入测试数据:
SQL> create table test(id number(10),name varchar(20));
Table created.
SQL> insert into test values(1,'baoyuhang');
1 row created.
SQL> insert into test values(2,'dbhang');
1 row created.
SQL> commit;
Commit complete.
SQL> update test set name='A' where id=1;
1 row updated.
*此事务不提交
测试库2:
创建dblink:
SQL> create database link to_tests CONNECT TO hr IDENTIFIED BY hr USING 'test';
Database link created.
通过DBLINK修改数据库1中的test表id=2的行:
SQL> update test@to_tests set name='B' where id=2;
1 row updated.
通过DBLINK修改数据库1中的test表id=1的行:
SQL> update test@to_tests set name='B' where id=1;
Hang住
......
......
......
等待60秒后出现如下错误
*
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock
ORA-02063: preceding line from TO_TESTS
分布式锁资源等待超时时间由distributed_lock_timeout参数控制.默认为60s
SQL> show parameter distributed_lock_timeout
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
distributed_lock_timeout integer 60
当等待时间超过该参数值就会出现ORA-02049: timeout: distributed transaction waiting for lock
以上这种测试情况属于非分布式事务阻塞了分布式事务的修改.
还有一种情况属于分布式事务阻塞分布式事务的情况(即两个session同时通过dblink修改相同行数据)
(注意在Oracle中通过DBLINK进行的事务操作都属于分布式事务类型)
生产中如果出现ORA-02049问题的解决措施:
问题模拟:
在数据库2开启两个会话通过DBLINK修改相同的数据库1中表中的数据行
session1:
SQL> update test@to_tests set name='DBhang' where id=1;
1 row updated.
--不提交
session2:
SQL> update test@to_tests set name='baoyuhang' where id=1;
Hang住
......
......
......
等待60秒后出现如下错误
*
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock
ORA-02063: preceding line from TO_TESTS
定位造成锁阻塞的Session:
查询等待事件是没有输出的:
SQL>select event,count(*) from gv$session_wait where wait_class<>'Idle' group by event;
no rows select
采用如下语句进行查询:
SELECT
S.USERNAME,
DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
O.OWNER,
O.OBJECT_NAME,
O.OBJECT_TYPE,
S.SID,
S.SERIAL#,
S.TERMINAL,
S.MACHINE,
S.PROGRAM,
S.OSUSER,
S.sql_id,
S.prev_sql_id
FROM V$SESSION S, V$LOCK L, DBA_OBJECTS O
WHERE L.SID = S.SID
AND L.ID1 = O.OBJECT_ID(+)
AND S.USERNAME IS NOT NULL;
SQL_ID是当前正在执行的SQL,我们可以根据prev_sql_id查询出该会话最后一次执行的SQL:(不一定是造成分布式锁资源等待的资源的语句,并不准确)
select sql_text from v$sql where sql_id='dkss7uk27mgm9';
SQL_TEXT
-----------------------------------------------------
update test@to_tests set name='baoyuhang' where id=1
根据sid,serial# kill会话:
alter system kill session 'sid,serial#';
alter system kill session '69,127';
alter system kill session '191,81';
解决完毕!
问题总结:
一般在生产环境下出现分布式锁资源等待问题就是业务逻辑问题,要修改事务提交的频率,减少多个session同时修改相同数据,减小事务,并且小事务要快速执行commit提交,如果业务逻辑中果真存在大事务60秒内无法提交,建议适当调大distributed_lock_timeout参数值