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

[RichJ]

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

[Dana_Baldwin]

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!

[RichJ]

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?

[Dana_Baldwin]

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

[Yadunandan · Accepted solution]

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