topic Re: How to handle "WITH (NOLOCK)" from the replica source SQL Server DB in Qlik Replicate

How to handle "WITH (NOLOCK)" from the replica source SQL Server DB

RichJ — Thu, 11 Jan 2024 23:13:26 GMT

Hi Sir,

The replica source db is SQL Server. The original query is like

select .... from table WITH (NOLOCK);

Which means the query will read the un-committed data. My question is how Qlik replica handles "WITH (NOLOCK)"? is that qlik's default option?

Thanks,

Rick

Re: How to handle "WITH (NOLOCK)" from the replica source SQL Server DB

Dana_Baldwin — Thu, 11 Jan 2024 23:17:32 GMT

Hi @RichJ

Qlik Replicate tasks must capture transactions as they are started when written to the transaction log, so "no lock" is required. The task has to "see" the transaction when it starts to know it exists and read it's contents. Only committed transactions are replicated to the target.

Hope this helps,

Dana

If one of the replies helped to resolve the issue, please mark it as a solution & like it to make it easier for others to find.
Thanks in advance!

Re: How to handle "WITH (NOLOCK)" from the replica source SQL Server DB

RichJ — Thu, 11 Jan 2024 23:46:08 GMT

From what I read, SQL Server "WITH (NOLOCK)" means read uncommitted or dirt data. But from what you replied, Qlik only supports reading committed data? If so, "WITH (NOLOCK)" cannot be implemented in Qlik?

Re: How to handle "WITH (NOLOCK)" from the replica source SQL Server DB

Dana_Baldwin — Thu, 11 Jan 2024 23:58:16 GMT

Hi @RichJ

Perhaps I misunderstood your original question. Are you asking if Replicate can be configured to replicate all changes including uncommitted changes to the target? If so, no, that is not a possible configuration.

Thanks,

Dana

Re: How to handle "WITH (NOLOCK)" from the replica source SQL Server DB

Yadunandan — Fri, 12 Jan 2024 00:55:21 GMT

Hello @RichJ

There are 2 things here, reading incoming changes and sending the same changes to the target.

Replicate do read uncommitted changes but it will be stored in the form of sorter files in the replicate data directory, once it is committed it will send the data to the Target endpoint. If the query is rolled back then those sorter files will be cleared without sending those changes to Target.

Hope this helps.

Regards,
Yadunandan