search

tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)
Other
381 stars 102 forks source link

Execute queries with NOLOCK on MS SQL side #200

Closed kloba closed 5 years ago

kloba commented 5 years ago

Hello,

How to run all queries throughout tds_fdw in NOLOCK mode on MS SQL side?

I need this, in a case to don't lock a table on an OLTP database.

Best regards, Taras Kloba

SudoerWithAnOpinion commented 5 years ago

Use the query option in the foreign table definition and define your query WITH NOLOCK

CREATE FOREIGN TABLE mssql_table (
    id integer,
    data varchar)
    SERVER mssql_svr
    OPTIONS ( query 'SELECT * FROM dbo.mytable WITH (NOLOCK)' );

Keep in mind, this causes READ UNCOMMITED (or dirty reads) and is generally considered bad practice. The MSSQL lock manger should allow your query to read data without locking the data from other transactions and updates (since this is a non-blocking type of lock) unless an UPDATE needs an exclusive lock for some reason. Due to the issues that arise from dirty reads, you might be returning more data (or incomplete/ or duplicated data) back to postgres. Use this option with care.

SudoerWithAnOpinion commented 5 years ago

@kloba: If this resolved your issue, can you be a sweetie and close this issue? @GeoffMontee: I think this has gone stale, can you close this if @kloba does not reply?

kloba commented 5 years ago

Thank you a lot.