Timeout and locking problems: DB2® database load utility

Use the following suggestions to help troubleshoot timeout and locking problems that might occur when you use a DB2® database load utility with IBM® Campaign.

Symptoms

Multiple flowcharts are running concurrently and the flowcharts write to the same table. A flowchart run fails with the following errors:

IBM® Campaign UI: "Loader command exited with an error status 4" and

Loader logs: "SQL0911N The current transaction has been rolled back because of a deadlock or timeout".

For example, you are using multiple flowcharts to insert records to the UA_ContactHistory table using the Mail List process box.

Causes

The load utility does not support loading data at the hierarchy level. When you concurrently run multiple flowcharts that load data into the same table, each individual load process locks the table. Each load process has to wait until the previous load finishes. If a process takes a longer time to finish, the next load process in the queue times out and produces the errors listed above.

Table locking during load operations: In most cases, the load utility uses table level locking to restrict access to tables. The level of locking depends on the stage of the load operation and whether it was specified to allow read access.

A load operation in ALLOW NO ACCESS mode uses a super exclusive lock (Z-lock) on the table for the duration of the load. Before a load operation in ALLOW READ ACCESS mode begins, the load utility waits for all applications that began before the load operation to release their locks on the target table. At the beginning of the load operation, the load utility acquires an update lock (U-lock) on the table. It holds this lock until the data is being committed. When the load utility acquires the U-lock on the table, it waits for all applications that hold locks on the table prior to the start of the load operation to release them, even if they have compatible locks. This is achieved by temporarily upgrading the U-lock to a Z-lock, which does not conflict with new table lock requests on the target table as long as the requested locks are compatible with the load operation's U-lock. When data is being committed, the load utility upgrades the lock to a Z-lock, so there can be some delay in commit time while the load utility waits for applications with conflicting locks to finish.

The load operation can time out while it waits for the applications to release their locks on the table prior to loading. However, the load operation does not time out while waiting for the Z-lock needed to commit the data.

Resolving the problem

Workaround: IBM® Campaign uses a shell script (or an executable, for Windows™) specified in the Loadercommand configuration property to invoke the database load utility. You can add queuing logic to your shell script or executable to work around this issue. This logic checks to see if one loader is running and performing load operation on a table; if this is the case, it does not allow other loaders to start loading until the previous one completes.