Friday, November 28, 2014

Does ORA-02049 invalidate DBMS XA transaction?

There is a widely spread rule in WebLogic/Oracle XA processing environment to keep DISTRIBUTED_LOCK_TIMEOUT on a very high level, higher than JTA Timeout and XA Timeout to avoid XA consistency problems. In my opinion it's not a valid configuration, creating unnecessary delays in processing data locks. 
This exercise shows that on a database level, lock timeout, manifested by 'ORA-02049: timeout: distributed transaction waiting for lock', does not change transactional state of the work i.e. rolls back the transaction. 

Preparation
Open two sessions pointing to Oracle database e.g. XE release. You may use sqlplus, SQL Developer, or any other Oracle database client. Distributed XA environment is simulated by a database link. Below example uses oracle/welcome1@XE as connection credentials. Update accordingly.

Execution steps

####
#### execute in session#1
####
#### sqlplus system/welcome1@XE
####

--- create database link to simulate distributed environment 
--- 
--- *Update* system, welcome1, and XE with values from your system. 
--- These are the only params in the script.
--- 
CREATE DATABASE LINK local_db_link CONNECT TO system IDENTIFIED BY welcome1 using 'XE';

--- prepare test table. This step may return errors, if table exists or does not exist.
drop table lock_test;
create table lock_test(f1 varchar2(5), f2 varchar2(5), constraint pk_lock primary key (f1));
truncate table lock_test;

--- insert data into table
insert into lock_test (f1, f2) values ('A','AAA');

####
#### execute in session#2
#### 
#### sqlplus system/welcome1@XE
####

--- insert data with expected ORA-02049: timeout: distributed transaction waiting for lock
insert into lock_test@local_db_link (f1, f2) values ('A','AAA');

--- do something else - despite above tentative problem, database connection can be used
insert into lock_test@local_db_link (f1, f2) values ('B','BBB');
select * from dual;
commit;

####
#### execute in session#1
####
commit;

####
#### execute in session#2
####
--- try another time to insert data with expected ORA-00001: unique constraint (SYSTEM.PK_LOCK) violated
insert into lock_test@local_db_link (f1, f2) values ('A','AAA');

####
#### execute in session#1
####
delete from lock_test where f1='A';
commit;

####
#### execute in session#2
####
--- try another time to insert data with expected success
insert into lock_test@local_db_link (f1, f2) values ('A','AAA');


Source of simulating the distributed transaction idea: https://hernk.wordpress.com/2006/06/21/simulating-lock-timeout/


###

No comments:

Post a Comment