Return to Revisions

3 of 3

deleted 16 characters in body

edited Apr 4, 2014 at 7:49

DTC Timeout when trying to update oracle table through link that contains a text index

Problem

Given is a SQL Server connected to an ORACLE Instance as a Linked Server. It’s using the Oracle Client OLEDB Provider. When doing an update in a transaction on a table having a CTXSYS.CONTEXT Text Index the DTC Connection aborts after approx. 10 Min with Error: 8522, Severity: 18, State: 1 on the commit. As soon you remove the CONTEXT index it works again.

Is this a bug in OraOLEDB or can we do something to fix this issue, while keeping the indexes?

Setup

On ORACLE

We use an ORACLE 11g 11.2.0.2.0

Create this table for this test.

CREATE TABLE docs (id NUMBER PRIMARY KEY, text VARCHAR2(200));
INSERT INTO docs VALUES(1, '<HTML>California is a state in the US.</HTML>');
INSERT INTO docs VALUES(2, '<HTML>Paris is a city in France.</HTML>');
INSERT INTO docs VALUES(3, '<HTML>France is in Europe.</HTML>');
INSERT INTO docs VALUES(4, '<HTML>Los Angeles is a city in California.</HTML>');
INSERT INTO docs VALUES(5, '<HTML>Mexico City is big.</HTML>');
COMMIT;
CREATE INDEX idx_docs ON docs(text)
    INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS
    ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');

On MSSQL

We use a SQL Server 2012 Express and Developer Edition both produce the same error. Setup a linked server to above Oracle Server with Oracle Provider for OLE DB (OraOLEDB) Release 12.1.0.1.0 Production.

Reproduce

Working Update

This updates work:

set xact_abort off;
update DBLINK..REMOTE_USER.DOCS set text='<HTML>Mexico City is massive.</HTML>' where id = 5;

(1 row(s) affected)

This Update Statement produces the error, after the DTC seems to run in a timeout.

set xact_abort on;
begin transaction t1
update DBLINK..REMOTE_USER.DOCS set text='<HTML>Mexico City is huge.</HTML>' where id = 5;
commit transaction t1;

(1 row(s) affected)
Msg 8522, Level 18, State 1, Line 4
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.