The Lookup transformation is resilient to a database deadlock for uncached lookups. When a database deadlock error occurs, the session does not fail. The Integration Service attempts to re-execute the last statement for a specified retry period.
You can configure the number of deadlock retries and the deadlock sleep interval for an Integration Service. These values also affect database deadlocks for the relational writer. You can override these values at the session level as custom properties.
Configure following Integration Service Properties:
The number of times the PowerCenter Integration Service retries a target write on a database deadlock. Minimum is 0. Default is 10. If you want the session to fail on deadlock set NumOfDeadlockRetries to zero.
Number of seconds before the PowerCenter Integration Service retries a target write on database deadlock.
If a deadlock occurs, the Integration Service attempts to run the statement. The Integration Service waits for a delay period between each retry attempt. If all attempts fail due to deadlock, the session fails. The Integration Service logs a message in the session log whenever it retries a statement.