TIBCO Scribe® Insight Queue based integrations can use multiple message processors for simultaneously processing several messages. There is one potential caveat to multi-threaded integrations. To understand the caveat, consider the following scenario in which you need to synchronize company information from your ERP system with accounts in your CRM system. You have configured a publisher that writes record change events to the ScribeIn queue. Your DTS then pulls records off the ScribeIn queue, and executes the following logic:
  The issue is that when multiple messages for the same account are processed simultaneously, it is possible to insert duplicate records because the Seek or Lookup operation returned no results. Assuming the account does not exist in CRM, the first message would generate an Insert. If the second message is processed at the same time, the Seek or Lookup operations would also indicate that the account does not exist and perform an Insert instead of an Update. The issue is one of timing.

When multiple messages for the same entity are processed simultaneously in a multi-threaded environment, it is possible to insert duplicate records because the seek or lookup operation returned no results.

The solution to this is the use Key Cross Reference Locking (KCRL). The key components to making this work are the KEYCROSSREFERENCE_LOCK and the KEYCROSSREFERENCETWOWAY tables, and the use of Key Cross Referencing in the DTS. Key Cross Reference Locking works in conjunction with the Key Cross Reference settings configured in the DTS for updating the KEYCROSSREFERENCETWOWAY table and calls made to the XREFLOOKUPLOCK_AKEY ()  or XREFLOOKUPLOCK_BKEY () functions.
 

Specifying The Number Of Message Processors
 

There is a way to prevent the above scenario without configuring Key Cross Reference Locking. This is a simple and pragmatic approach if the volume of MSMQ messages is relatively small. Setting the number of messages processors to two leaves only a single message processor for handling queue based integrations and, therefore, only one message is processed at a time. This is a great way to determine if duplicate entries are caused by multiple messages being processed at once. If the duplication stops, then you know that was the issue. As long as volume is light, you can leave the setting at two Processors. However for a large volume of messages, Key Cross Reference Locking must be employed.
 

Using The Cross Reference Tables
 

 
In multi-threaded scenarios the benefit of using Key Cross Reference Locking is that it can prevent duplicate records caused by Seek, Insert, Update logic in the DTS as shown in the diagram at the beginning of this document. Note that the Key Cross Reference Locking only prevents duplicates for newly created records on the target – it doesn’t perform any locking for Update operations.

Functions
 

TIBCO Scribe® Insight has several new functions for working with Key Cross Reference logic:
XREFLOOKUPLOCK_AKEY() and  XREFLOOKUPLOCK_BKEY() are used to return values from the KEYCROSSREFERENCETWOWAY table. They work exactly as the XREFLOOKUP_AKEY() and XREFLOOKUP_BKEY() did in TIBCO Scribe® Insight 6.5.x except that these new methods employ the locking logic as described in the Using The Cross Reference Tables section.

XREFLOOKUPNOLOCK_AKEY() and  XREFLOOKUPNOLOCK_BKEY() work exactly like XREFLOOKUP__AKEY() and XREFLOOKUP_BKEY() did in TIBCO Scribe® Insight 6.5.x. They do not perform any locking logic. If you are not using message queuing and thus are not worried about the duplicate record scenario as described above, you achieve a minor performance gain by using the "NOLOCK" version of these functions.

For backward compatibility,  XREFLOOKUP_AKEY() and  XREFLOOKUP_BKEY() functions are still supported. By default, they use the newer locking logic. They  behave in the same manner as the XREFLOOKUPLOCK_AKEY() and XREFLOOKUPLOCK_BKEY() functions. However, as a best practice use the "LOCK" and "NOLOCK" versions of these functions to make the intended behavior explicitly clear in your DTS.

The KEYCROSSREFERENCE_LOCK table grows over time. You can safely delete records from this table at any time without having any impact on integrations.
 

Pseudo Code For The Key Cross Reference Locking Process
 

This is the logic flow for when to lock a record.

XREFLOOKUPLOCK_AKEY(  LabelValue, AppBKeyValue )
{
      If( LabelValue, AppBKeyValue found in KEYCROSSREFERENCETWOWAY )
      {
             return  AppAKeyValue   // value found no locking needed
      }
      else
      {
            Insert and lock record in KEYCROSSREFERENCELOCK for LabelValue, AppBKeyValue  // 
            no valuefound, set the lock

            // lock has been set or cleared
            If(LabelValue, AppBKeyValue found in KEYCROSSREFERENCETWOWAY )
            {
                  return AppAKeyValue // somebody else had locked it and has set the value
            }
            else
            {
                   return null  // I have locked it and should set the value
            }
       } 
}

Note: If the was lock is set, it is cleared when the steps for the row are completed.

How Locking Works
 

Based on the pseudo code above, notice that a lock is generated only if an entry does not exist in the KEYCROSSREFERENCETWOWAY table. TIBCO Scribe® Insight does a lookup in the KEYCROSSREFERENCETWOWAY table using the Label and Key specified in either XREFLOOKUPLOCK_AKEY ()  or XREFLOOKUPLOCK_BKEY () functions.

This is the approach generally used in all of the TIBCO Scribe® Insight Dynamics CRM and Dynamics GP templates. Locking only occurs on Inserts because by the time an Update occurs, there is an entry in the KEYCROSSREFERENCETWOWAY table.

This means locking occurs for the Insert of a parent record but not Updates or Inserts of child records. For example, assume you have a Sales Order message with multiple line items and the target contains separate entities for sales header and sales line items. Locking occurs on the parent sales header record and then is released. Subsequent records do not employ locking since an entry now exits in the KEYCROSSREFERENCETWOWAY table.
 

Configuring A DTS For Key Cross Reference Locking
 

Below  is a DTS used in the Dynamics CRM / Dynamics GP templates. Notice that the Seek step contains a reference to a calculated variable. The calculated variable uses the XREFLOOKUPLOCK_BKEY() function to manage the locking process.

If a lock is created, it is maintained until the row is processed.
 
In addition, Key Cross Reference is enabled and configured in the DTS.
 
If a lock is created, it is generated against the KEYCROSSREFERENCE_LOCK table regardless of whether or not an entry already exists.

A lock is generated if the corresponding Label and Key specified in the XREFLOOKUPLOCK function also exist in the KEYCROSSREFERENCETWOWAY table. For example:  XREFLOOKUPLOCK_BKEY( "ERP2CRM-Customer", S114 )

First DTS Run
 

With the first run of the DTS a lock is obtained because the value does not exist in the KEYCROSSREFERENCETWOWAY table. An entry is generated in the KEYCROSSREFERENCE_LOCK table. The Insert step is executed based on Seek or Lookup logic in the DTS, based on either traditional seeks or a lookup to the KEYCROSSREFERENCETWOWAY table.

   

Second DTS Run

The KEYCROSSREFERENCETWOWAY table was updated by the first DTS at the end of processing the row/record. A lock is not generated for the second DTS because the lookup to the KEYCROSSREFERENCETWOWAY table returned a value indicating that the record exists. An Update is performed based on seek or lookup logic in the DTS.

 
 

For additional information, see the following:
TIBCO Scribe® Insight Help: Preventing Duplicates, About Cross Reference Keys, XREFLOOKUP Functions, Defining Cross Reference Keys