How to detect deadlock in Azure Flexible PostgreSQL server? What is the recommended deadlock time out server parameters?

Question

How to detect deadlock in Azure Flexible PostgreSQL server? What is the recommended deadlock time out server parameters?

By default 1000 MS deadlock time out value is correct?

1 answer

Your answer

Answer 1

Sai Raghunadh M 4,635 Microsoft External Staff Moderator

Hi @ Sivangnanam, Kuraliniyan (AWM, IRL)

To detect deadlocks in an Azure Flexible PostgreSQL server, you can monitor the PostgreSQL error logs. Deadlocks are logged with the severity level "ERROR," and these logs provide details about the transactions involved, the resources they are waiting for, and the queries being executed. This information can help you identify and resolve deadlocks effectively

Regarding the deadlock_timeout parameter, the default value of 1000 milliseconds (1 second) is indeed a commonly recommended setting. This parameter determines how long a process waits before checking for a deadlock situation. You can adjust this value based on your application's requirements, but 1000 ms is generally a good starting point.

Please refer to these Documentations that might help you:

https://www.squash.io/detecting-and-resolving-deadlocks-in-postgresql-databases/
https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/server-parameters-table-lock-management?pivots=postgresql-17

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Sivangnanam, Kuraliniyan (AWM, IRL) 0 Reputation points

2025-04-15T13:32:51.0933333+00:00

Thank you .What would the recommended value for Lock_timeout settings in server parameter and application parameter?

Whether we can set particular app user Lock_timeout value to 15 mins for avoid long running queries?
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-04-15T15:35:54.7066667+00:00
Hi @ Sivangnanam, Kuraliniyan (AWM, IRL)

What would the recommended value for Lock_timeout settings in server parameter and application parameter?

The lock_timeout parameter specifies the maximum time a query will wait to acquire a lock before aborting. The recommended value depends on your application's requirements and workload. A typical starting point is a few seconds (e.g., 5,000 ms or 5 seconds) to avoid long waits that could impact performance.

Whether we can set particular app user Lock_timeout value to 15 mins for avoid long running queries?

Yes, you can set a specific lock_timeout value for a particular application user. For example, you can set it to 15 minutes (900,000 ms) if your application requires longer lock durations for certain operations.

Use the following SQL command:

ALTER ROLE app_user SET lock_timeout = '15min';

Replace app_user with the username of the application user.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Sivangnanam, Kuraliniyan (AWM, IRL) 0 Reputation points

2025-04-16T14:16:55.13+00:00

If we set the ALTER ROLE app_user SET lock_timeout = '15min';

Transaction cancelled due to below message

"canceling statement due to lock timeout"

So the issue related to particular statement ?or Any other best practice setting we missing ?
Sivangnanam, Kuraliniyan (AWM, IRL) 0 Reputation points

2025-04-16T14:17:28.0866667+00:00

If we set the ALTER ROLE app_user SET lock_timeout = '15min';

Transaction cancelled due to below message

"canceling statement due to lock timeout"

So the issue related to particular statement ?or Any other best practice setting we missing ?
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-04-16T17:11:04.5466667+00:00
Hi @ Sivangnanam, Kuraliniyan (AWM, IRL)

Yes, most likely. The error indicates that a particular statement couldn't acquire the required lock within the lock_timeout period (15 minutes in your case). This is not a server-wide failure, but one tied to contention involving specific tables/rows and concurrent operations.

This may happens because,

Another session is holding a conflicting lock on the same resource (e.g., row/table).

That lock isn't released fast enough (possibly due to long-running transactions).

Your statement is stuck waiting until it hits the lock_timeout limit.

Please try below suggestions:

Set lock_timeout conservatively 15 minutes is very high. In most production environments, people use:

1–5 seconds (1000ms to 5000ms) for general queries

Slightly higher (10–30s) for critical batch operations

Keep transactions short and commit as soon as possible. Long transactions can hold locks for too long.

Enable query and error logging (already built into Azure Flexible Server) and check the logs or use Azure Monitor to analyze slow or failed queries.
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-04-17T15:03:26.3833333+00:00

Hi @ Sivangnanam, Kuraliniyan (AWM, IRL)
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution

please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sivangnanam, Kuraliniyan (AWM, IRL) 0 Reputation points

2025-04-17T15:19:08.01+00:00

Thank you for the follow up .Currently application team checking the timeout query for the improvement. I would like to understand the below statement

"Unlike statement_timeout, this timeout can only occur while waiting for locks. Note that if statement_timeout is nonzero, it is rather pointless to set lock_timeout to the same or larger value, since the statement timeout would always trigger first."

What would be the recommended value statement_timeout and idle_in_transaction_session_timeout,idle_session_timeout ?
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-04-17T16:07:22.8566667+00:00

Hi @ Sivangnanam, Kuraliniyan (AWM, IRL)

lock_timeout only applies while waiting for locks, whereas statement_timeout applies to the overall execution time of a statement. If statement_timeout is set to a nonzero value, it will always trigger first if the statement exceeds the timeout duration. Therefore, it is recommended to set lock_timeout to a value smaller than statement_timeout to ensure lock-related issues are addressed promptly without impacting the overall statement execution.

Recommended values for statement_timeout**,** idle_in_transaction_session_timeout**, and** idle_session_timeout:

statement_timeout: A typical starting point is 30 seconds (30,000 ms) for general queries. For critical operations, you may increase this value based on your application's requirements.

idle_in_transaction_session_timeout: This parameter is used to terminate sessions that remain idle within a transaction. A recommended value is 5 minutes (300,000 ms) to prevent long-running idle transactions from holding locks unnecessarily.

idle_session_timeout: This parameter terminates sessions that remain idle outside of a transaction. A recommended value is 10 minutes (600,000 ms) to free up resources from inactive sessions.

Share via

How to detect deadlock in Azure Flexible PostgreSQL server? What is the recommended deadlock time out server parameters?

1 answer

Your answer