The recommendations in this SAS note require that you install SAS^® Marketing Automation Hot Fix E08005 for release 5.4M1 and Hot Fix E88002 for release 5.41 or later and pertains to TERADATA only. This information is also valid for all 6.x versions.

When you run multiple, concurrent campaigns or single campaigns with multiple communications, there is a potential for the database to deadlock. These deadlocks typically occur when the campaigns or communications publish data to the SAS Customer Intelligence Reporting Common Data Model or when you update the contact history. If you see errors in the SASCustIntelCore or Stored Process Server logs that contain the text string deadlock, then you have encountered the problem described in this note.

Here are some recommendations:

1. Use the Teradata Fastload for MATABLES. Set the SAS^® options BULKLOAD, TENACITY, and SLEEP (described below) on the Business Context. These options ensure that the campaign does not fail immediately if a utility slot is not available.

   BULKLOAD=YES - tells SAS to use Teradata's Fastload to upload data

   SLEEP - if a utility slot is not available for a Fastload, then this setting causes SAS to wait ("sleep") for some period of time (the default is one minute) and then try again.

   TENACITY - this option controls the number of hours that Teradata continues to retry the Fastload.

2. If you store MATABLES in a separate database, then add the schema name and do NOT select Use database's temporary table capacity on the Options tab within the Business Context in SAS^® Management Console.
3. Be sure to explicitly set the formats for your SUBJECT_IDs in the information map. Do not leave the format blank.
4. Ensure that you have the same options in all of your Teradata LIBNAME statements that are used by SAS Customer Intelligence products. In particular, ensure that all of these Teradata LIBNAME statements include the option SQL_FUNCTIONS=ALL. SAS cannot keep the query processing in Teradata if the LIBNAME statements for the queries do not all have the same connection properties.
5. Add these options to MarketingAutomation_autoexec_usermods.sas:
   options dbidirectexec; %let SYS_SQL_IP_SPEEDO=YES;
6. Remove foreign-key integrity constraints. Although foreign-key integrity constraints provide a valuable service, SAS Customer Intelligence does not need them, and as a result can cause deadlock errors in concurrent processing situations. Some vendors recommend specific tuning features for foreign keys such as adding additional indexes to foreign keyed columns or with using the Teradata WITH NO CHECK OPTION syntax.

   Be aware that removing the integrity constraints as discussed above is a circumvention for the deadlock problem described and only should be considered if deadlocks are manifesting. No other changes to the Reporting Common Data Model are supported.

7. The following locking options are required on the SAS CDM library within SAS^® Management Console (select Data Library Manager ► Libraries ► Options ► Advanced Options ► Locking):

   Isolation level for reading: BLANK
   Lock Type for reading: BLANK
   Whether to wait to acquire read lock: BLANK

   Isolation level for updating: BLANK

   Lock Type1 for updating: BLANK

   Whether to wait to acquire update lock: BLANK

For additional information related to this topic, see SAS Note 45809 "SAS Marketing Automation returns "ERROR: Teradata row not delivered (trget): Transaction ABORTed due to deadlock" when you publish a campaign."
 

Operating System and Release Information