Skip to main content
Solved

Multiple API calls to same process flow at the same time

  • February 17, 2025
  • 5 replies
  • 135 views
NickJanssen
Captain
Forum|alt.badge.img+5

Hello,

I have a question about a process flow which I made specifically for an integration with another application, say “App B”.

The process flow makes sure to merge the correct record into the correct tables. There is some custom logic where checks are done whether to insert or to update the record. This is an atomic transaction.

We have a webhook configured in App B, which means that every update App B receives is immediately send to our Thinkwise application. Now it seems that App B is updated via an integration as well. Sometimes, this means that the same record in App B gets updated multiple times in rapid succession.

Our process flows is not able to handle this correctly. I get the following error: 

Cannot insert duplicate key row in object “XXX”

 

Do you have any recommendations in order to solve this issue?

Will simply rewriting my check into a merge statement rather than the custom check help resolve the issue? The issue is that I have no easy way to test the issue in another environment at the moment.

Best answer by NickJanssen

Hello,

​@Ricky, First a check is done whether the indexed key is already present in the table.

In both transactions both checks result in a False: the records does not exist yet, which means that both transactions are trying to insert a new record with the same indexed key. Obviously, one of the two fails because it is already present in the table.

I did some more investigation, and I think rewriting the check logic to a merge statement already solves our problem. In this way, there is only 1 transaction (merging) rather than 2 (checking whether the records exist & inserting). The new logic is not yet present in our production environment, but soon I will know if this works.

Thanks for the help!

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

5 replies

E
Moderator
Forum|alt.badge.img+17
  • Erwin Ekkel
  • Moderator
  • 761 replies
  • February 17, 2025

Does the table where the insert takes place have an identity primary key? This sounds like the key is set up manually and the merge causes the insert to fail due to a simultaneous insert. 

NickJanssen
Captain
Forum|alt.badge.img+5
  • NickJanssenCaptain
  • Author
  • Captain
  • 43 replies
  • February 17, 2025

Hi Erwin,

We have an identity column set as primary key. However, there is a unique index on another columns (which causes the error).

Note that this specific template has no merge statement at the moment:

IF EXISTS (...)
   BEGIN
      UPDATE
   END
ELSE
   BEGIN
      INSERT
   END

 

Ricky
Superhero
Forum|alt.badge.img+8
  • RickySuperhero
  • Superhero
  • 96 replies
  • February 17, 2025

​@NickJanssen How do you know which “request” should be updated/inserted in the DB? Unless I’m misunderstanding your issue, you may want to take a look at some form of locking mechanisms: database - Optimistic vs. Pessimistic locking - Stack Overflow

Jeroen van den Belt
Administrator
Forum|alt.badge.img+9

Hi ​@NickJanssen,

Can you answer the latest question asked by Ricky? Thanks in advance!

NickJanssen
Captain
Forum|alt.badge.img+5
  • NickJanssenCaptain
  • Author
  • Captain
  • 43 replies
  • Answer
  • February 28, 2025

Hello,

​@Ricky, First a check is done whether the indexed key is already present in the table.

In both transactions both checks result in a False: the records does not exist yet, which means that both transactions are trying to insert a new record with the same indexed key. Obviously, one of the two fails because it is already present in the table.

I did some more investigation, and I think rewriting the check logic to a merge statement already solves our problem. In this way, there is only 1 transaction (merging) rather than 2 (checking whether the records exist & inserting). The new logic is not yet present in our production environment, but soon I will know if this works.

Thanks for the help!

Ricky
1 person likes this
  • Ricky
    Ricky
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings