CODEWITHSUNDEEP
sql-servert-sqlisolation-levelread-committed-snapshot
Advice Me
Advice Me

Reputation: 79

tsql wait for change with Read Committed Snapshot On

Hi Stackoverflow community,

Let me ask for your help as I did run into a critical issue.

We have two linked servers and both are Microsoft SQL Servers: CRM and DW servers. Some changes in CRM system triggers a procedure to instantly get updates to DW server, and the way it works is that CRM system calls DW server to update the record. In my case the updates coming from CRM system for CRM and DW sql servers are called simultaneous, and here the problem begins.

DW server tries to read changes and gets records only before transaction begin. Yes, this happens because CRM Server uses:

Read Committed Snapshot On

Unfortunately, we are not able to change isolation level on the CRM sql server. Simple explanation- CRM comes from a third party provider, and they want to limit us to make these possibilities.

Is there any other way, to wait for transaction to commit and then read the latest data after commitment?

If there is a lack of information, please let me know, then I will provide more insights.

Upvotes: 1

Views: 133

Answers (1)

S3S
S3S

Reputation: 25132

I don't understand the control flow here, but from the first paragraph you said updates in the CRM triggers a proc to update the DW server. So, I don't see how the DW server could be updating before the CRM server. You stated they are called simultaneously, but that would negate the comment about the trigger. You wouldn't want the DW to get dirty reads, so READ COMMITTED SNAPSHOT is a good choice here but you can also specify whatever isolation level you want at the transaction level and override the server default.

Since you asked "Is there a way to wait for transaction to commit and then read the latest data after commitment?". Sure, this can be handled in a few ways...

  • one would be a AFTER INSERT trigger
  • another would be to add the UPDATE to the DW in a code block after the INSERT statement, in the same procedure. Here, you could use TRY / CATCH and of course, SET XACT_ABORT ON so that if anything fails, the entire transaction is rolled back. Remember, nested transaction aren't real.

Upvotes: 0

Related Questions