deadlock occurs with use statement

[donlxz]

When issuing a query from Informatica using a Delta connection, the statement use catalog_name.schema_name is executed first. At that time, the following error appeared in the query history:

Query could not be scheduled: (conn=5073499)
Deadlock found when trying to get lock; try restarting transaction

I understand that deadlocks can occur when multiple Informatica workflows are writing to the same table at the same time, but I’m not sure why this would happen with a USE statement.

Has anyone else experienced this situation?

[ManojkMohan]

@donlxz 

Informatica documentation and community discussions mention deadlock retry strategies, primarily for DML operations. However, metadata locks for catalog or schema operations can also lead to deadlocks.

https://docs.informatica.com/data-integration/powercenter/10-5/workflow-basics-guide/targets/working...

Solution thinking:

To prevent deadlocks related to USE statements and metadata locking:

Serialization of Workflows
Schedule workflows that write to the same catalog or schema to avoid running at the same time. This will reduce lock contention on metadata.

Implement Deadlock Retry Logic
Set up Informatica session properties to retry on deadlock errors with a backoff delay. This includes specifying deadlock retry counts and wait times, as supported by Informatica PowerCenter.

https://network.informatica.com/s/question/0D56S0000AD6vkxSQB/how-to-avoid-deadlocks-when-to-session...

https://docs.informatica.com/data-integration/powercenter/10-5/transformation-guide/lookup-transform...