Transaction Stays Open On SQL*Server With The Parameter HS_TRANSACTION_MODEL=SINGLE_SITE_AUTOCOMMIT Using DG4MSQL (Doc ID 2045606.1)

Last updated on JANUARY 19, 2017

Applies to:

Oracle Database Gateway for SQL Server - Version 11.2.0.1 and later
Information in this document applies to any platform.

Symptoms

Using the Database Gateway for SQL*Server (DG4MSQL) 11.2.0.4 a call is made to a procedure on the SQL*Server database.
The procedure on SQL Server makes insert on a SQL*server table without making any commits on the Oracle side and active transactions are left on the SQL*Server side.
The DG4MSQL init<sid>.ora has the parameter -

 

HS_FDS_TRANSACTION_ISOLATION=SINGLE_SITE_AUTOCOMMIT

- and it is expected that the transaction is committed on SQL*Server, as it says in the gateway documentation -

SINGLE_SITE_AUTOCOMMIT provides read and write access to the non-Oracle
database without logging. The gateway cannot participate in distributed
updates.
Moreover, any update to the non-Oracle database is committed immediately.


Open transactions can be checked on SQL*Server using the command -

dbcc opentran

which will give output similar to -

Transaction information for the database 'Databse_Name'
Oldest active transaction :
   SPID ( the server process ID ) : 211
   UID ( User ID ) : -1
   Name : user_transaction
   LSN : ( 533303 : 241 : 1 )
   Start time : 19 December 2014 5 : 10 : 54 : 283PM
   SID : 0xfbf68d09f88c6a47a58240ce595f2d26
DBCC execution completed . If you have any error messages contact your system administrator .


 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms