cancel
Showing results for 
Search instead for 
Did you mean: 

Deadlock in Production

SOLVED
Highlighted
Level 2 Adventurer

Deadlock in Production

Hi Folks,

        In our porductions server deadlock detected..we dont know how to solve this issue.

 

What?

How to find the lock.?

How to resolve?

Any prameters we need to set?

 

Please advice guys.

 

Regards

Jithesh

 

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
EDB Team Member

Re: Deadlock in Production

Hi Jithesh, 

 

Thank you for feedback. 

Please note: Almost all DML or DDL Queries from application enclosed with BEGIN Transaction mode to avoid auto commit for safe side. As below

 

Ex:

BEGIN;

DDL

DML

COMMIT/ROLLLBACK

END.

 

Postgresql is not just auto-committed , it needs manual commit/rollback on when transaction started in begin mode. 

 

To avoid deadlocks, The most possible way to calling tables in appropriate order ratharthan jumbling. Locks will occur or waiting for resouces due to manipulating already manipulated tables (Which are already affected by dml on earlier after begin mode and waiting for commit/rollback).

 

Hope this helps. 

 

Regards

Siva.

5 REPLIES 5
EDB Team Member

Re: Deadlock in Production

Hi Jithesh, 

 

Hope you are doing good. Inline comments for your questions. 

 

What?:    Deadlocks: By definition of deadlocks,  two or more processes can end up in a state in which each process is waiting for the other one's resources. This state is called a deadlock. i.e Both proceses are waiting for each other's resources. 

 

How to find the lock.?: All kind of locking/waiting/blocking queries can get from wiki of postgresql, Here you go https://wiki.postgresql.org/wiki/Lock_Monitoring

 

How to resolve? : This is depends on application code. In general, applications must be ready to handle deadlocks issue and retry the transaction in case of a failure. Application locks tables on database, App should have a code to handle locks in proper order if not deadlocks will occur. 

 

Any prameters we need to set? : 

PG raises a deadlock error if it detects that two processes are waiting for each other. PostgreSQL will wait for a given interval before it sends deadlock signal. This interval is defined with deadlock_timeout configuration value. The default timeout value in PostgreSQL is 1 second, and this is probably the smallest time interval you would want to set in practice. If your database is heavily loaded, you might want to raise this value to reduce the overhead on your database servers.

 

Hope this helps, Please let us know if you have any concerns. 

 

Regards

Siva.

 

 

 

Silver Adventurer

Re: Deadlock in Production

Hi ,

           Thanks for your sharing this information..it is very helpfull for me..and i have some doubts is there in this conversation.

 

FYI @sivamekala 

 Oracle is not Autocommited is the deadlock is possible but postgreSQL is commited right.

How is it possible in Locks.can you explain some briefly. THANKS

 

Regards

Jithesh

 

EDB Team Member

Re: Deadlock in Production

Hi Jithesh, 

 

Thank you for feedback. 

Please note: Almost all DML or DDL Queries from application enclosed with BEGIN Transaction mode to avoid auto commit for safe side. As below

 

Ex:

BEGIN;

DDL

DML

COMMIT/ROLLLBACK

END.

 

Postgresql is not just auto-committed , it needs manual commit/rollback on when transaction started in begin mode. 

 

To avoid deadlocks, The most possible way to calling tables in appropriate order ratharthan jumbling. Locks will occur or waiting for resouces due to manipulating already manipulated tables (Which are already affected by dml on earlier after begin mode and waiting for commit/rollback).

 

Hope this helps. 

 

Regards

Siva.

Silver Adventurer

Re: Deadlock in Production

@sivamekala 

                       I understand..this is perfect reply..many many thanks.If ay reopen this Loops.

 

THANKS A LOT @sivamekala 

 

Regards

Jithesh.

EDB Team Member

Re: Deadlock in Production

Hi Jithesh, 

 

Thank you very much. Have a nice day. 

 

Regards,

Siva.