CODEWITHSUNDEEP
sql-serveroracle-databaselockingdblinkheterogeneous-services
Dima Yankin
Dima Yankin

Reputation: 460

Is it possible to use WITH (NOLOCK) through heterogeneous link from Oracle

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?

Upvotes: 0

Views: 1650

Answers (1)

ashwin
ashwin

Reputation: 332

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

Upvotes: 0

Related Questions