Thread: Postgresql & Deadlocks

Good day all


           I just wanted to follow up again on a previous post by asking the questions differently after doing a lot of research into the matter.

           A previous post was related to table locking on postgresql and current locks monitoring providing counts including those for access share locks as well.


            What we are more interested in monitoring though is more specifically deadlocks on the databases, however from some research into the matter it seems that Postgresql is supposed to manage deadlocks on its own as it is detected by killing one of the processes.


             Thus, from what I understand it is not something that should really require monitoring (even though we are still looking for methods to monitor the amount detected during the last 24 hours.).

              I would however now like to know the following, in terms of server performance and availability, is there any aspects of table/row locking that can be monitored, which can perhaps cause problems?

           Any other suggestions for something specific to postgresql databases that needs to be kept an eye on for the same reason would also be appreciated as I am fairly new to postgresql.


Regards
Machiel

On 10 September 2010 08:03, Machiel Richards <machielr@rdc.co.za> wrote:
> Good day all
>
>
>            I just wanted to follow up again on a previous post by asking the
> questions differently after doing a lot of research into the matter.
>
>            A previous post was related to table locking on postgresql and
> current locks monitoring providing counts including those for access share
> locks as well.
>
>
>             What we are more interested in monitoring though is more
> specifically deadlocks on the databases, however from some research into the
> matter it seems that Postgresql is supposed to manage deadlocks on its own
> as it is detected by killing one of the processes.
>
>
>              Thus, from what I understand it is not something that should
> really require monitoring (even though we are still looking for methods to
> monitor the amount detected during the last 24 hours.).
>
>               I would however now like to know the following, in terms of
> server performance and availability, is there any aspects of table/row
> locking that can be monitored, which can perhaps cause problems?
>
>            Any other suggestions for something specific to postgresql
> databases that needs to be kept an eye on for the same reason would also be
> appreciated as I am fairly new to postgresql.

Are you aware of the pg_locks system catalog?
http://www.postgresql.org/docs/8.4/static/view-pg-locks.html

SELECT * FROM pg_catalog.pg_locks;

You can use that for monitoring locks.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935