Sql-server – DTC Timeout when trying to update oracle table through link that contains a text index

full-text-searchmsdtcoraclesql server

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.

Trace

This the end of the trace of a successful update:
successful update trace

This is the end of the trace of the update that runs into the timeout:
broken update trace

Best Answer

Turns out (after an Oracle SR) this was an already known problem we needed to patch the Oracle Server -> ORACLE 11G 11.2.0.2 PATCH 27 BUG FOR WINDOWS helps.

Related Question