0

I have a heterogeneous database link from Oracle Server to SQL Server. I issue SELECT statements on some frequently updated tables in SQL Server and my SELECT statements cause SQL Server to hold all UPDATES on mentioned rows. I know that it's a known behavior in MSSQL (read access blocks write access), but can I acquire dirty reads somehow? I searched throughout docs and see no sign of WITH (NOLOCK) or READ UNCOMMITED behavior for Oracle SQL Syntax.

My settings, just in case:

# Linux

HS_FDS_CONNECT_INFO = MSSQL
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE

# Depending on where you defined your ODBC settings.
set ODBCINI=/etc/odbc.ini

# Following parameters are SQL Server environment dependant.
HS_LANGUAGE=RUSSIAN_RUSSIA.CL8MSWIN1251
HS_NLS_NCHAR=UCS2
HS_NLS_LENGTH_SEMANTICS=CHAR
HS_FDS_SQLLEN_INTERPRETATION=8
HS_FDS_FETCH_ROWS=1
HS_TRANSACTION_MODEL=READ_ONLY

Is there any way to emulate WITH (NOLOCK) behavior, so I can read dirty rows from SQL Server and not cause write locks?

Dima Yankin
  • 365
  • 1
  • 12
  • 1
    I don't think so - I think you'd have to control isolation level on the SQL Server side, probably by wrapping the table in a view which internally has `WITH (NOLOCK)` or in a function: https://stackoverflow.com/questions/64208/how-to-force-nolock-hint-for-sql-server-logins – kfinity Oct 02 '19 at 13:27

1 Answers1

0

Try creating a sql stored procedure with read uncommitted isolation level and execute it from oracle.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

HS_FDS_PROC_IS_FUNC

ashwin
  • 332
  • 2
  • 16