Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qrBGK-002xjy-Ne for pgsql-admin@arkaria.postgresql.org; Fri, 13 Oct 2023 06:03:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qrBGI-00Eh47-CM for pgsql-admin@arkaria.postgresql.org; Fri, 13 Oct 2023 06:03:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qrBGH-00Eh3W-U4 for pgsql-admin@lists.postgresql.org; Fri, 13 Oct 2023 06:03:02 +0000 Received: from mail-lf1-x132.google.com ([2a00:1450:4864:20::132]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qrBGG-000FJm-4j for pgsql-admin@lists.postgresql.org; Fri, 13 Oct 2023 06:03:01 +0000 Received: by mail-lf1-x132.google.com with SMTP id 2adb3069b0e04-50573e85ee0so2200310e87.3 for ; Thu, 12 Oct 2023 23:02:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20230601.gappssmtp.com; s=20230601; t=1697176978; x=1697781778; darn=lists.postgresql.org; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=PcvJVwNke389NwfKS0/YRBEcnmoX11nrz/+KtrCI7DE=; b=kDeMRDwxbN+hkvp2KfSfP3FFPJMHiolZZErHk/J9+8BdDL6n81lBfkZ05P8zf4QQL7 HceeOOu/Ol3ZeAVbuvho7KPn+LEyW9QJOH5vib6PVsZSDSc2z0WCDIeSSq6REgzOqp1X GYZxPWlDwSdXMmtBZDL3B3+g1FSe1vQOwrSJBd/AKwtQ1DWnscNBtfm7AuOOaSAyQwIb +JrryTnMR2OOLbHw6aDUBYRx5zAvCsQ3vORDOt7r5wfTwDiHZI7DyhdDOcZaao5aBYZw QOTzJ1fm1tqB6W6YV/xl/9T9Gz1Vrvlb77zTrDAJJy+xxflV8680ZxptP+cs5G9lYbmK tEeg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697176978; x=1697781778; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=PcvJVwNke389NwfKS0/YRBEcnmoX11nrz/+KtrCI7DE=; b=jY6nX1vkIP25UQpD6mg5YsapeNtXAjB1ku67T3sSJKUwzZ+QV+nDLcDXVpXr1AE6HN +204a5y+s4/gBjj1WhWfV+mWmfIMVrEqXytZNYNYExM0xK6QidpcElw2cp2JLLDipEBz f1NyPx11BM/3X1Astrsg5uDBEiTCfWVeCaWYlVuDmnoHA+hGG6e5cNAfv3HVdoRVEMik JvLXFE+R89uw94sRpu7jtx5j33d8i/uqWS8uycg+5zoGRo9aNEd1UhLKt9rdEDer+Rhr 6qRMGpvHK0MYjFosUz4sKRsHtBx94qezePcDnuqNBN2oSgF8Z5mT56ArBnjWbS+dxNqI QiRw== X-Gm-Message-State: AOJu0YzZcduW3F228Zq5JB75wmrYcNWH4XGpjApQ1XC08F+P9XEUElxU A51B7/JP9+lBKQAxpFadPnf5Ze3wbzggqzZABxE= X-Google-Smtp-Source: AGHT+IE0OeTZuH1QjB4CVv50H0jd6Q6obfHuvRplFRidz9kdYuhNIvY/s1dhlpNUN5k0JfdoO/+Lew== X-Received: by 2002:a05:6512:1594:b0:505:6c7e:9950 with SMTP id bp20-20020a056512159400b005056c7e9950mr23609018lfb.19.1697176978071; Thu, 12 Oct 2023 23:02:58 -0700 (PDT) Received: from localhost.localdomain ([2001:871:5e:39a:9c21:f6c2:efbf:ff4d]) by smtp.gmail.com with ESMTPSA id u5-20020aa7d545000000b00534e791296bsm11043847edr.37.2023.10.12.23.02.57 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 12 Oct 2023 23:02:57 -0700 (PDT) Message-ID: <91d371667a3e27cb0da7001d048d1c417074b6ef.camel@cybertec.at> Subject: Re: Locks analyze From: Laurenz Albe To: Rajesh Kumar , Pgsql-admin Date: Fri, 13 Oct 2023 08:02:57 +0200 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 2023-10-13 at 05:16 +0530, Rajesh Kumar wrote: > How do I analyze locks and resolve it. Is there only one query that helps= ? > This lock issue leading to high connections it seems. Once you have identified a session that is blocked, get its process ID from pg_stat_activity and SELECT pg_blocking_pids(12345); with that process number as argument. Then you get all the other sessions that block that session. Yours, Laurenz Albe