There is a blog, Mr. Raghavendra, which discusses very well the subject: deadlocks in PostgreSQL. See the link below.
You can better understand the process of control and locks management PostgreSQL.
"Deadlocks in PostgreSQL" - Raghavendra
According to Raghavendra
What is deadlock ?
Process A holding lock on object X and waiting for lock on Object Y.
Process B holding lock on Object Y and waiting for lock on Object X.
At this point the two processes are now in what's called 'deadlock'
each is trying to obtain a lock on something owned by the other. They
both will wait on each other forever if left in this state. One of
them has to give up and release the locks they already have. Now,
deadlock detector comes into picture and allow one process to success
and another to rollback.
To over come deadlock, design application in such a way that any
transaction UPDATE or DELETE should succeed with complete ownership on
the table. Lock the table with 'SHARE UPDATE EXCLUSIVE MODE' or
'SELECT...FOR UPDATE' or 'ACCESS EXCLUSIVE MODE' and complete the
transaction. In this model, deadlock detector never throw that it has
hit by a EXCLUSIVE LOCK's.
To try to monitor and trace the source of the problem, follow the link below which will help you in debugging.
"Postgres deadlocks debugging guidelines, tips and tricks" - Silvio Moioli
According to Silvio Moioli :
If you have to figure out what locks were requested in some occasion,
your best friend is pgAdmin III’s Tools -> Server Status window (full
explanation with screenshot here).
Important things to notice are:
that the Activity tab lists active processes/transactions;
that the Locks table lists all currently active locks.
As you showed in his first screenshot.
There is a blog, Mr. Raghavendra, which discusses very well the subject: deadlocks in PostgreSQL. See the link below.
You can better understand the process of control and locks management PostgreSQL.
"Deadlocks in PostgreSQL" - Raghavendra
According to Raghavendra
What is deadlock ?
Process A holding lock on object X and waiting for lock on Object Y.
Process B holding lock on Object Y and waiting for lock on Object X.
At this point the two processes are now in what's called 'deadlock'
each is trying to obtain a lock on something owned by the other. They
both will wait on each other forever if left in this state. One of
them has to give up and release the locks they already have. Now,
deadlock detector comes into picture and allow one process to success
and another to rollback.
To over come deadlock, design application in such a way that any
transaction UPDATE or DELETE should succeed with complete ownership on
the table. Lock the table with 'SHARE UPDATE EXCLUSIVE MODE' or
'SELECT...FOR UPDATE' or 'ACCESS EXCLUSIVE MODE' and complete the
transaction. In this model, deadlock detector never throw that it has
hit by a EXCLUSIVE LOCK's.
To try to monitor and trace the source of the problem, follow the link below which will help you in debugging.
"Postgres deadlocks debugging guidelines, tips and tricks" - Silvio Moioli
According to Silvio Moioli :
If you have to figure out what locks were requested in some occasion,
your best friend is pgAdmin III’s Tools -> Server Status window (full
explanation with screenshot here).
Important things to notice are:
that the Activity tab lists active processes/transactions;
that the Locks table lists all currently active locks.
There is a blog, Mr. Raghavendra, which discusses very well the subject: deadlocks in PostgreSQL. See the link below.
You can better understand the process of control and locks management PostgreSQL.
"Deadlocks in PostgreSQL" - Raghavendra
According to Raghavendra
What is deadlock ?
Process A holding lock on object X and waiting for lock on Object Y.
Process B holding lock on Object Y and waiting for lock on Object X.
At this point the two processes are now in what's called 'deadlock'
each is trying to obtain a lock on something owned by the other. They
both will wait on each other forever if left in this state. One of
them has to give up and release the locks they already have. Now,
deadlock detector comes into picture and allow one process to success
and another to rollback.
To over come deadlock, design application in such a way that any
transaction UPDATE or DELETE should succeed with complete ownership on
the table. Lock the table with 'SHARE UPDATE EXCLUSIVE MODE' or
'SELECT...FOR UPDATE' or 'ACCESS EXCLUSIVE MODE' and complete the
transaction. In this model, deadlock detector never throw that it has
hit by a EXCLUSIVE LOCK's.
To try to monitor and trace the source of the problem, follow the link below which will help you in debugging.
"Postgres deadlocks debugging guidelines, tips and tricks" - Silvio Moioli
According to Silvio Moioli :
If you have to figure out what locks were requested in some occasion,
your best friend is pgAdmin III’s Tools -> Server Status window (full
explanation with screenshot here).
Important things to notice are:
that the Activity tab lists active processes/transactions;
that the Locks table lists all currently active locks.
As you showed in his first screenshot.
