Re: Deadlock

Dan Smith <j.daniel.smith1@xxxxxxxxx> · Tue, 2 Apr 2024 16:09:37 -0400

I think what is being said is that parsing the logs or looking for specific events such as a deadlock is the responsibility of the monitoring or the application interacting with the database, not the database server.  The database server is simply tasked with logging / providing notice to the client in this instance.  Ideally, as previously stated, the application should be determining how to address the issue.
If memory serves, pg_stat_database and pg_stat_database_conflicts views have some statistics and information regarding deadlocks which can be checked.

As for monitoring: Percona Monitoring and Management, pganalyze, pgbadger or any similar tool might prove to be helpful.  Ultimately, whatever alerting you configure from monitoring is what should be sending emails about potential issues vs the database server directly.  There are probably extensions or walkthroughs on using Postgres to trigger sending of emails in a manner similar to SQL Server but this does not seem aligned with the database server's purpose (manage access, storage, and retrieval of data).  Hope this information helps you find a suitable solution for your use case.

Best regards,

Dan Smith

On Tue, Apr 2, 2024 at 3:01 PM Rajesh Kumar <rajeshkumar.dba09@xxxxxxxxx> wrote:
Ok, do u mean to say that we do not have to do anything if I see an error in log for deadlock lock?

On Tue, 2 Apr 2024, 23:12 Holger Jakobs, <holger@xxxxxxxxxx> wrote:

    Am 02.04.24 um 19:09 schrieb Rajesh
      Kumar:

      Yes, I am trying to find out what message i should
        reply if I receive email with deadlock notification.

        I understand how deadlock occurs, hot to find
          them in logs, prevention of deadlock(ex: oqueries in order)
          etc.

        But since the deadlock_timeout is 1s, they don't
          probably hang around. So post getting an alert, not sure what
          to respond to them

        On Tue, 2 Apr 2024, 22:15
          Holger Jakobs, <holger@xxxxxxxxxx>
          wrote:

            Am 02.04.24 um 18:40 schrieb Rajesh Kumar:

              Hi

                If I receive an alert for deadlock, what
                  should I do? Like what analysis should I do? No way to
                  find a root cause?
                Under pg_locks, what I can analyse? 

            If you receive an error with a deadlock, the current
              transaction is dead. You have to decide whether to try
              again.
            Within the error message you find a hint which other (not
              aborted) transaction was involved in the deadlock. So you
              have all the information you need to find the culprit.
            Regards,
            Holger

            -- 

              Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

                chat with me on Signal
                  messenger

    I wonder what you are writing about. A
      database doesn't send email notifications. That must be a feature
      of some application.

    So you will have to deal the the
      application code or its developers.

    Or is it a customer sending you a message
      that some deadlock happened on their system? Still, you have to
      deal with the application code or the log files of the database
      system.

    Regards,
    Holger