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...

[donlxz]

Hi @ManojkMohan 

Thank you for your response.
I understand that adjustments are needed on the Informatica side, and I’ll ask them to review the deadlock retry settings.
Is there anything that can be changed or configured on the Databricks side to help with this issue?

Also, is it possible to avoid deadlocks by tweaking the JDBC driver’s retry strategy?

[ManojkMohan · Accepted solution]

@donlxz 

Try improving the JDBC URL like 

jdbc:databricks://adb-1234567890123456.7.azuredatabricks.net:443/my_schema;
HttpPath=/sql/1.0/endpoints/abcd1234;
AuthMech=3;
UID=myuser;
PWD=mypassword;
Catalog=my_catalog;
Schema=my_schema;
RetryCount=5;
RetryInterval=2000;

On data bricks side

Workload design Separate concurrent ETL jobs into different schemas or catalogs Prevents two jobs from contending for locks on the same schema

SQL design best practice Use fully qualified table names instead of relying on USE Eliminates the need for schema context switching

Monitoring and troubleshooting Track queries causing deadlocks using Databricks system tables Helps identify which workflow/session caused the lock

Cache meta data as best practise

[donlxz]

I’ll try making adjustments on the Informatica side.
Thank you for your help.